If query returns no records...

[gfcaim]

... then i'd like to catch that, display a message to that effect and cancel the report or form or whatever i'm trying to open.

Does anyone have some examples of catching zero records returned queries that i could utilise for my own application?

 

[maxmangion]

in a report you can use the on NoData event !

 

[gfcaim]

Tried that...

In the 'On No Data' event i put 'msgbox "no Data"' just to try it and it dodn't show - any idea why?

What about queries and forms that have no data?

 

[Mike375]

... then i'd like to catch that, display a message to that effect and cancel the report or form or whatever i'm trying to open.

Does anyone have some examples of catching zero records returned queries that i could utilise for my own application?

I don't how to do it with code but with a macro I think I would have the macro open a form based on the query but open the form so you can't see it.

The second part of the macro would open the form I want to see but with the condition column having

[Forms]![FormICantSee]![IDNumberFiled] Is Not Null

That would stop the form being opened.

The last action would close the form you can't see.

A message box form could also be opened on condition

[Forms]![FormICantSee]![IDNumberFiled] Is Null

Mike

 

[Mile-O]

The simplest way, prior to opening the form or report is to use this:

If DCount("*", "MyQuery") = 0 Then
    MsgBox "No records found.", vbInformation
    Exit Sub
End If

Just insert them before the lines that open the form or report. And change MyQuery to the name of the query that underlies the form or report.

A report, of course, has the NoData event, whereby you can set Cancel = True and it will close.

On a form, you can use the OnOpen event to determine the records and use Cancel = True if there are none to stop opening the form.

My four lines above will stop either a query or form from being opened prior to even trying.

 

[Mike375]

Mile,

Can that be out on a label and is MyQuery replaced by form name and will it go onto open the form if records exist.

Mike

 

[Mile-O]

Can that be out on a label

What do you mean by "go out on a label"?

and is MyQuery replaced by form name and will it go onto open the form if records exist.

MyQuery is replaced by the query that's bound to either the form or report that's being opened.

You'd need to put the open form code in after the four lines above.

ie..

If DCount("*", "MyQuery") = 0 Then
    MsgBox "No records found.", vbInformation
    Exit Sub
End If
DoCmd.OpenForm "MyForm", acNormal

 

[Mike375]

Typo...put on a label OnClick

 

[Mile-O]

Yip. That's fine.

 

[Mike375]

Yip. That's fine.

I am advancing. I only have 1999 macros to go

 

[Mile-O]

You do know that you can convert macros to code?

It makes a module for you. Once it makes a module, copy the code within the sub and paste it in the relevant form event.

 

[Mike375]

You do know that you can convert macros to code?

It makes a module for you. Once it makes a module, copy the code within the sub and paste it in the relevant form event.

Would it not be better if it is done as a module and that way it would be like a macro in that changes could be made while the data base is fully open by bringing the data base window forward. We do that quite often with macros, especially some of the ones that change the record source.

This is a recordsource macro I just converted. It has some red writing which I have shown. What I do with it. It sure makes for a lot of writing.

Option Compare Database
Option Explicit

'------------------------------------------------------------
' 016Select
'
'------------------------------------------------------------
Function 016Select ()
On Error GoTo 016Select_Err
Forms![2ProspectT].RecordSource = "016Select"
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record


16 Select_Exit:
Exit Function

16 Select_Err:
MsgBox Error$
Resume 016Select_Exit
End Function