passing variables from form to report page header

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi,

I have a form that uses a button to bring up a report. Basically on the
form there are two fields, start date and end date.

The user fills in these 2 fields and the report shows the meal allowances
for employees within the range of the 2 dates entered.

The result was successfull, but I was wondering how do I pass these 2
variables to the report's page header? I want the page header to display
"Report between startdate and enddate".

The code that I used in shown below.

Thanks.

Peter



Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim strFilter As String

DoCmd.OpenReport "rptMeal", View:=acPreview
Set rpt = Reports("rptMeal")
rpt.FilterOn = True
rpt.Filter = "[IN_WRK_DATE] BETWEEN #" & Format(txtDate1,
"mm\/dd\/yyyy") & "# AND #" & Format(txtDate2, "mm\/dd\/yyyy") & "#"

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click


End Sub
 
If the form is still open, you could set the Control Source of the text box
on your report to something like this:
=[Forms]![MyForm]![startdate]


Alternatively, you can pass a description of your filter in a global string
variable.

In the General Declarations section (top) of a standard module, declare a
public string to hold the value of the expression you wish to display:
Dim gstrReportFilter As String

In the Click event of the command button that opens the report, set the
string, e.g.:
gstrReportFilter = "Work date between " & Format(Me.[startdate], "Long
Date") ...

In the Format event of the Report Header, assign the string to an unbound
control on your report, and clear the string:
Me.txtFilterDescription = gstrReportFilter
gstrReportFilter = vbnullstring
 
And, if using ACCESS 2002, you can use the OpenArgs argument of the
DoCmd.OpenReport command to pass info to the report.

--
Ken Snell
<MS ACCESS MVP>

Allen Browne said:
If the form is still open, you could set the Control Source of the text box
on your report to something like this:
=[Forms]![MyForm]![startdate]


Alternatively, you can pass a description of your filter in a global string
variable.

In the General Declarations section (top) of a standard module, declare a
public string to hold the value of the expression you wish to display:
Dim gstrReportFilter As String

In the Click event of the command button that opens the report, set the
string, e.g.:
gstrReportFilter = "Work date between " & Format(Me.[startdate], "Long
Date") ...

In the Format event of the Report Header, assign the string to an unbound
control on your report, and clear the string:
Me.txtFilterDescription = gstrReportFilter
gstrReportFilter = vbnullstring

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Peter said:
Hi,

I have a form that uses a button to bring up a report. Basically on the
form there are two fields, start date and end date.

The user fills in these 2 fields and the report shows the meal allowances
for employees within the range of the 2 dates entered.

The result was successfull, but I was wondering how do I pass these 2
variables to the report's page header? I want the page header to display
"Report between startdate and enddate".

The code that I used in shown below.

Thanks.

Peter



Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim strFilter As String

DoCmd.OpenReport "rptMeal", View:=acPreview
Set rpt = Reports("rptMeal")
rpt.FilterOn = True
rpt.Filter = "[IN_WRK_DATE] BETWEEN #" & Format(txtDate1,
"mm\/dd\/yyyy") & "# AND #" & Format(txtDate2, "mm\/dd\/yyyy") & "#"

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click


End Sub
Click to expand...
Click to expand...
 
Back
Top