Need to display in an empty report " No Data Available" 3

[cohibaman]

A series of queries are run(under a macro) and tables are created to generate a report. The issue that I have is in handling a blank report. A blank report(titles and headers do print)is generated when the filtering/selection criteria are not met. In order to avoid confusion with the report's receiptant, I need to indicate on that report something like: "No data is available for the reporting period". Otherwise the results of the report are then questioned.
Any suggestions would be appreciated

 

[dhookom]

You can use code in the On Page event like:
Private Sub Report_Page()
If Me.HasData = False Then
Me.CurrentX = 2000
Me.CurrentY = 4000
Me.FontName = "Arial"
Me.FontSize = 24
Me.ForeColor = vbRed
Me.Print "No Records To Report"
End If
End Sub

Duane
MS Access MVP

 

[cohibaman]

It worked like a charm. Thanks for your help!!!

 

[cohibaman]

I have one additional question. The report "page header" section has a date field which would have been extracted from the empty table generating the report. Is there a way that I can grab this date from another area, say from another table, etc., and still keep the report's "No Records To Report" format? The date field I'm referring to is extracted from an initial query prompt.

Thanks

 

[dhookom]

I try to avoid all prompts in queries. There isn't enough flexibility or control. If you use a text box on a form, you should be able to set a control source of a text box in your report header to:
=Forms!frmA!txtYourCriteria

Duane
MS Access MVP

 

[cohibaman]

I'll give it a try. Thanks again for all your help!

 

[cohibaman]

My "last" question(I hope)on this issue. Could I accomplish the same result if I place a text box on the actual report itself and if so then what would syntax be?

Thanks

 

[dhookom]

This could probably be accomplished if the text box was in the Report or Page header or footer. Your control source would be something like:
=IIf([HasData]=True,"","There Ain't No Data&quot

Duane
MS Access MVP

 

[cohibaman]

Based upon the previous recommendations, I created a form that contains a value(date). In turn, I created a text box on the report and set the control source of a text box to reference the form's value as in "=IIf([HasData]=True,"",Forms![frmdate]![end])". When I run the report the text field returns "#Name?" instead of the form's value. I've tried several ways to format the reference to the form's object, however I either receive an "#Error" or "#Name?" as the value. What am I doing wrong?

Thanks!

 

[dhookom]

Is the form open? Did you spell the names of the form and text box correctly. I just tested this with the expression:
=IIf([HasData]=True,"Has Data",[Forms]![frmInspectionsSingle]![insInsID])
and it worked as hoped.

Make sure the name of the text box on the report is not also the name of any field.

Duane
MS Access MVP

 

[cohibaman]

Sorry, the form was closed when I was testing the report function. It now works. Thanks again for your help and time.

 

[janetlyn]

Duane, in trying to go with your advice about not using parameters in queries, I have a form with a label. When the label is pushed I would like a listbox to come up with a table showing AR Customers. The user selects one AR Customer. From that selection, it opens a query with just the data for that AR Customer. So behind my label I put the code:

DoCmd.OpenQuery "QRY-MoInvbyClient", , acEdit

It says that "QRY-MoInvbyClient" is not a valid name. Why not? Also, how do I tell the query to use the selected AR Customer to filter the records?

Thanks for your help, Janet Lyn

 

[dhookom]

I would place the list box of AR Customers on the form containing the "label". Set the list box up so that it is bound to the primary key of the AR Customers table. Then set the criteria of the record source of your report to include something like:
Forms!frmX!lboCustomerID

Duane
MS Access MVP

 

[janetlyn]

Not sure what you mean by "set the criteria of the record source of your report". Can I put code behind a report? Or, do I open Properties of my report and put it in there on OnOpen or something like that? Thanks for your help. Janet Lyn

 

[dhookom]

Open your report in design view and then view the properties of the report. Find the Record Source property and open it. Find the CustomerID column and enter something in its criteria like:
Forms!frmX!lboCustomer

Duane
MS Access MVP

 

[janetlyn]

Fantastic. Thanks, I will work on it this weekend. Have a great weekend yourself. JL