Changing labels on the fly

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

Guest

I have two buttons on a switchboard: (1) Accounts for all banks and (2)
Accounts for selected bank. If you click on the second button, a form pops up
with a combo box to select the bank. In both cases, you are taken to the
exact same report, AccountReport, except in the second case there is a filter
to limit the report to the selected back.
In the Report Header of the AccountReport there is a label,
AccountReportLabel. I want the caption to change depending on the number of
banks in the report, something like "Accounts for all banks" and "Accounts
for selected bank" but I can't get it to work.
What I've tried is putting a new textbox, BankCount, in the Bank Header of
the report and use the Running Sum property of that box to count the number
of banks. I also put a textbox, LastBankCount, in the Report Footer. Both of
these boxes show the correct number of banks.
However if I put an If statement in the On Activate property of the report
like:
If Me.LastBankCount = 1 Then
Me.AccountReportLabelTitle.Caption = "Aoounts for selected bank"
End If
No matter how many banks I have showing and counted on the report, this If
statement always thinks LastBankCount is 1 eventhough the visible number on
the report is greater than 1.
Any suggestions would be appreciated.
 
Glen said:
I have two buttons on a switchboard: (1) Accounts for all banks and (2)
Accounts for selected bank. If you click on the second button, a form pops up
with a combo box to select the bank. In both cases, you are taken to the
exact same report, AccountReport, except in the second case there is a filter
to limit the report to the selected back.
In the Report Header of the AccountReport there is a label,
AccountReportLabel. I want the caption to change depending on the number of
banks in the report, something like "Accounts for all banks" and "Accounts
for selected bank" but I can't get it to work.
What I've tried is putting a new textbox, BankCount, in the Bank Header of
the report and use the Running Sum property of that box to count the number
of banks. I also put a textbox, LastBankCount, in the Report Footer. Both of
these boxes show the correct number of banks.
However if I put an If statement in the On Activate property of the report
like:
If Me.LastBankCount = 1 Then
Me.AccountReportLabelTitle.Caption = "Aoounts for selected bank"
End If
No matter how many banks I have showing and counted on the report, this If
statement always thinks LastBankCount is 1 eventhough the visible number on
the report is greater than 1.
Click to expand...


The Activate event fires before the report starts to process
ots data and way before a runing sum can be calculated
(which requires the entire report to be formatted). Another
point to keep in mind is that the Activate event only fires
when the report window becomes active. This means that if
the report is printed without being previewed, the report
will not have a window to activate and the event will not
fire at all. Bottom line, the Activate event is totally
inappropriate for this kind of thing.

Instead of using a RunningSum text box to figure out how how
many banks there are, get the filter information from the
form (or maybe the filter?).

If the report's record source query uses a parameter to
filter the data, you can refer to the parameter in a text
box expression.

If you are using any other method to filter the report's
data, please explain how/where the filtering is applied and
we'll try to figure a way to display it in the report.
 
Thanks.

I'll go with that approach, but I was hoping to keep all the "intelligence"
in report code in case I had some other form or switchboard also call the
report.

BTW I may have used the term "filter" incorrectly. I'm actually using the
"where" criteria, as follows:

stDocName = "AccountReport"
stCriteria = "[Bank_Abrv] =
[forms]![account4BankQueryForm]![BankQueryCombo]"
DoCmd.OpenReport stDocName, acPreview, , stCriteria

Without the "where" I get all banks.
 
You can still achieve the goal of making the report
independent of the form that opened it by passing the
information to the report using the OpenArgs argument:

