Query/ Form (2003)

Topic

New Reply

I create search menu by using form and query. Form to let use to entry criteria, then click “Search” to run query. I want database to pops up warning message like “no result find base on the criteria” when query can’t find anything base on the criteria that user input in the form.

Thanks

Reply | Quote

Replies

You could use the DCount function:

If DCount(“*”, “NameOfTable”, “SearchCriteria”) = 0 Then
MsgBox “There are no records matching the criteria.”, vbInformation
Else
‘ Open query, or …
End If

In the above code, you must replace NameOfTable with the name of the table, and SearchCriteria with the criteria as set by the user, for example

“ID = ” & Me.txtSearch

again with the correct names substituted.

Reply | Quote

Thank.

I have more than one ceriteria, so how should I put together by DCount?

Should I put the code on “Search” button?

Thanks

Reply | Quote

You can use any combination of criteria, for example

“ID = ” & Me.txtSearchID & ” AND OrderDate = #” & Format(Me.txtSearchDate, “mm/dd/yyyy”) & “#”

or

“ID = ” & Me.txtSearchID & ” OR CustomerName = ” & Chr(34) & Me.txtSearchName & Chr(34)

Reply | Quote

How about I want input data is exactly match with data in table? What I should put instead of “*”

“ID = ” & Me.txtSearchID & ” OR CustomerName = ” & Chr(34) & Me.txtSearchName & Chr(34)

What is “Chr (34)?” What’s “ID=”

Thanks

Reply | Quote

The example I posted is for an exact match.

The field names ID and CustomerName were just examples. You should use the field names from your own table instead.

Chr(34) is the double quote character “. This is used to surround text values in a string with ” characters.

Reply | Quote

Thanks

Reply | Quote