Detecting and Empty Query Result and acting on it!

[JHB]

Hi:

I have a query that is used as a basis for a form, however when the
query does not find an results, the form just does not work out at
all!!

I would like to be able to detect that the query will produce no
results BEFORE I open the form, and if it does have no results I would
like to issue a message and move down a different program,m path.

My challenge is in determining when the query result will be empty. I
am just not certain how to set up to detect that condition.

I am using Macros, and set the following condition, but its not
acceptable:

IsEmpty([IUU05 Set Up Investment Action Units for input]![Investment
ID])

"UU05 Set Up Investment Action Units for input" is the name of the
query I am interested in testing. Any advice would be much
appreciated.
..

Thanks in Advance

John Baker

 

[Guest]

Try this:

If DCount("[AnyField]","YourQuery")>0 Then
DoCmd.OpenForm "YourForm"
Else
MsgBox "No Data!"
End If

Steve

 

[John W. Vinson]

I would like to be able to detect that the query will produce no
results BEFORE I open the form, and if it does have no results I would
like to issue a message and move down a different program,m path.

Steve's suggestion is one approach; another is to use the form's Open event:

Private Sub Form_Open(Cancel as Integer)
If Me.Recordsetclone.Recordcount = 0 Then
MsgBox "No data to show!", vbOKOnly
Cancel = True
End If
End Sub

This will pop a warning and just close the form before displaying it.

John W. Vinson [MVP]

 

[JHB]

Thank you very much for your suggestion.

Since I am somewhat of a neophyte in VBA, could I ask for just one
additional piece of advice:

When I find this situation, I need to run a MACRO (already developed)
called ZeroRecords. Can you give me the VBA call to execute this
macro. I know its NOT: RunMacro(ZeroRecords) [ I tried this and it
didn't compile] but I don't know what it is, and my Access manual is
silent on the issue.

Thanks very much

John Baker

 

[John W. Vinson]

. I know its NOT: RunMacro(ZeroRecords) [ I tried this and it
didn't compile] but I don't know what it is, and my Access manual is
silent on the issue.

The argument to RunMacro needs to be a Text String - and to tell Access that
something's a text string you need to enclose it in quote marks:

RunMacro("ZeroRecords")

John W. Vinson [MVP]