Testing Query with NO Result in it

[John Baker]

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

[Salad]

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.Openr ecordset("query nam"...)
If rst.Recordcount = 0 then...

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

[Trevor Best]

On Fri, 16 Jan 2004 21:08:28 GMT in comp.databases. ms-access, John
Baker <Ba******@Veriz on.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.

[Fletcher Arnold]

"John Baker" <Ba******@Veriz on.net> wrote in message
news:8l******** *************** *********@4ax.c om...

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(Cance l As Integer)

On Error GoTo Err_Handler

Cancel = True

If Me.RecordsetClo ne.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

[John Baker]

Thanks again guys..You have resolved the problem!!

Regards

John Baker