Referencing A Form Field In A Report

[CGM3]

Okay, I've written a report which accesses the records in the table involved for a given Fiscal Year and Point-of-Contact person. The criteria are entered into combo boxes on a form, and VBA code runs it with the command line:

DoCmd.OpenReport strReportName, acViewPreview, , strSQL

where strReportName holds the name of the report, and strSQL is the 'WhereCondition' specifying the criteria, created from the combo boxes:

strSQL = "[Fiscal Year]='" & Me!cboFY.Column(0) & "' And [POC]='" & Me!cboPOC.Column(0) & "'"

The header of the report is a text box with a formula that references both [Fiscal Year] and [POC] to show the fiscal year and Point-of-Contact involved.

The thing is, if no records are found for the given criteria, the header reads #Error. I can insert an IIf(IsError([Fiscal Year]),"(no data)",[Fiscal Year]) check (and the same for [POC]), but I thought if I referenced the combo boxes on the form instead I could avoid the whole problem.

Unfortunately, when I make the header source data:

="Report for " & frmMainForm!cboPOC.Column(0) & " During FY " & frmMainForm!cboFY.Column(0)



MS Access changes it to:

="Report for " & [frmMainForm]![cboPOC].[Column](0) & " During FY " & [frmMainForm]![cboFY].[Column](0)

which, well, doesn't work. Is there a proper format to "fetch" the value of a field on a form into a report?

[RuralGuy]

Why not just use the NoData event of the report to put up a message and close the report?

[CGM3]

Because the people for whom I'm creating the report want to be able to print the hardcopy showing that "John Smith" has no records for the given Fiscal Year.

[RuralGuy]

Good point. In that case just go through the Forms collection to reference the control:
FORMS.frmMainForm!cboPOC.Column(0)

[CGM3]

That works. For that matter, I got to looking into the On No Data event and found you can reference fields on the form there as well, such as:

 
Private Sub Report_NoData(Cancel As Integer)
 
     MsgBox "There are no records for " & Form_frmMainForm!cboPOC.Column(0) & " during FY " & Form_frmMainForm!cboFY.Column(0)
, vbExclamation, "No Records Found"
     Cancel = True
 
End Sub

Both of these go into my Access Bits 'n' Stuff folder. Thanks.

[RuralGuy]

Excellent! Learning is a ton of fun and you're welcome.