Query produces no records

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I have a Query by Form , with a button that runs a macro and opens a
document.

The document is opened through a module.

-----------------------------------------------------------------------

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryThankYouLettersEnvelopes")
qdf.Parameters(0) = _
Forms!frmThankYouLettersEnvelopes!TYLEBatchNumber
Set rst = qdf.OpenRecordset

'Set rst = CurrentDb.OpenRecordset("QryThankYouLetters")
If rst.RecordCount > 0 Then

DoCmd.RunMacro "TYLEnvelopes"
Application.FollowHyperlink "C:\\My Documents\ThankyouLetterEnvelope.doc"

Else
MsgBox "No records found for this batch number."
End If

rst.Close
qdf.Close

---------------------------------------------------

This works fine when there is only the one parameter, but i have another
query that has a number of parameters where some are left empty "Is Null".

eg. [Forms]![frmGenLetters]![GenBatchNumber] or Like
[Forms]![frmGenLetters]![GenBatchNumber] Is Null

(there is also a "Between" function for date values and "
Code: 
" or where
either of these are null)

When I run the similar module to the above I get the error message "Too Few
Parameters Expected (4)"

How can I stop the document from opening if the query is empty.

Thanks

Craig
 
Dear Craig:

Please post the SQL of the query itself. Without that, my guess is
that the controls on which the parameters are based are not always
NULL when you expect them to be, but may be blank instead. To
accomodate both possibilities, use:

Nz([Forms]![frmGenLetters]![GenBatchNumber]) = ""

instead of

Like [Forms]![frmGenLetters]![GenBatchNumber] Is Null

The latter actually does not make sense to me. At a minimum, drop the
"Like." But, certainly, get the query working when it prompts for
parameters first, then work to get it functioning in the code.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top