VBA syntax for finding an empty query

[aphelps]

Howdy--

I have a DB with many forms, each containing many canned queries (most of which have at least one parameter passed to them), attached to buttons. Many of these queries come up with no records (and should, that's not the problem).

What I'd like to be able to do is, click a button, enter the necessary parameters, and then, if no records fit the parameters, pop up a message box that says "no records" or some such, then closes both the query and the msgbox with the "OK".

I just can't suss out how to make Access see if the query came up empty. Below is one example of how I have tried this. The blue line is where the problem is, I am sure, but I have tried at least a dozen different ways of saying this and can't find the right one (if there is a right one). This includes using stDocName, = 0 instead of isnull, is null after the query name, etc.:
================
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Dim stDocName As String

stDocName = "qselAllVisitsForAGivenNest"

DoCmd.OpenQuery stDocName, acNormal, acReadOnly

If IsNull(qselAllVisitsForAGivenNest) Then GoTo Empty_query
Exit Sub

Empty_query:
MsgBox "This query returned no records."
DoCmd.Close acQuery, stDocName, acSaveNo
Exit Sub

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub
==================

I get a variety of error messages which I can detail if necessary. Does anyone know if this is possible (it should be), and what the syntax is to do it?

Thanks for any input!

 

[Mile-O]

If DCount("aFieldInYourQuery", "yourQueryName") = 0 Then
    MsgBox "No records found.", vbInformation, "Example"
    Exit Sub
Else
    [i]whatever you have planned[/i]
End If

 

[Mile-O]

Private Sub Command19_Click() 

    On Error GoTo Err_Command19_Click 

    If DCount("[b]aFieldInYourQuery[/b]", "qselAllVisitsForAGivenNest") = 0 Then
        MsgBox "No records found.", vbInformation, "Example"
        Exit Sub
    Else
        DoCmd.OpenQuery qselAllVisitsForAGivenNest, acNormal, acReadOnly 
    End If

Exit_Command19_Click: 
    Exit Sub 

Err_Command19_Click: 
    MsgBox Err.Description 
    Resume Exit_Command19_Click 

End Sub

 

[aphelps]

Mile---

I gave it a shot, but as I suspected, there's a problem in that it's a parameter query and it's trying to read the query results without knowing the nest number. This will probably work for some of the other queries, but most of them require a parameter input from the user.

By opening the query first, I get the dialog box asking for the necessary parameter(s), but this way I don't. I also tried this:
===========

DoCmd.OpenQuery stDocName, acNormal, acReadOnly

If DCount("NestNumber", "qselAllVisitsForAGivenNest") = 0 Then

MsgBox "No records found.", vbInformation, "Example"
DoCmd.Close acQuery, stDocName, acSaveNo

=====================

which is basically just reversing the order of how you gave it. But it's still looking for the parameter in the DCount line (that is, just opening the query first doesn't help, I guess it's trying to open it again?). What I need, I guess, is a way to store the parameter as a variable from the initial dialog box, and I don't know how to do that. I think I could figure out how to do it with the code above, but the user would have to enter the nest number twice, which seems silly.

Thanks for your help, I'm closer now than I have been.

 

[Mile-O]

Personally, I've never seen the need for these annoying 'pop up parameters' in a database as they, in the case of queries, are easily quelled by referencing values on forms or by using a public function with a public variable in the query.

Why not let the user pick the parameters on a form and then change the queries to reference the relevant controls on the form?

 

[AncientOne]

Have you considered a slightly different approach? Instead of opening the actual query, construct a temporary QueryDef using the CreateQueryDef method and using an empty string for NAME.
Then count the records that this returns. If zero, exit the sub, otherwise invoke the query itself.

 

[aphelps]

In some cases, I do have the parameters selected on a form, generally for multi-parameter queries when a whole set of them have one or more parameters in common. However, I don't think it is any less appropriate to use the easier parameter query method in many cases, especially when only a single parameter is called for. I don't find them annoying, I find them convenient, insofar as I get the same functionality without having to create dozens of new forms.

As for the QueryDef, I have been playing with that, actually, but Access doesn't seem to want to recognize QueryDef as a valid variable type. I haven't had the time yet this morning to dig into it anymore but I will be doing so right now. I believe I have to explicitly reference DAO in some way...

Thanks again.

 

[AncientOne]

CreateQueryDef is a method, not a variable. If you consult Help using this as a search string, you'll find several examples. By using an empty name, your QueryDef becomes temporary and dies when the procedure goes out of scope. I would have thought this would be a good way out of your dilemma.

 

[aphelps]

Understood. I haven't tried the CreateQueryDef method, but a coworker and I have tried the following, in the same vein:

========================

Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strInput As String

strInput = Inputbox("Enter a nest number, no hyphens:", "Nest Number")

Set qd = CurrentDb.QueryDefs!qselAllVisitsForAGivenNest

qd.Parameters(0) = strInput
Set rst = qd.OpenRecordset
MsgBox "Number of Records is " & rst.RecordCount

If rst.RecordCount > 0 Then
DoCmd.OpenQuery "qselAllVisitsForAGivenNest", acViewNormal, acReadOnly
End If
Exit Sub

=================

This works--except that if the RecordCount IS > 0, the user has to re-enter the parameter, as the query starts again from scratch. We can't figure out how to get Access to just display the Recordset. If you know a way to get this to happen, that'd be great--

The crux of the issue is, I'm trying to avoid the user having to enter the nest number twice, and I can't figure out how to do it.

 

[AncientOne]

I'm sorry, but having read through these posts more carefully and realizing that you don't want to use form-based parameters, I have formed the opinion that this is not a valid approach. Sorry to have misled you.

 

[AncientOne]

Except to remark that that there are other ways to do what you want. It depends on how you are using these queries.

For example you could use your queries to produce reports in print preview, where it is possible to use the OnNoData property to cancel printing or preview.

Or you could use your method to find the recordcount (You need to use MoveLast, MoveFirst to return an accurate count)and print out the resulting recordset to a temporary table.


Or your parameters could be mediated through variables stored in modules i.e. your parameters could be the return value of a function.

I don't know whether a real authority like Pat Hartman could advise you on a procedure to send parameters to a saved query without the intervention of the pop-up parameter window. That would be your last court of appeal, I think.

 

[aphelps]

I thought I'd post back here how I finally defeated the evil Access gods. I wound up putting textboxes on the main switchboard form where the user can enter up to three variables. S/he then must select the query they want to see. This gets me out of creating dozens of new forms (my main problem with Mile's suggestion above). Basically, I've glopped together multiple suggestions into one solution that works.

===========================
Dim stDocName As String
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
stDocName = "qselAllVisitsForAGivenNestAndYear"

Set qd = CurrentDb.QueryDefs!qselAllVisitsForAGivenNestAndYear
qd.Parameters(0) = Me!NestNo
qd.Parameters(1) = Me!Year
Set rst = qd.OpenRecordset(dbOpenSnapshot, dbReadOnly)
MsgBox "This query has returned " & rst.RecordCount & " records."

If rst.RecordCount > 0 Then
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
End If
rst.Close
Exit Sub
===========================

For the record, I still think it's stupid that there's no way to pass a parameter using DoCmd.OpenQuery. Thanks for your help.