Stanley Lai
asked on
How to suppress the #Error in Access reports when no record set return ?
I have built some reports in MS Access. Yet, the user find that it is not that good to display #Error in the fields when no records return for the report. They would like to show a line of like "no records found" ... etc to override the line of #Error. It is nearly impossible for me to built another report when detected no record through modules as there are over hundreds of reports showing #Error when no records found. Please help urgently. Thanks.
Just use the NoData event of the report. Put some code like this in the event:
MsgBox "No records found."
Cancel=True
This will show the message and prevent the report from opening.
MsgBox "No records found."
Cancel=True
This will show the message and prevent the report from opening.
Hello StanleyLMW,
If you add this procedure to a module in your app and run it, it will create a 'NoData' event procedure for every report in your app. The event procedure will display a msgbox as indicated in the code and then close the report.
Pete
Sub sethasnodata()
Dim x
Dim rpt As Object, mdl As Module
Dim rn As String
For Each rpt In CurrentProject.AllReports
rn = rpt.Name
'Debug.Print rn
DoCmd.openreport rn, acViewDesign, , , acHidden
Set mdl = Reports(0).Module
x = Reports(0).Module.CreateEv entProc("N oData", "Report")
mdl.InsertLines x + 1, vbTab & "Msgbox ""No data for this report - report will close"""
mdl.InsertLines x + 2, vbTab & "Cancel = True"
'Debug.Print Reports(0).OnNoData
DoCmd.Close acReport, rn, acSaveYes
Next rpt
End Sub
If you add this procedure to a module in your app and run it, it will create a 'NoData' event procedure for every report in your app. The event procedure will display a msgbox as indicated in the code and then close the report.
Pete
Sub sethasnodata()
Dim x
Dim rpt As Object, mdl As Module
Dim rn As String
For Each rpt In CurrentProject.AllReports
rn = rpt.Name
'Debug.Print rn
DoCmd.openreport rn, acViewDesign, , , acHidden
Set mdl = Reports(0).Module
x = Reports(0).Module.CreateEv
mdl.InsertLines x + 1, vbTab & "Msgbox ""No data for this report - report will close"""
mdl.InsertLines x + 2, vbTab & "Cancel = True"
'Debug.Print Reports(0).OnNoData
DoCmd.Close acReport, rn, acSaveYes
Next rpt
End Sub
ASKER
Hi Jaffer,
Thanks for your advise. Yet, the intention of our user to print the report even with any data is for the audit concern. As claimed by the auditors, if we don't print the report even the report don't contain any records, then no one will know there is a system error, report lost or someone intentionally hide the facts in the report. As a result, just send a message to the user onscreen may need users to print the screen together with the blank report show behind. I afriad that user will reject my recommendation. Also, as for audit check, #Error seems tends to be report or system error rather than no data, therefore, #Error in case of no record will sure be query by the auditors.
Thanks.
Stanley
Thanks for your advise. Yet, the intention of our user to print the report even with any data is for the audit concern. As claimed by the auditors, if we don't print the report even the report don't contain any records, then no one will know there is a system error, report lost or someone intentionally hide the facts in the report. As a result, just send a message to the user onscreen may need users to print the screen together with the blank report show behind. I afriad that user will reject my recommendation. Also, as for audit check, #Error seems tends to be report or system error rather than no data, therefore, #Error in case of no record will sure be query by the auditors.
Thanks.
Stanley
Then why don't you have a Report called ErrorReport
HowMany=Dcount("[FieldName ]","TableN ame","[myF ield]=Form s![MyForm] ![myField] ")
if HowMany > 0 then
Docmd.openReport "NormalReport", ...
else
Docmd.openReport "ErrorReport", ...
endif
In the Error Report just write,
"no records found"
With the other Report identifications, like the Invoice number,
No other field that will yield #Error
jaffer
HowMany=Dcount("[FieldName
if HowMany > 0 then
Docmd.openReport "NormalReport", ...
else
Docmd.openReport "ErrorReport", ...
endif
In the Error Report just write,
"no records found"
With the other Report identifications, like the Invoice number,
No other field that will yield #Error
jaffer
In case you missed my post - try the report's NoData event. pete57r's code also uses this event and programmatically creates lines similar to the ones that I suggested in your reports for you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use the ISERR() function
Change the controlsource of your controls to look something like this:
=iif(iserr([CompanyName]), "NO DATA",[CompanyName])
That will let you change any cell that has an error into the data of your choice.
M
Change the controlsource of your controls to look something like this:
=iif(iserr([CompanyName]),
That will let you change any cell that has an error into the data of your choice.
M
Stanley, ThanX for the points! Glad I was able to help.
Shane & Pete, You OK with that? I basically just modified your ideas.
God bless!
Sam
Shane & Pete, You OK with that? I basically just modified your ideas.
God bless!
Sam
It's fine Sam, not to worry. Some feedback from the OP would have been nice though.
ASKER
Hi all,
Actually, all of your ideas are great, especially the one from Pete which I never think of. Anyway, I should thanks for all of your prompt action. Really appreciated ^_^
Cheers
Stanley
Actually, all of your ideas are great, especially the one from Pete which I never think of. Anyway, I should thanks for all of your prompt action. Really appreciated ^_^
Cheers
Stanley
Sam
No problem here. There'll be another one along in a minute...
Pete
No problem here. There'll be another one along in a minute...
Pete
I would recommend checking witha Dcount if there are Records, then Open the Report:
HowMany=Dcount("[FieldName
if HowMany > 0 then
Docmd.openReport ...
else
msgbox "no records found"
endif
Hope this helps
Jaffer