Searching using combo box input

[key1pop1]

Hey all,

So I have made a search form which looks up info on a table using VBA in such a way (text box version)

If Not IsNull(Me.TxtFitLastname) Then
strWhere = strWhere & "([Last Name] Like ""*" & Me.TxtFitLastname & "*"") AND "
End If

This goes on for several variables that add to a large string which then is applied to the filter of the form.

My question is...
In my table that is being filtered I have a couple of fields which are based off other tables that utilize a combo box where the user can pick multiple values off the combo list. Back on my filter form I am have another combobox (this one does not need to let the searcher to pick multiple values). For the life of me I can not get a stable code that will add this to some kind of string that in turn can be directed at the filter. I know combos don't like WHERE clauses, but I don't know how to approach the issue.

Long story short I need to search for values built off a combo field in a table using VBA.

Thanks in advance!

 

[Beetle]

This is one, among several, of the reasons not to use Lookup fields (i.e. combo boxes) in tables. Because it obscures the value that is actually being stored in the table. You might see the name John Smith, for example, but what is actually being stored is the Primary Key value for John Smiths record from the other table.

My first suggestion would be to get rid of the Lookup fields. Combo boxes in forms are appropriate. Combo boxes in tables are not. For more reasons why see;

http://access.mvps.org/access/lookupfields.htm

Barring that, you're going to have to modify your code so that as it's building the Where string it is looking for the key value of whatever is selected in the combo box (or Lookup field) instead of the value that you see. This may include modifying your use of any delimiters because in most cases the combo boxes displays a text value but stores a numeric value.