Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / January 2006

Tip: Looking for answers? Try searching our database.

Only show query if it contains results.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mowol - 12 Jan 2006 18:48 GMT
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...
fredg - 12 Jan 2006 19:14 GMT
> 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
[quoted text clipped - 7 lines]
> been frustrating me for the past week.
> Let me know if my explanation isnt clear enough. Thanks...

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

mowol - 18 Jan 2006 19:20 GMT
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!!

> > 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
[quoted text clipped - 38 lines]
>     End If
>     Resume Exit_Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.