Searching with Multiselect fields on a form

[malcprice]

I have a form with 4 multi select fields that I wish to allow user's to select and the results are displayed in a sub form, on the same Form.

1) How do I produce an unbound field which will show a lists the search criteria

2) How do you perform the search and display the results.

Any help would be appreciated

Many regards

Malc

 

[BSman]

A good method to use is to put your "filter criteria" controls on the header or footer of the form. The controls should all have a blank control source. If any of the controls are combo boxes, set up the appropriate query or list in the row source property.

Include on the footer or header a "Find data" button. When it's clicked, use code like the following (I'll assume two string criteria controls: txtFind1 and txtFind2):

dim strFilter as string
strFilter = ""
if me.txtFind1 > " 0" then
strFilter = "[Col1] = '" & me.txtFind1 & "'"
end if
if me.txtFind2 > " 0" then
if isnull(strFilter) then
strFilter = "[Col2] = '" & me.txtFind2 & "'"
else
strFilter = strFilter & " And [Col1] = '" & me.txtFind1 & "'"
end if
end if
me.filteron = true
me.filter = strfilter
me.requery

To turn off the filtering you can just do the following:

me.filter = ""
me.requery

The code above assumes that the values are string values, thus the single quotes that will surround each value. If they are dates, use # instead of '. If they are numbers, don't use any character.