Null recordset problem

[George Papadopoulos]

Hello, Access Community

I have this problem, with several reports I`ve built. Normally the reports
work fine, but sometimes I happen to open a report bounded to a query, which
returns a null recordset. Since I`m using a SUM total aggregate, I get an
error message in that case. How can I get around this?

thx, in advance

George Papadopoulos

 

[Nikos Yannacopoulos]

George,

If you want to not run the report at all in the case of a null recordset,
then you can use some code to check the number of records in the recordset
and run the report only if there are 1 or more records, rather than running
the report directy. Assuming your report is called "Report1" and its
recordsource is "Query1", the (DAO) code would look something lke:

Sub Open_Report()
Dim db as DAO.Database
Dim rst as DAO.Recordset
Set db = CurrentDB()
Set rst = db.OpenRecordset("Query1")
On Error GoTo no_record
rst.MoveLast
On Error GoTo 0
rst.Close
Set rst = Nothing
Set db = Nothing
DoCmd.OpenReport "Report1", acViewPreview
Exit Sub
no_record:
MsgBox "No Record found"
End Sub

HTH,
Nikos

 

[Marshall Barton]

I have this problem, with several reports I`ve built. Normally the reports
work fine, but sometimes I happen to open a report bounded to a query, which
returns a null recordset. Since I`m using a SUM total aggregate, I get an
error message in that case. How can I get around this?

You can use the report's NoData event to cancel the report
when there is no data to display.

if you want to print something(?) when there is no data,
then you can use the report's HasData property to check for
the situation.

=IIf(Report.HasData, Sum(field), 0)

 

[George Papadopoulos]

I wrote the code below

Private Sub btnReport_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("query_Anafora_bash_aitias_blabhs")
On Error GoTo no_record

rst.MoveLast
On Error GoTo 0
rst.Close
Set rst = Nothing
Set db = Nothing

If IsDate(Me.From_Date) Then dtFrom_Date = Me.From_Date
If IsDate(Me.To_Date) Then dtTo_Date = Me.To_Date

If (Me.btnKakh_xrhsh) Then
DoCmd.OpenReport "Anafora_bash_aitias_blabhs_kakh_xrhsh", acViewPreview
Else
DoCmd.OpenReport "Anafora_bash_aitias_blabhs_fysiologikh_f8ora",
acViewPreview
End If

Exit Sub

no_record:
MsgBox ("Aai anYeceai aaanaoYo")

End Sub

Private Sub btnUpdate_Click()
DoCmd.Close acDefault
End Sub

Private Sub Form_Load()
Flag_From_Date = False
Flag_To_Date = False
End Sub

The code now fails with a syntax error at the statement

Set rst = db.OpenRecordset("query_Anafora_bash_aitias_blabhs"). The error
states that more arguments are need. I had the idea that all other arguments
to the OpenRecordset method were optional. is this not the case?

 

[Nikos Yannacopoulos]

George,

To be honest, so did I! I work with DAO recordsets a lot, and I've never
seen this. Is this a plain select query? Any chance it's looking for a
parameter it can't find (for instance, from a form which is not open at the
time)?

Nikos

 

[George Papadopoulos]

Correct! The query is actually looking for a parameter!

It is searching for a parameter which is supposed to be read directly from
a text box on a form. The form is open at the time, due to the fact that the
DAO recordset code is directly called from a button on the form.
Thus, I had the impression that the query could directly access the text
box data (it`s parameter) exactly just as when the report, which the query
is bounded to, is opened directly.
Do I need to pass the parameter as a further argument to the OpenRecordset
function?

George Papadopoulos

 

[Nikos Yannacopoulos]

Don't think so, but I ain't sure either.

Suggestion: Open the query in SQL view and copy the SQL statement. Use that
in your code as the recordset definition instead of the saved query name,
chances are it'll solve the problem, or at least help you put your finger on
it.

strSQL = "SELECT .... FROM ... WHERE FieldName =' & Me.ControlName
(add quotes if a text field)
Set rst = db.OpenRecordset(strSQL)

HTH,
Nikos