Multiple criteria matching

[Design by Sue]

I have a search form that has 3 possible search fields, sID, sDescription and sProductGroup. The user can enter any amount of information in these fields and when they click the find button the resulting form displays all records that match any of the information entered, in any possible combination. (Based on the video found here http://www.datapigtechnologies.com/flashfiles/searchform.html) This part works 100% correctly, except that when there is not matching criteria (for example the user enters an sID as 500 but there is not matching record in the table for the ID and there is not other criteria entered in the form) the result is a blank form. This is not user friendly. I want to build into the search form a check for the entered information and give a message box that there is no matching records.

To that end I have found the following code works but if I am to do it this way, I will have to write 9 (I think that is the correct number) of If statements to cover all of the possibilities. I can do this but my fear is that I will miss one possible combination. So my question is - can someone suggest a better way to check these. It has to allow searches for any combination of entries and if the combination of these entries do not result in a match, then produce the message box.


If Me.sID.Value = "" And Me.sDescription.Value = "" And Me.sProductGroup.Value = "" Then
DoCmd.OpenForm "Item Master Edit 2 FRM", acNormal, , , , acNormal
Exit Sub
End If

If Not IsNull(Me.sID) And Me.sDescription.Value = "" And Me.sProductGroup.Value = "" And DCount("[ID]", "[Item Master TBL]", "[ID] = " & sID) = 0 Then
MsgBox "There are no items matching this criteria.", vbExplination, "No Items"
Exit Sub
End If

Thanks!
Sue

 

[AUGuy]

The way I've gone about handling multiple search criteria without unnecessary VBA is use the query to limit the information based on controls in the form.

Lets say you have a form with TextBox1, TextBox2, and TextBox3. You have a listbox on that form tied to the query qry_FilteredData. Your 3 fields in the query are tied to Textboxes 1-3 as their criteries with the Like "*" & Forms!FormName!TextBox1 & "*".

This is just one way to handle it, but requires no VBA and is easy to implement within a form. Make sure you requery the listbox when your textboxes change. Additionally, you can do an 'If Control = "" then * else Control' type logic if you don't want the wildcards to be active when they actually enter in criteria in the textbox.

 

[Design by Sue]

Thanks - not clear on the listbox part of this. How does that fit into the process?

Sue

 

[boblarson]

Okay, again I will tell you. "" is not the same as a NULL. So, you should check for BOTH but you can check for both by using the LEN function.

If [B][COLOR=red]Len([/COLOR][/B]Me.sID [B][COLOR=red]& vbNullstring) = 0[/COLOR][/B] And [B][COLOR=red]Len([/COLOR][/B]Me.sDescription [B][COLOR=red]& vbNullstring) = 0 [/COLOR][/B]And [B][COLOR=red]Len([/COLOR][/B]Me.sProductGroup [B][COLOR=red]& vbNullstring) = 0[/COLOR][/B] And DCount("[ID]", "[Item Master TBL]", "[ID] = " & sID) = 0 Then
   MsgBox "There are no items matching this criteria.", vbExplnation, "No Items"
   Exit Sub
End If
 
If [B][COLOR=red]Len([/COLOR][/B]Me.sID [B][COLOR=red]& vbNullstring) = 0 [/COLOR][/B]And [B][COLOR=red]Len(Me.sDescription & vbNullstring) = 0[/COLOR][/B] And [B][COLOR=red]Len([/COLOR][/B]Me.sProductGroup [B][COLOR=red]& vbNullstring) = 0[/COLOR][/B] Then
   DoCmd.OpenForm "Item Master Edit 2 FRM", acNormal, , , , acNormal
Exit Sub
End If

And what is vbExplination? That isn't a built in constant. You have that in your message box code. Not only that but Explination should be Explanation.

 

[Design by Sue]

Bob - so true, but that's the least of my worries - though I will take your advise.

Do you know of a way to streamline this process or do I have to write the code for every possible combination?

OR is their a way to detect that there are no results in the form that is opening (I remember doing that with a report) and then trigger the message box?

Sue

vbExplination is a typo should be vbExplanation! DUH

 

[boblarson]

Personally I would write it this way:

Dim strWhere As String
If Len(Me.sID & vbNullString) > 0 Then
    strWhere = "[ID]=" & Me.sID & " AND "
End If
If Len(Me.sDescription & vbNullString) > 0 Then
    strWhere = strWhere & "[Description]=" & Chr(34) & Me.sDescription & Chr(34) & " AND "
End If
If Len(Me.sProductGroup & vbNullString) > 0 Then
    strWhere = strWhere & "[ProductGroup]=" & Chr(34) & Me.sProductGroup & Chr(34) & " AND "
End If
If Right(strWhere, 5) = " AND " Then
    strWhere = Left(strWhere, Len(strWhere) - 5)
End If
If DCount("[ID]", "[Item Master TBL]", strWhere) = 0 Then
   MsgBox "There are no items matching this criteria.", vbExplnation, "No Items"
   Exit Sub
End If
 
   DoCmd.OpenForm "Item Master Edit 2 FRM", acNormal, WhereCondition:=strWhere

 

[Design by Sue]

Thanks Bob - Would your code completely replace the coding that had the criteria for opening that is already on the opening form? If so, I would like to continue with what I have so far and trap no records. I found the answer to that here (well almost)":
http://windowssecrets.com/forums/sh...2010-Display-message-when-no-matching-records

The only problem now is the error trapping - where do I put it? I tried to put it on the onerror event of the opening form, on the onerror event of the search form (from where the form is opened) and I even tried putting it in the button coding that opens the form but with no effect - the message box error 2501 still show up.

Because everything else seems to be functioning fine I would like to get this error handling cleaned up and use what I have so far.

Sue

 

[boblarson]

Yes, the code I gave you should replace your code entirely. It builds the appropriate where condition value based on what is actually there and then it looks, using the DLookup, if there are records existing and then opens the form if there are.

As for error handling maybe this (by our own pbaldy) will help:
http://www.baldyweb.com/ErrorTrap.htm

 

[Design by Sue]

Go it. As so often in the past - thank you Bob (and also AUGuy) you have been so helpful, but I finally found the solution by checking for an empty form as in my link before. The error check went in the button code and ended up like this

On Error GoTo Err_Find_BTN_Click

DoCmd.OpenForm "Item Master Edit 2 FRM", acNormal, , , , acNormal


Exit_Find_BTN_Click:
Exit Sub

Err_Find_BTN_Click:
If Err.Number = 2501 Then
Resume Next
Else

MsgBox Err.Description
Resume Exit_Find_BTN_Click
End If

Sue

 

[Design by Sue]

Bob - thanks I will bookmark this for future needs like this!

Sue