Changing a Label in Code

  • Thread starter Thread starter Matthew DeAngelis
  • Start date Start date
M

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
 
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.
 
Duane said:
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.
Click to expand...

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
 
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
--

Matthew DeAngelis said:
Duane said:
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.
Click to expand...

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
Click to expand...
 
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
 
Back
Top