Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query/ Form (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query/ Form (2003)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query/ Form (2003)

    Thank.

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

    Should I put the code on "Search" button?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query/ Form (2003)

    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)

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query/ Form (2003)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query/ Form (2003)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query/ Form (2003)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •