Don't display datasheet if select query returns zero rows

[Sarah]

I'm doing some simple data validation on entry on a form. I have a
select query that's triggered on the AfterUpdate property of the Last
Name field on the form. The query searches through all of the records
for matching Last Names and displays matches in a datasheet. How, in
VBA, can I tell it not to show the datasheet if the query doesn't find
any matches?

Thanks in advance,
Sarah

 

[Allen Browne]

Queries are just for data retrieval. They don't have the power you need.

Instead, create a form. It can be in Datasheet view if you want it to look
like a query, and you can base it on the query that returns the right
records (possibly none.)

You can now cancel the Open event procedure of the form if there are no
records:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "no records"
End If
End Sub

You will need error handling in the event that does the OpenForm, so you can
trap and ignore the error 2501 that is Access' way of notifying you the form
did not open.

A completely different approach would be to DLookup() the primary key field
from the query before you open it:
If IsNull(DLookup("ID", "Query1")) Then
MsgBox "no records"
Else
DoCmd.OpenQuery "Query1"
End If