Replace parameter values in control source property

[theSizz]

I have a text box in a report, with it's control source property set to =[BegDt].
Upon loading the report a message box pops up and asks the user for the value of [begDt]. This occurs 3 more times for 3 other parameters.

Now I want to change the design of the report so I decided to put these 4 parameters on a form so the user can fill in the 4 values all at once rather than responding to 4 message boxes that pop up when you run the report.

The problem is now the control source properties in all the different text boxes need to be changed to [forms]![frmABC]![txtBegDt] etc.etc. There are over 100 text boxes in this report that make reference to the 4 different parameters.
Is there a replace command I can use to change all the occurrences in the text boxes or perhaps a simpler way would be to pass the values to the report through code.

I tried placing this in the report header's format event and it doesn't seem to have any effect.

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

begytd = [Forms]![frmABC]![txtStDt]
endytd = [Forms]![frmABC]![txtEndDt]
curmo = [Forms]![frmABC]![cboMo]
curyr = [Forms]![frmABC]![cboYr]

End Sub

I should mention that the report uses a table (tblRecap) as it's record source. I tried using a query that refers back to the form for the parameter values but that means I have to use the query as the reports record source and that creates a problem because the report needs fields from the table to perform calculations.

Can someone help me with a solution please.

Thanks for your time.

 

[dhookom]

You can use a query as your reports record source like:

SELECT tblRecap.*, [Forms]![frmABC]![txtStDt] as begytd,
[Forms]![frmABC]![txtEndDt] As endytd, [Forms]![frmABC]![cboMo] as curmo , [Forms]![frmABC]![cboYr] as curyr
FROM tblRecap;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[theSizz]

Thanks Duane, that was my first course of action but I received an error when trying to run the query.

Upon checking the sql statement closer I discovered I wrote an expression referring to a different calculated field in the query improperly. Fixing that expression solved the problem.

That's for your help and pointing me in the right direction.
I appreciate your time.

theSizz