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

Testing Query with NO Result in it

P: n/a
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I have been checking a
field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no
data in it.

Suggestions?

Thanks in advance

John Baker
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
John Baker wrote:
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I have been checking a
field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no
data in it.

Suggestions?

Thanks in advance

John Baker


How do you open the query? WIth a recordset?
Set rst = Currentdb.Openrecordset("querynam"...)
If rst.Recordcount = 0 then...

or is the query the recorsource of the form.
If Me.Recordsetclone.Recordcount = 0 then Cancel = True

Nov 12 '05 #2

P: n/a
On Fri, 16 Jan 2004 21:08:28 GMT in comp.databases.ms-access, John
Baker <Ba******@Verizon.net> wrote:
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I have been checking a
field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no
data in it.

Suggestions?


This particular query is not updatable, there are many reasons why
this could be. non-updatable queries will not have a blank line in
them for entering new records. There's nothing you can do to change
that except to change the query so that it is updatable.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #3

P: n/a
"John Baker" <Ba******@Verizon.net> wrote in message
news:8l********************************@4ax.com...
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in the result that I can test. I have one query where no result produces a response with NO lines at all in it. How do I test this kind of query result to determine that there is no information in it. I am using macros, and on other blank queries I have been checking a field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no data in it.

Suggestions?

Thanks in advance

John Baker

John,
Whether you see a 'blank line' or not might be a bit misleading. The
important point is that, blank line or not, the query returns no records.
The difference is whether you can add to the query or not. Imagine you had
an extremely simple table: tblContacts (ConID, ConName) then with a query
like:

SELECT ConID, ConName FROM tblContacts WHERE ConName="xyz"

you would be able to run this query and, if you had no-one called "xyz" in
your contacts table there would be a single blank row where you could add a
new person (with name="xyz" or anything else you chose). However, if your
query was not updateable, like:

SELECT DISTINCT ConID, ConName FROM tblContacts WHERE ConName="xyz"

Then you would see no rows at all.

In either case, you can inspect the form's RecordsetClone.RecordCount
property to decide if you want to open the form - like this:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Handler

Cancel = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Data", vbExclamation, _
"Cannot open form"
Else
Cancel = False
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
This assumes that you have some knowledge SQL and viewing the statement for
your queries and that you know where to insert VBA code. Let me know if
this is an assumption too far.

Fletcher
Nov 12 '05 #4

P: n/a
Thanks again guys..You have resolved the problem!!

Regards

John Baker
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.