Abort opening report if there are no records.

  • Thread starter Thread starter user
  • Start date Start date
U

user

Hi,

I have a form with query criteria, then a button to open another form to
display the query results. I want to capture when no records are found so
as not to open an empty result form. I tried a bunch of stuff like:

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

Dim d As DAO.Database
Dim q As DAO.QueryDef
Dim r As DAO.Recordset

Set d = CurrentDb
Set q = d.QueryDefs("qryLastInitialReport")
Set r = q.OpenRecordset() ' error below in this line

If r.RecordCount > 0 Then
' open the report
Else
MsgBox "No records found", vbOKOnly
End If

=============
but when I run this I get:

Run-time error '3061'

Too few parameters. Expected 1.

TIA, Doug
 
user said:
Hi,

I have a form with query criteria, then a button to open another
form to display the query results. I want to capture when no records
are found so as not to open an empty result form. I tried a bunch of
stuff like:

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

Dim d As DAO.Database
Dim q As DAO.QueryDef
Dim r As DAO.Recordset

Set d = CurrentDb
Set q = d.QueryDefs("qryLastInitialReport")
Set r = q.OpenRecordset() ' error below in this line

If r.RecordCount > 0 Then
' open the report
Else
MsgBox "No records found", vbOKOnly
End If

=============
but when I run this I get:

Run-time error '3061'

Too few parameters. Expected 1.
Click to expand...

Probably the query makes reference to a control on a form, as a
criterion or in a calculated field. Access knows what these references
mean and resolves them for you when you open queries, forms, or reports
normally. But DAO doesn't know what they are, so when you open a
recordset it can't resolve what appears to it to be a parameter. You
have to resolve any such parameters in your code before opening the
recordset. Here's one convenient way:

Dim d As DAO.Database
Dim q As DAO.QueryDef
Dim r As DAO.Recordset
Dim p As DAO.Parameter

Set d = CurrentDb
Set q = d.QueryDefs("qryLastInitialReport")

For Each p in q.Parameters
p.Value = Eval(p.Name)
Next p

Set r = q.OpenRecordset()

If r.RecordCount > 0 Then
' open the report
Else
MsgBox "No records found", vbOKOnly
End If
 
Dirk Goldgar said:
Probably the query makes reference to a control on a form, as a
criterion or in a calculated field. Access knows what these references
mean and resolves them for you when you open queries, forms, or reports
normally. But DAO doesn't know what they are, so when you open a
recordset it can't resolve what appears to it to be a parameter. You
have to resolve any such parameters in your code before opening the
recordset. Here's one convenient way:

Dim d As DAO.Database
Dim q As DAO.QueryDef
Dim r As DAO.Recordset
Dim p As DAO.Parameter

Set d = CurrentDb
Set q = d.QueryDefs("qryLastInitialReport")

For Each p in q.Parameters
p.Value = Eval(p.Name)
Next p

Set r = q.OpenRecordset()

If r.RecordCount > 0 Then
' open the report
Else
MsgBox "No records found", vbOKOnly
End If

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Perfect! Thanks very much for the nudge up the learning curve.

Doug Munich
d o u g @ m u n i c h . c a
 
Do you want to open a form or report?

Reports has OnNoData event. Open the report in design
view, then on the Events tab, click the three builder
buttons for the On No Data section.

You want it to be:

Private Sub Report_NoData(Cancel As Integer)
Cancel=True
End Sub


If you do this, you will also get an error on the line
that tried calling the report, so you'll have to trap that.



Chris
 
Back
Top