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]

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 <Ba******@Verizon.net> wrote:

[Paul]

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" <Ba******@Verizon.net> wrote in message
news:4f********************************@4ax.com...

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 <Ba******@Verizon.net> wrote:

[John Baker]

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" <pz****@rogers.com> wrote:

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" <Ba******@Verizon.net> wrote in message
news:4f********************************@4ax.com.. .

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 <Ba******@Verizon.net> wrote:

[Paul]

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

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("YourQueryName", 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" <Ba******@Verizon.net> wrote in message
news:ig********************************@4ax.com...

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" <pz****@rogers.com> wrote:

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" <Ba******@Verizon.net> wrote in message
news:4f********************************@4ax.com. ..

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 <Ba******@Verizon.net> wrote:

[Bas Cost Budde]

Paul wrote:

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

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("YourQueryName", 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

or,

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

[Trevor Best]

Bas Cost Budde wrote:

Paul wrote:

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

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("YourQueryName", 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

or,

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

Or

Sub Form_Open(Cancel As Integer)
Cancel= me.recordsetclone.recordcount=0
End Sub

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

[Bas Cost Budde]

Trevor Best wrote:

Or

Sub Form_Open(Cancel As Integer)
Cancel= me.recordsetclone.recordcount=0
End Sub

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

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

I like the Cancel= approach.