Form and Report Titles

[rhernand]

I have a Form with a Combo Box. I use the entry data from the Combo Box for the criteria in a Query. I also use the Entry fron the Combo Box as a Title Text Box in the result set Form and in a Report. Example: the Year 2007 is chosen in the Combo Box and I want to include the Year 2007 in my Form and Report. However, whe the Query results in an empty result set, the Text Box in my Form and Report are empty too. How can I save it?

 

[RuralGuy]

How can you save what?

 

[rhernand]

I have to save the entries in the Combo Box, in order to use them in the reset set Form and Report. For example: I choose the year 2007 in the Combo Box, the Query will search all records for the year 2007. The Form and Report headers will read 'This are the records found for the year "2007". Since the year can change depending what year I want. I have put a Text Box to capture the Year from the Combo Box. However, when the reset set to the query is empty. the Text Box is emplt too.

 

[RuralGuy]

How are you filling the TextBox in the report? Does this reset set really mean RecordSet?

 

[rhernand]

I do not really know what you are asking. I am just geeting what was in the Combo Box in the Entry Form. =Forms!frmYTDTotalsandComparisions!CYear

 

[RuralGuy]

You answered the question. If I understand correctly, you want to print the empty report with the selection criteria in the heading, correct?

 

[rhernand]

Yes, but when I get en empty set, there is nothing in the Text Box. Although, the result set is empty, I want to still have a Report Heading containing the selection criteria, "This Report is for the year "2007", if 2007 was the selection criteria chosen. It would also be good to create another Text Box saying "No records found"

 

[RuralGuy]

I would pass the selection criteria to the report in the OpenArgs argument of the OpenReport command. You can save the OpenArgs argument to a variable in the open event of the report and move it to your header in the ReportHeader_Format() event. If the RecordSet.RecordCount is 0 then you can make a "No records found" label visible in the Detail_Format() event.

 

[rhernand]

Sorry, can you give me a play by play, I am not too familiar with ACCESS

 

[RuralGuy]

Can you post the code you are using to run the report?

 

[rhernand]

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String

stDocName = "Print YTD Totals"
DoCmd.RunMacro stDocName

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

 

[RuralGuy]

I'm afraid I do not use macros and will not be of much help here.

 

[RuralGuy]

If you want to stay with macros then you may want to start a new thread in the Macro area. If you want to venture into the dark world of code then post back here and we can give it a shot.

 

[rhernand]

The Macro just has 3 statements, SetWarnings, OpenReport, SetWarnings. I was told to just a macro with SetWarnings to suppress any warning messages. I could have just used a Print Report command button

 

[RuralGuy]

If you use an OpenReport command button then we can make some adjustments to the code to pass the date Criteria and we could modify the Report to expect the parameter in the OpenArgs and also display your label if the RecordSet is empty.

 

[rhernand]

Done. Here it is

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

Dim stDocName As String

stDocName = "YTD Totals"
DoCmd.OpenReport stDocName, acNormal

Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub

 

[RuralGuy]

Now change it to:
DoCmd.OpenReport stDocName, , , , , Me.CYear
...acViewNormal is the default view for the OpenReport command.

Now open the report in design mode and click on the Header bar on the report and open the properties sheet and select the "..." button on the OnFormat line and insert the following:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

'-- Show the Date Criteria being passed in the OpenArgs
Me.YourControlName = Me.OpenArgs

End Sub

...using YourControlName of course! Try it and see if you now get the CYear printed on the empty report.

 

[rhernand]

Yes, Thanks a lot

 

[RuralGuy]

Do you still want to display a "Sorry but no records available for this report" message if the RecordSet is empty?

 

[rhernand]

Yes, that would be good