Solved

How to suppress the #Error in Access reports when no record set return ?

Posted on 2004-09-29
12
433 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:StanleyLMW
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 12177627
Hi StanleyLMW,

I would recommend checking witha Dcount if there are Records, then Open the Report:

HowMany=Dcount("[FieldName]","TableName","[myField]=Forms![MyForm]![myField]")
if HowMany > 0 then
 Docmd.openReport ...
else
 msgbox "no records found"
endif

Hope this helps

Jaffer
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12177654
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12177731
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.CreateEventProc("NoData", "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
0
 

Author Comment

by:StanleyLMW
ID: 12177737
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12177765
Then why don't you have a Report called ErrorReport

HowMany=Dcount("[FieldName]","TableName","[myField]=Forms![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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12177780
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 250 total points
ID: 12177833
To build a slightly different approach from the others, but using the same idea...

If you want the reports to print even in the event of no data, but not show the data fields, use the No Data event as proposed by Shane & Pete, but use it to hide your detail fields and show a error field.

To explain... Create an unbound textbox on your form and set the data source to something like  [="No Data. Error in data"] without the brackets, then set it's "visible" property to False (No), then in the No Data event have something like this:

Private Sub Report_NoData(Cancel As Integer)
    Me!ControlToHide.Visible = False
    Me!NoDataErrorControl.Visible = True
End Sub

That will hide the details when there is no data and display your warning message.
0
 
LVL 3

Expert Comment

by:Moother
ID: 12177970
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
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12178057
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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12178066
It's fine Sam, not to worry. Some feedback from the OP would have been nice though.
0
 

Author Comment

by:StanleyLMW
ID: 12178098
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
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12178280
Sam
No problem here.  There'll be another one along in a minute...

Pete
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now