| re: Testing Query with NO Result in it
"John Baker" <Baker.JH@Verizon.net> wrote in message
news:8lkg00tge70eqeeovubdpgkjbghtv1b997@4ax.com...[color=blue]
> Hi:
>
> Most of the time when I do a query and it has no matches, there is a[/color]
single blank line in[color=blue]
> the result that I can test. I have one query where no result produces a[/color]
response with NO[color=blue]
> lines at all in it. How do I test this kind of query result to determine[/color]
that there is no[color=blue]
> information in it. I am using macros, and on other blank queries I have[/color]
been checking a[color=blue]
> 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[/color]
it if there is no[color=blue]
> data in it.
>
> Suggestions?
>
> Thanks in advance
>
> John Baker[/color]
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
| 
