Detecting an empty query result

[John Baker]

Hi:

I have a pop up form based on a query.

I am openings the form, and wish to close it immediately if the query has result.

My method is to put a macro in the "on Open" event , which has the following code

IsNull([date]) Close (the form I just opened)

Date is one of the fields in the form.

If the date is null, its supposed to trigger a closing of the form. Unfortunately, what
happens is a blank form appears. I have tried IsEmpty and various other formulations with
no joy! The same thing happens if I use the "Load" or "On Open" events, so I am a bit
perplexed.

If the query DOES have a result, the form appears as it should and the fields are entered.

Can anyone suggest what I should be doing? Is there a proper way to determine if a form
(or a query) is empty?

Best and thanks

John Baker

[John Baker]

Re: Detecting an empty query result

OOPS:

THe second line should have said:[color=blue]
>I am openings the form, and wish to close it immediately if the query has NO result.[/color]

John Baker <Baker.JH@Veriz on.net> wrote:

[Paul]

Re: Detecting an empty query result

Why not just create a recordset based on the table/query and check to see if
there is any data in it. If not, dont open the form and if there is, just
continue with your code.

HTH
Paul

"John Baker" <Baker.JH@Veriz on.net> wrote in message
news:4fl1p0he91 1cggfusugfc83jq 6qla3ulg5@4ax.c om...[color=blue]
> OOPS:
>
> THe second line should have said:[color=green]
>>I am openings the form, and wish to close it immediately if the query has
>>NO result.[/color]
>
> John Baker <Baker.JH@Veriz on.net> wrote:
>
>[/color]

[John Baker]

Re: Detecting an empty query result



Paul:

The real reason is that I am not certain how to do that. Its the testing for empty or
null that has me foxed.

best

John

"Paul" <pzspam@rogers. com> wrote:
[color=blue]
>Why not just create a recordset based on the table/query and check to see if
>there is any data in it. If not, dont open the form and if there is, just
>continue with your code.
>
>HTH
>Paul
>
>"John Baker" <Baker.JH@Veriz on.net> wrote in message
>news:4fl1p0he9 11cggfusugfc83j q6qla3ulg5@4ax. com...[color=green]
>> OOPS:
>>
>> THe second line should have said:[color=darkred]
>>>I am openings the form, and wish to close it immediately if the query has
>>>NO result.[/color]
>>
>> John Baker <Baker.JH@Veriz on.net> wrote:
>>
>>[/color]
>[/color]

[Paul]

Re: Detecting an empty query result

Quite Simple!!!!!
=============== =============== =============== ==
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecords et("YourQueryNa me", dbOpenDynaset)

With rst
If rst.EOF Then
exit sub
Else
docmd.openform "FormName"
End If
End With

dbs.close
set dbs = nothing
set rst = nothing

=============== =============== =============== ===
HTH
Paul


"John Baker" <Baker.JH@Veriz on.net> wrote in message
news:igm1p0hptl ojvphfjgqao123n 4tf6ip1r8@4ax.c om...[color=blue]
>
>
> Paul:
>
> The real reason is that I am not certain how to do that. Its the testing
> for empty or
> null that has me foxed.
>
> best
>
> John
>
> "Paul" <pzspam@rogers. com> wrote:
>[color=green]
>>Why not just create a recordset based on the table/query and check to see
>>if
>>there is any data in it. If not, dont open the form and if there is, just
>>continue with your code.
>>
>>HTH
>>Paul
>>
>>"John Baker" <Baker.JH@Veriz on.net> wrote in message
>>news:4fl1p0he 911cggfusugfc83 jq6qla3ulg5@4ax .com...[color=darkred]
>>> OOPS:
>>>
>>> THe second line should have said:
>>>>I am openings the form, and wish to close it immediately if the query
>>>>has
>>>>NO result.
>>>
>>> John Baker <Baker.JH@Veriz on.net> wrote:
>>>
>>>[/color]
>>[/color]
>[/color]

[Bas Cost Budde]

Re: Detecting an empty query result

Paul wrote:[color=blue]
> Quite Simple!!!!!
> =============== =============== =============== ==
> Dim dbs As Database
> Dim rst As Recordset
>
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecords et("YourQueryNa me", dbOpenDynaset)
>
> With rst
> If rst.EOF Then
> exit sub
> Else
> docmd.openform "FormName"
> End If
> End With
>
> dbs.close
> set dbs = nothing
> set rst = nothing[/color]

or,

if DCount("*","you rqueryname")>0 then docmd.openform "formname"

[Trevor Best]

Re: Detecting an empty query result

Bas Cost Budde wrote:[color=blue]
> Paul wrote:
>[color=green]
>> Quite Simple!!!!!
>> =============== =============== =============== ==
>> Dim dbs As Database
>> Dim rst As Recordset
>>
>> Set dbs = CurrentDb()
>> Set rst = dbs.OpenRecords et("YourQueryNa me", dbOpenDynaset)
>>
>> With rst
>> If rst.EOF Then
>> exit sub
>> Else
>> docmd.openform "FormName"
>> End If
>> End With
>>
>> dbs.close
>> set dbs = nothing
>> set rst = nothing[/color]
>
>
> or,
>
> if DCount("*","you rqueryname")>0 then docmd.openform "formname"[/color]

Or

Sub Form_Open(Cance l As Integer)
Cancel= me.recordsetclo ne.recordcount= 0
End Sub

This will generate a runtime error in the code that opens the form though.

[Bas Cost Budde]

Re: Detecting an empty query result

Trevor Best wrote:[color=blue]
> Or
>
> Sub Form_Open(Cance l As Integer)
> Cancel= me.recordsetclo ne.recordcount= 0
> End Sub
>
> This will generate a runtime error in the code that opens the form though.[/color]

Like "operation was cancelled?" 2501 if I am right.

I like the Cancel= approach.