How to Automatically close an empty query result?

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

Guest

I have a few queries running in macro. I want to automatically close the
queries that have no results.

How can i automatically close the query based on it's results?
 
Hi,


Test the number of records that will be returned before running the big
query.



Pseudo code:


If 0 <> DCount("*", "savedQuery") then
... run the query...
End If



May be nice, in the negative, to display a MsgBox, as confirmation that
there is no data (so the end user knows it is NOT a bug in YOUR application
that make the data sometimes appear, sometimes not).



Hoping it may help,
Vanderghast, Access MVP
 
where do i place the code if all my queries are running one after another in
a saved macro?
 
Hi,


I would discourage the use of macro and suggest to use VBA instead, but
if someone forces you to use macro, first display the "condition" column
(from a button on the toolbar), and supply the condition in front of each
"Action" line to be affected by the condition. You may try in the macro
newsgroup section for more details about the use of macros, per se.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks michael for your help but I'm just getting started programming.

When i tried to create a module for the code it wont let me run it. Evertime
i try to run it it asks for a macro name???
 
Hi,

In a standard module (by opposition to a class module), add the line


Option Explicit


(or check the "Require Variable Declaration" option under Tools | Options,
Editor Tab) among the first lines, then, type:


Public Sub MySub( )


The editor should respond by adding a

End Sub


You will type your VBA code between these lines. MySub can be changed to
any name you like.

To run a select query, a line of code can be:


DoCmd.RunSQL "QueryNameHere"


So, you can use a test condition, like:


---------------------------
Public Sub MySub()

On Error Resume Next ' if there is an error, continue

If 0 <> DCount:("*", "SavedQuery1") Then
DoCmd.RunSQL "SavedQuery1"
Else
MsgBox "No data from SavedQuery1", vbInformation + vbOKOnly
End If

If 0 <> DCount:("*", "SavedQuery2") Then
DoCmd.RunSQL "SavedQuery2"
Else
MagBox "No data from SavedQuery2", vbInformation + vbOKOnly
End If

Debug.Assert 0=Err.number ' we assumed there was no error
' so, assert it

End Sub
------------------------

Note that we added some very basic error handling: if an error occurs, the
end-user is probably clueless about how to solve it, so we decided to
continue, but, while we will test-debug our application, if an error occur,
we should be able to examine the Err.Number and Err.Description, and,
eventually, rerun the code, step by step, in debug mode (which is then much
easier than with macros). Furthermore, since we modified nothing, we don't
have to undo (rollback) any changes, if an error occur. That basic error
handling sounds quite appropriate, for a first draft.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top