EJC wrote:
Hi Again,
first off thanks for all the help, I have now achieved a further level
of the functionality I was after. I have followed this method
(http://www.microsoft-accesssolutions..._parameter.htm)
and adapted the parameter example to work with my combo box to achieve
this. This works fine for one combo box however as soon as I try to
add the same functionality to the other combo boxes on my form it
breaks down and I end up getting the empty record set returned. Any
ideas as to why this is?
Yes. You need to adjust your query for each and every possible instance
of true and false. You only satisfied 1 criteria, you need to satisfy
all criterias.
That is the problem with your query....you need to have lots of ORs in
it to make it work. Maybe I should say lots or Ors and Ands to meet all
of the truth table results. In fact, If you have more than 2 or 3 text
boxes I would hate to be the one to modify your query later on. It will
become extremely complex and a pita if you ever need to update or modify it.
If your list in the combo is basically small, you might want to consider
creating a function and use it in the combo's query. Lets say the form
is called MF and the table fields to compare are Field1...Field3 and the
form fields are Text1...Text3. Create a column like this
PassFail : DoesItPass([Field1],[Field2],[Field3])
This will pass the 3 fields to compare to the 3 text fields on your form
In the criteria row for PassFail enter
True
Public Function DoesItPass(var1, var2, var3) As Boolean
Dim bln1 As Boolean
Dim bln2 As Boolean
Dim bln3 As Boolean
bln1 = True
bln2 = True
bln3 = True
If Forms!MF!Text1 > "" then
If NZ(var1,"") <> Forms!MF!Text1 then bln1 = False
endif
If Forms!MF!Text2 > "" then
If NZ(var2,"") <> Forms!MF!Text2 then bln2 = False
endif
If Forms!MF!Text3 > "" then
If NZ(var3,"") <> Forms!MF!Text3 then bln3 = False
endif
DoesItPass = (bln1 And bln2 And bln3)
end function
Now only those records in the combo query where DoesItPass is true will
be displayed. A function like this will be MUCH easier to modify and
update later on. BTW, this function would be created under the Modules tab.