Changing a Label in Code

[Matthew DeAngelis]

I am sure there must be a way to do this, but I am not having any
success.

I am trying to use the same report to display many different selections
of records. This works great by applying filters to the OpenReport
action. However, since each report displays different information, I
need to reset the caption of the window (which I have gotten to work)
and the caption of the title label (which does not work). Here is the
code behind the command button:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptDealsTabular"
stLinkCriteria = "[Status] Like '1 - Newly Logged'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Reports![rptDealsTabular].Caption = "New Deals To Review"
Reports!rptDealsTabular![ReportTitle].Caption = "New Deals To
Review"

I don't get any error messages; the caption simply remains the default
title instead of becoming the new title.

Any help would be much appreciated.


Thanks,
Matt

 

[Duane Hookom]

I would place the value for the caption in a hidden text box on the form (or
similar). Then use code in the On Open event of the report to set its
caption.

 

[Matthew DeAngelis]

I would place the value for the caption in a hidden text box on the
form (or similar). Then use code in the On Open event of the report
to set its caption.

Thanks for helping me out.

I am not sure how to do what you describe. The command button I am
using is on my switchboard, so there is no form to reference (even if
it was on a form, I use a form with applied filters in much the same
way I am trying to work this report). Also, since I am using the same
report for many different purposes, I do not see how I could use the
OnOpen event to set a different caption depending on what filter I am
applying. I tried making my caption into an unbound text box, however,
and set its value in code instead of the label caption. The code now
looks like this:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptDealsTabular"
stLinkCriteria = "[Status] Like '1 - Newly Logged'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Reports![rptDealsTabular].Caption = "New Deals To Review"
Reports![rptDealsTabular]![ReportTitle] = "New Deals To Review"

This works partially. The text box comes out blank unless I go into
design view and back out, probably because I am setting the value after
the report is already open. However, if I set the value before the
report opens, I get the 'report not open or misspelled' error. Can I
somehow set the text box before the form opens, or perhaps I can
dynamically change the OnOpen event to the proper captions in code?


Thanks,
Matt

 

[Duane Hookom]

Create a general module that declares a public memory variable in its
declarations section.
'===============
Option Compare Database
Public strReportTitle As String

Sub SetTitle(strTitle As String)
strReportTitle = strTitle
End Sub

Function GetTitle() As String
GetTitle = strReportTitle
End Function
'=================

Then in your code to open the report:
'==something like this=====
Private Sub cmRunReport_Click()
On Error GoTo Err_cmRunReport_Click

Dim stDocName As String

stDocName = "rptDealsTabular"
SetTitle "This is the Title"
DoCmd.OpenReport stDocName, acPreview

Exit_cmRunReport_Click:
Exit Sub

Err_cmRunReport_Click:
MsgBox Err.Description
Resume Exit_cmRunReport_Click

End Sub
'===============
and in your report's On Open Event
Private Sub Report_Open(Cancel As Integer)
Me.Caption = GetTitle()
End Sub


--
Duane Hookom
MS Access MVP
--

I would place the value for the caption in a hidden text box on the
form (or similar). Then use code in the On Open event of the report
to set its caption.

Thanks for helping me out.

I am not sure how to do what you describe. The command button I am
using is on my switchboard, so there is no form to reference (even if
it was on a form, I use a form with applied filters in much the same
way I am trying to work this report). Also, since I am using the same
report for many different purposes, I do not see how I could use the
OnOpen event to set a different caption depending on what filter I am
applying. I tried making my caption into an unbound text box, however,
and set its value in code instead of the label caption. The code now
looks like this:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptDealsTabular"
stLinkCriteria = "[Status] Like '1 - Newly Logged'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Reports![rptDealsTabular].Caption = "New Deals To Review"
Reports![rptDealsTabular]![ReportTitle] = "New Deals To Review"

This works partially. The text box comes out blank unless I go into
design view and back out, probably because I am setting the value after
the report is already open. However, if I set the value before the
report opens, I get the 'report not open or misspelled' error. Can I
somehow set the text box before the form opens, or perhaps I can
dynamically change the OnOpen event to the proper captions in code?


Thanks,
Matt

 

[Matthew DeAngelis]

That's pretty neat. As usual, I come to the forums looking for a mere
solution and get a little lesson in database design instead


Thank you kindly,
Matt