How do I change a report label control from a form code modual?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not able to find the object when referencing from a form module that
gathers information for the report form located in the reports container.

Thanks for your help,

Cj_DB
 
Here is some code you can pattern after...

'~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- change a label caption
rpt.Label_controlname.caption = "New Caption"

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
CJ_DB said:
I am not able to find the object when referencing from a form module that
gathers information for the report form located in the reports container.
Click to expand...


Opening a report is asynchronous from the VBA code that
initiates the report. The reliable way to set report
properties is for the report to set its own properties in
its Open event procedure. If you want to set the Value of
some controls on the report. then the code should be in the
Format event the section containing the control.

You next question is probably going to be how to get the
information to the report. Well, since "pushing" the info
into the report does't work, try having the report "pull"
the info from the form. To do this, the code in the report
can just refer to the form's controls by using a reference
like:
Me.RecordSource = Forms!yourform.txtSQL
Me.label.Caption = Forms!yourform.txtStartDate

If needed you can always have some invisible controls on the
form that are only used for this purpose.

The only restriction with this approach is that the from
must remain open until the report is closed. If you don't
want the form on the scrren, then it can make itself
invisible instead.
 
This was what I was looking for. It works. Thanks for the info. I was still
thinking 97.

CJ_DB
 
you're welcome ;) happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
Back
Top