#error in report field for no data.

[FrigidDigit]

Hi Guys,

I did a search of the forum but didn't find any answers.

I have a report that is based on a query using date parameters. When running a report for a period during which no data is returned, the fields on the report returns "#error".

Is there any way to avoid this? This report will be used as a subreport so I don't know if a "no data event" can be used without cancelling the whole main report?

Thanks!

FD

 

[VicRauch]

The first thing I would try would be to have code in the OnOpen event of the main report check for records in the RecordSource query for the sub-report. If no records, then set the RecordSource for the sub-report to a zero length string ("").
HTH,

 

[FrigidDigit]

Thanks Vic!

I wil try this and let you know if it works.

regards
FD

 

[FrigidDigit]

Vic,

Can you perhaps give me a hand in doing this? I new to Access and while I am familiar with Excel VBA, I am still getting to grips with the Access object model.

Thanks!
FD

 

[VicRauch]

I'll give you the steps, you look them up in Help, then I'll help you debug the code, if needed.
1. Using the query (or SQL) that is the record source for the sub-report, open a RecordSet as a SnapShot.
2. Once open, set a variable equal to rs.EOF. If there are no records in the recordset, then rs.EOF will be True. ("rs" is the variable name I used for the recordset object we opened in step 1.)
This is one way to find out if a query will have any records in it.
Vic

 

[FrigidDigit]

1st Attempt

Thanks Vic

This is what I've got (please excuse the clumsy coding I'm still learning):

I add the following to the open event of the subreport I want to run:

Private Sub Report_Open(Cancel As Integer)
Dim rstSGBReportData As Recordset
Dim eofNoData As rstSGBReportData.EOF 'eof??

Set rstSGBReportData = CurrentDb.OpenRecordset
("SELECT a_Trainee.Prov, a_Trainee.ID_Trainee, a_Trainee.TraineeSurn," & _
"a_Trainee.TraineeFirst, a_Trainee.ID13, a_Trainee.Gender, a_Trainee.SGB, " & _
"a_Trainee.SMT, a_Trainee.Rank, b_Programme.ProgName, b_Programme.Level, " & _
"b_Programme.ID_Programme, b_Programme.ProgStart, b_Programme.ProgEnd, " & _
"b_ProgrammeTraineeLink.DaysAttended, IIf([gender]=0,1,0) AS CountUnknown, " & _
"IIf([gender]=1,1,0) AS CountFemale, IIf([Gender]=2,1,0) AS CountMale " & _
"FROM b_Programme INNER JOIN (a_Trainee INNER JOIN b_ProgrammeTraineeLink " & _
"ON a_Trainee.ID_Trainee = b_ProgrammeTraineeLink.ID_Trainee) ON b_Programme.ID_Programme " & _
"= b_ProgrammeTraineeLink.ID_Programme WHERE (((a_Trainee.SGB)=2) AND " & _
"((b_Programme.Level)=9 Or (b_Programme.Level)=11) AND ((b_Programme.ProgStart) " & _
"Between [Forms]![00 QR Report Figures]![txtFromDate] And " & _
"[Forms]![00 QR Report Figures]![txtToDate]) AND ((b_Programme.ProgEnd) " & _
"Between [Forms]![00 QR Report Figures]![txtFromDate] And [Forms]![00 QR Report Figures]" & _
"![txtToDate]) AND ((b_ProgrammeTraineeLink.DaysAttended)>0)& "));")

If eofNoData = True Then
Exit Sub
End If

End Sub

I know that there is a problem with the closing parentheses in the sql statement but I am too thick too figure it out. I tried matching them but I can't figure out where I'm going wrong.

Also, the "If eofNoData = True Then" is pretty much a shot in the dark

Thanks again for helping out!
FD