stDocName = "AccountReport"
stCriteria = "Bank_Abrv=""" & Me!BankQueryCombo & """""
DoCmd.OpenReport stDocName, acPreview, _
WhereCondition:= stCriteria, _
OpenArgs:= Me!BankQueryCombo

Note the different way of constructing the Criteria.

The Report can then check the criteria value with something
like:

Me.AccountReportLabelTitle.Caption = _
"Accounts for " & Me.OpenArgs
--
Marsh
MVP [MS Access]

I'll go with that approach, but I was hoping to keep all the "intelligence"
in report code in case I had some other form or switchboard also call the
report.

BTW I may have used the term "filter" incorrectly. I'm actually using the
"where" criteria, as follows:

stDocName = "AccountReport"
stCriteria = "[Bank_Abrv] =
[forms]![account4BankQueryForm]![BankQueryCombo]"
DoCmd.OpenReport stDocName, acPreview, , stCriteria

Without the "where" I get all banks.


Marshall Barton said:
The Activate event fires before the report starts to process
ots data and way before a runing sum can be calculated
(which requires the entire report to be formatted). Another
point to keep in mind is that the Activate event only fires
when the report window becomes active. This means that if
the report is printed without being previewed, the report
will not have a window to activate and the event will not
fire at all. Bottom line, the Activate event is totally
inappropriate for this kind of thing.

Instead of using a RunningSum text box to figure out how how
many banks there are, get the filter information from the
form (or maybe the filter?).

If the report's record source query uses a parameter to
filter the data, you can refer to the parameter in a text
box expression.

If you are using any other method to filter the report's
data, please explain how/where the filtering is applied and
we'll try to figure a way to display it in the report.
Click to expand...
Click to expand...
 
Neat! Thanks. I learned something new.

Marshall Barton said:
You can still achieve the goal of making the report
independent of the form that opened it by passing the
information to the report using the OpenArgs argument:

stDocName = "AccountReport"
stCriteria = "Bank_Abrv=""" & Me!BankQueryCombo & """""
DoCmd.OpenReport stDocName, acPreview, _
WhereCondition:= stCriteria, _
OpenArgs:= Me!BankQueryCombo

Note the different way of constructing the Criteria.

The Report can then check the criteria value with something
like:

Me.AccountReportLabelTitle.Caption = _
"Accounts for " & Me.OpenArgs
--
Marsh
MVP [MS Access]

I'll go with that approach, but I was hoping to keep all the "intelligence"
in report code in case I had some other form or switchboard also call the
report.

BTW I may have used the term "filter" incorrectly. I'm actually using the
"where" criteria, as follows:

stDocName = "AccountReport"
stCriteria = "[Bank_Abrv] =
[forms]![account4BankQueryForm]![BankQueryCombo]"
DoCmd.OpenReport stDocName, acPreview, , stCriteria

Without the "where" I get all banks.


Marshall Barton said:
Glen wrote:

I have two buttons on a switchboard: (1) Accounts for all banks and (2)
Accounts for selected bank. If you click on the second button, a form pops up
with a combo box to select the bank. In both cases, you are taken to the
exact same report, AccountReport, except in the second case there is a filter
to limit the report to the selected back.
In the Report Header of the AccountReport there is a label,
AccountReportLabel. I want the caption to change depending on the number of
banks in the report, something like "Accounts for all banks" and "Accounts
for selected bank" but I can't get it to work.
What I've tried is putting a new textbox, BankCount, in the Bank Header of
the report and use the Running Sum property of that box to count the number
of banks. I also put a textbox, LastBankCount, in the Report Footer. Both of
these boxes show the correct number of banks.
However if I put an If statement in the On Activate property of the report
like:
If Me.LastBankCount = 1 Then
Me.AccountReportLabelTitle.Caption = "Aoounts for selected bank"
End If
No matter how many banks I have showing and counted on the report, this If
statement always thinks LastBankCount is 1 eventhough the visible number on
the report is greater than 1.


The Activate event fires before the report starts to process
ots data and way before a runing sum can be calculated
(which requires the entire report to be formatted). Another
point to keep in mind is that the Activate event only fires
when the report window becomes active. This means that if
the report is printed without being previewed, the report
will not have a window to activate and the event will not
fire at all. Bottom line, the Activate event is totally
inappropriate for this kind of thing.

Instead of using a RunningSum text box to figure out how how
many banks there are, get the filter information from the
form (or maybe the filter?).

If the report's record source query uses a parameter to
filter the data, you can refer to the parameter in a text
box expression.

If you are using any other method to filter the report's
data, please explain how/where the filtering is applied and
we'll try to figure a way to display it in the report.
Click to expand...
Click to expand...
Click to expand...
 
For those looking for something similar with Access 2007 here is what I did.
Also, it will output to a PDF if you have the addins installed. The
acviewhidden keeps the screen from flickering and the PDF will open for the
"preview"

Private Sub AllComputersBTN_Click()

'Open report for items with In Use, Under Repair, or Storage in their Status

DoCmd.OpenReport "AllComputersRPT", acViewReport, , "[Status]= 'In Use' OR _
[Status]= 'Under Repair' OR [Status]= 'Storage'", acHidden, _
"Computers In Use, Under Repair, or In Storage"

'Spits out the report to PDF format to D:\LAN Admin Reports\ADPE Reports\
'but doesn't open it yet. Also closes the Access version of the report

DoCmd.OutputTo acOutputReport, "AllComputersRPT", acFormatPDF, _
"D:\LAN Admin Reports\ADPE Reports\All Computers.pdf", False
DoCmd.Close acReport, "AllComputersRPT", acSaveNo

'This is where the report opens
Application.FollowHyperlink "D:\LAN Admin Reports\ADPE Reports\All
Computers.pdf"

End Sub


When the access report opens, I have the following in the On Activate area:

Private Sub Report_Activate()

Me.AllComputersTXT.Value = Me.OpenArgs

End Sub


The OpenArgs information I got from this post thanks to Marshall Barton as
well as the On Activate info. Thanks Glen for asking this question so long
ago.

--
"My hair is NOT turning grey, it''s getting singed because my personal CPU is
over-heating."


Marshall Barton said:
You can still achieve the goal of making the report
independent of the form that opened it by passing the
information to the report using the OpenArgs argument:

stDocName = "AccountReport"
stCriteria = "Bank_Abrv=""" & Me!BankQueryCombo & """""
DoCmd.OpenReport stDocName, acPreview, _
WhereCondition:= stCriteria, _
OpenArgs:= Me!BankQueryCombo

Note the different way of constructing the Criteria.

The Report can then check the criteria value with something
like:

Me.AccountReportLabelTitle.Caption = _
"Accounts for " & Me.OpenArgs
--
Marsh
MVP [MS Access]

I'll go with that approach, but I was hoping to keep all the "intelligence"
in report code in case I had some other form or switchboard also call the
report.

BTW I may have used the term "filter" incorrectly. I'm actually using the
"where" criteria, as follows:

stDocName = "AccountReport"
stCriteria = "[Bank_Abrv] =
[forms]![account4BankQueryForm]![BankQueryCombo]"
DoCmd.OpenReport stDocName, acPreview, , stCriteria

Without the "where" I get all banks.


Marshall Barton said:
Glen wrote:

I have two buttons on a switchboard: (1) Accounts for all banks and (2)
Accounts for selected bank. If you click on the second button, a form pops up
with a combo box to select the bank. In both cases, you are taken to the
exact same report, AccountReport, except in the second case there is a filter
to limit the report to the selected back.
In the Report Header of the AccountReport there is a label,
AccountReportLabel. I want the caption to change depending on the number of
banks in the report, something like "Accounts for all banks" and "Accounts
for selected bank" but I can't get it to work.
What I've tried is putting a new textbox, BankCount, in the Bank Header of
the report and use the Running Sum property of that box to count the number
of banks. I also put a textbox, LastBankCount, in the Report Footer. Both of
these boxes show the correct number of banks.
However if I put an If statement in the On Activate property of the report
like:
If Me.LastBankCount = 1 Then
Me.AccountReportLabelTitle.Caption = "Aoounts for selected bank"
End If
No matter how many banks I have showing and counted on the report, this If
statement always thinks LastBankCount is 1 eventhough the visible number on
the report is greater than 1.


The Activate event fires before the report starts to process
ots data and way before a runing sum can be calculated
(which requires the entire report to be formatted). Another
point to keep in mind is that the Activate event only fires
when the report window becomes active. This means that if
the report is printed without being previewed, the report
will not have a window to activate and the event will not
fire at all. Bottom line, the Activate event is totally
inappropriate for this kind of thing.

Instead of using a RunningSum text box to figure out how how
many banks there are, get the filter information from the
form (or maybe the filter?).

If the report's record source query uses a parameter to
filter the data, you can refer to the parameter in a text
box expression.

If you are using any other method to filter the report's
data, please explain how/where the filtering is applied and
we'll try to figure a way to display it in the report.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top