MsgBox - Help

[swedish_mania]

Im want to create a msgbox that pops up when a parameter is entered wrong.

Ive created a query to search for customers - When run a box pops up saying 'Please enter surname' - If correct then a record appears.

If the surname is incorrect i get a blank table. I want a msgbox to pop up saying something like 'No Records Found. Please Try Again'.

Ive also created a button on a form which runs the query, this works fine:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Customer Search"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

How can i change this so a msgbox appears if nothing is found?

Any suggestions welcome. Thanks in advance...

 

[Uncle Gizmo]

I realize that this isn't exactly what you asked for, but you might find it's a better way of achieving what you want. Have a look at this example I posted in an earlier post.

 

[swedish_mania]

Not what im after, but still an intersting way of doing it.

How did you create the form within a form? I might try this if i cant do it the other way. Cheers

 

[FoFa]

How about, right before you run your query you do something like this:
if 0 = dcount("ColumnNameInQuery",stDocName) then
msgbox "My Message"....
exit sub
end if

 

[swedish_mania]

How do i run it before the query?

Ive tried your suggestion in various parts of the code i have already got, couldnt get it working.

 

[FoFa]

Put the code I gave you (After you fix it up) right before the DOCMD.OPENQUERY.

stDocName = "Customer Search"
Put Code Here
DoCmd.OpenQuery stDocName, acNormal, acEdit

 

[swedish_mania]

Tried that, the code now looks like this.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Customer Search"

If 0 = DCount("Forename", stDocName) Then
MsgBox "No Records Found. Please Try Again"
Exit Sub
End If

DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

I get the error msg "You cancelled the previous operation".
I thought that might have something to do with 'Exit Sub' before 'End If' but that didnt make a difference.

 

[swedish_mania]

Code Change

Ive changed the code slightly so a msgbox appears if nothing is found, although it appears if a record is found. Can someone please have a look and tell me whats wrong. Cheers

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "CustomerSearch"
DoCmd.OpenQuery stDocName, acNormal, acEdit

strTest = DLookup("Forename", "Client", "Forename = '" & search & "'")
If strTest = search Then
    MsgBox "Records Found"
    Else
      MsgBox "No Records Found. Please Try Again"

End If

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub