Filter Form with unbound dropdown

[samot79]

Hi,

I am trying to use an unbound Combobox in a form to filter my result that is shown in a form. The form contains data from three different table and I want to filter result on a specific field with a value, e.g only show fruits.

I can easily to do this with a parameter, I just put [Enter criteria] in design view for the field I want to filter on. But this method is not ideal since the user could easily misspell and has to rerun form if he/she wants to change this value.

I have now put a Combobox in my form which I populate with data that user may filter on.

I build an expression in design view for my form on the field I want to filter on, looking like this.

[Forms]![frmMyData]![MyComboBox]

I enter event handler in properties for my Combobox and creates a macro after update looking like this.

Private sub MyComboBox_AfterUpdate()

DoCmd.OpenQuery "qryMyData"


DoCmd.OpenForm "frmMyData"

End Sub

But nothing happens. Shouldn't query filter on what's in my Combobox and form show data that query generates? With nothing, I mean that query doesn't contain any data.

[samot79]

I will try to rephrase my question.

How can I use value in an unbound Combobox in a form as criteria in a query?

I want to run the query each time I change the value in Combobox.

[aytee111]

1 - query used as record source shows ALL records you would like to see
2 - combobox shows all values which can be used in filtering
3 - in the AfterUpdate event of the combobox,
Me.Filter="field_in_record_source=" & Me!comboboxname
Me.FilterOn=True
Me.Requery

Note: the value used in the filter here is shown as a number. If it is a text field on the table, surround it with single quotes; if it is a date field, surround it with #

[samot79]

Query actually works now, it shows only data I want. But the form is empty.

[aytee111]

Which line isn't working? Please be more descriptive of your issue.

[davegri]

Is qryMyData the recordsource for the form?

[June7]

Don't need to open the query object. Set the query as the form's RecordSource.

I never use dynamic parameterized queries. I prefer the VBA method described by aytee111.

More info in http://www.allenbrowne.com/ser-62.html.

[samot79]

But that is whats weird, I have query as forms RecordSource.

But if I enter query builder from record source and run it, outcome is no data. Even if I populated my combobox with a proper value. But if I run query when I have my form open, than query have correct data. Just not working with form.

[June7]

So the form you want to filter is already open and this form has the combobox on it? Requery the form.

Private Sub MyComboBox_AfterUpdate()
Me.Requery
End Sub

If you want the form to open with all records then set the query dynamic parameter with LIKE operator and wildcard:

LIKE [Forms]![frmMyData]![MyComboBox] & "*"

[samot79]

Thanks a million! You have no idea how much I struggled with this today. I can try myself, but would it be possible to just add another combobox like this if I want to filter on one more criteria?

[June7]

Criteria applied to another field? Yes. Use the LIKE operator and wildcard. Review the link referenced in post 7.

[samot79]

Thanks again!