Hi there,
I don't think that feature exists in base yet. Hopefully one day. Until then there are still a couple of ways to do what you want.
The first involves creating a "dummy
table", purely for holding the boundfield value from the list box or combo box you use. A dummy
table is a
table of just two fields, a PK field and a data field that will hold the boundfield value. You then base your mainform around this
table and put the combobox in that form, linking its boundfield to the second data field. The form you want filtered you just hang off the mainform as a subform. You do a join between the two forms from the field that holds the combobox's bound value to the field in your subform you are filtering by. A very simple commit and reload
macro is required to reload the subform when ever you make a selection. Further details here...
http://www.oooforum.org/forum/viewtopic.phtml?t=72134
The second way would require a bit more
macro code and would actually change the select statement your form is based on as per the bound value from the combobox, sort of similar to what you are trying to achieve, but the whole statement would need to be programatically changed and then the form reloaded. I have not tried this but it should work. The code would be similar to that which we write when wanting to use one
listbox to narrow the contents of a second
listbox, except we would be changing the datasource statement of a form rather than of another
listbox. You would still need to use a dummy
table to hold the boundfield of the combobox but a second mainform could be used to accomodate the dummy
table. I'll see if I can fashion a quick example database to show how this would be done.
(Edit. You don't need to use a dummy
table if you are using a combobox, as you can use the CurrentValue property instead. If using a
listbox and wanting to use its separate bound field to
filter with, it's boundfield has to be put somewhere so a dummy
table comes in handy.)
Example...
First change the content type of your MainForm to SQL Command and fashion an appropriate statement by pressing the [...] next to content. Write the statement down because you will need it for the
macro code below.
Next create a dummy
table as discussed above (non auto incrementing). Go to the
table in the
table editor and fill in the ID field for just the first row by putting in a 1
Create a second MainForm in your form called "MainForm2" and base it on your dummy
table.
Making sure you have the wizard button from the "FormControl" toolbar pressed, draw a ComboBox on your form. If it is drawn in the MainForm rather than MainForm2 form then you will need to move it with the "FormNavigator" and edit its properties to leave its boundfield in the dummy tables field.
Copy the code below into the macros area of the database and customize any names and the SQL statement.
In the ComboBox properties, go to the events tab and make the ItemStatusChanged event point to the "Combobox_Limit_Form"
macro.
Code: Select all
sub Combobox_Limit_Form( oEv as object )
dim oSubForm As object
dim oControl as object
dim new_list_sql(0) as string
oMainForm2 = oEv.Source.Model.Parent Rem... MainForm2
oControl = oEv.Source.Model
if oControl.CurrentValue <> "" then
oControl.commit Rem ... Commits current value to boundfield
Rem... Calls the LimitItems sub and passes the Form event object and the boundfield value of the selected item.
limitItems( oMainForm2, oControl.BoundField.String )
End if
End sub
sub limitItems( oMainForm2 as variant, oControlBoundfield as String )
dim oMainForm as variant
oMainForm = oMainForm2.parent.GetByName("MainForm")
oMainForm.Command = "SELECT * FROM Names AS Names WHERE Surname = '" & oControlBoundfield & "'"
oMainForm.reload() Rem loads the the MainForm so as to display the value selected in the combobox
end sub
Here is a link to an example database called Surnames3. It will work only in OOo V3.1 or greater due to the
macro code being included inside the database.
- Surnames3.odb
- Surnames3 Example Database
- (14.33 KiB) Downloaded 1706 times
Cheers
Voo