Only show query if it contains results.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to writing code in access, but have managed to build a large
database, with security and the works. I've been pulling my hair out trying
to figure out something that is probably very simple, and would appreciate
some help.
I have created a query that checks for matching last names. In the after
update event of a field the query runs and uses the data entered as the
criteria. What I would like to know is, how do I write the code so that the
query only shows if it finds matching results? If there are no matches, I
don't want it to even pop up. I appreciate any assistance on this, since its
been frustrating me for the past week.
Let me know if my explanation isnt clear enough. Thanks...
 
I am new to writing code in access, but have managed to build a large
database, with security and the works. I've been pulling my hair out trying
to figure out something that is probably very simple, and would appreciate
some help.
I have created a query that checks for matching last names. In the after
update event of a field the query runs and uses the data entered as the
criteria. What I would like to know is, how do I write the code so that the
query only shows if it finds matching results? If there are no matches, I
don't want it to even pop up. I appreciate any assistance on this, since its
been frustrating me for the past week.
Let me know if my explanation isnt clear enough. Thanks...
Click to expand...

You cannot stop the query from displaying even if there are no
records.

What you can do is .....
Create another form to display the results of the query (i.e. make the
query this new form's record source.
Set the Form's Default View property to Datasheet.
Records will be displayed the same as query datasheet view.

Code this form's Open Event:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records to display" ' This is optional if you want
Cancel = true ' stops the form from opening
End if

Then instead of opening the query, open this form. You will need to
use error handling as the above cancelling of the form opening will
generate error 3021.

On Error GoTo Err_Handler
DoCmd.OpenForm "FormName"
Exit_Sub
Exit Sub
Err_Handler:
If Err = 3021 Then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_Sub
 
Thanks soo much!! It works like a champ! Like I said, I figured it was a
simple fix, but just couldn't get my head around it. Thanks again!!
 
Back
Top