microsoft access search macro 1

[poppadoc75]

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"?

 

[francesca]

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.

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

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.

 

[francesca]

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.

[COLOR=blue]On Error Goto[/color] LineName

(code statements)

[COLOR=blue]Exit Sub[/color][COLOR=green] 'So that normal operation does not invoke the Msgbox command[/color]

LineName: Msgbox "No match was found."

[COLOR=blue]End Sub[/color]

 

[poppadoc75]

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

Private Sub SEARCH_Click()
Private Sub cmdSearch_Click()

DoCmd.FindRecord , , acNext

Exit_SEARCH_Click:
Exit Sub

Err_SEARCH_Click:
MsgBox Err.Description
Resume Exit_SEARCH_Click

End Sub

 

[poppadoc75]

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

 

[francesca]

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!