Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

microsoft access search macro

microsoft access search macro

microsoft access search macro

In access i have the macro "requery", that searches for a program name using the description for that program name. The problem is that when you type in a word that cant be found in the description, the form goes blank. How can fix it so that a message comes up and says "there are no matches"?

RE: microsoft access search macro

It would be easier to advise you if you could post a copy of your macro here.

Essentially, you need an if statement somewhere in your loop that searches for the file name.  


If (condition that identifies if there is a match or not) [color blue]Then[/blue]
   (statements that result in pulling the form)
Else 'i.e. no match is found
   MsgBox "No matches were found."
   Exit Sub 'This kills the macro.  
'You could also try a "Goto" or "Exit Do" to take you to a point in your code.

End If

If you post your actual macro, we might could give you a piece of code that you can copy and paste.

There are several options with a message box, for instance, you could get it to pop up an input box saying "No matches found for {your search string}. Search again for: {input box for you to input corrected search string}" with option buttons "OK" and "Cancel" where "OK" searches again while "Cancel" gets you out of the loop/macro.

RE: microsoft access search macro

You could also slot in there an On Error Goto statement, assuming that no match results in an error for the code. Of course, the following will trap all errors and produce the same "No match found" dialog box. The best way to preclude this is to know where to put the On Error Goto statement into the code, and then put an On Error Resume Next statement after the search statement but before the statement that brings up your form.  


On Error Goto LineName

(code statements)

Exit Sub 'So that normal operation does not invoke the Msgbox command

LineName: Msgbox "No match was found."

End Sub

RE: microsoft access search macro

Here is the the code to find the description in my form

Private Sub SEARCH_Click()
Private Sub cmdSearch_Click()

    DoCmd.FindRecord , , acNext

    Exit Sub

    MsgBox Err.Description
    Resume Exit_SEARCH_Click

End Sub

RE: microsoft access search macro

Also when the screen goes blank, it is not an error. It just means there is no matches and the fields are blank. Im thinking I need "if description is null the message "there are no message" will come up", but i have no clue in how I should do this

RE: microsoft access search macro

Well, your only instructions are to find the record by going to the next record if it's not found.  You're ending up on the last record - i.e. the blank line at the bottom of a table that you can still write to.  Note that if your table had default values for some fields, they'd be filled here.

As far as I know, the only way to print a message box when no record is found is to complicate your code by first checking the field to see if a record exists and then only calling that record if it exists - else displaying the message box.

Unfortunately, it's been so long since I've written VB for Access ('99) that I have no idea how to do that anymore!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close