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.)
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
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 Expand viewCollapse view
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 )
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
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.