Help Needed: Message Box Error Message

[Steph]

I have a query set up to look up records that contain a
certain value in a text box (txtSortByIONumb) on a form
(SearchBy). I have the following expression under the
query criteria:

Like Nz([Forms]![SearchBy]![txtSortByIONumb],"*")

If a user enters a value in the text box that is not in
the database, how do I create an error message to pop up
that says "This value does not exist. Please enter
another value."

Thanks,

Steph
..

 

[Allen Browne]

Queries can't do that, as they are not really designed as a user interface.

However, you could make a form in datasheet view (looks like a query) based
on this query, and cancel its Open event if there are no records:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No matches. Try again."
Cancel = True
End If
End Sub

Alternatively, if the target were a report, you could cancel the report's
NoData event.