By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Detecting an empty query result

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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:
Nov 13 '05 #2

P: n/a
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:

Nov 13 '05 #3

P: n/a


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:


Nov 13 '05 #4

P: n/a
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:

Nov 13 '05 #5

P: n/a
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"
Nov 13 '05 #6

P: n/a
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.
Nov 13 '05 #7

P: n/a
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.
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.