Print Title On Report From Form

[Lirva Smith via AccessMonster.com]

How can I print a title on a report from a command button on my form? I
have a couple command buttons on my form and depending on which one the
user click on the appropriate report title will show. For example:

Command button: cmdBlkBelts would show "Black Belts" on the report as the
title.

This is what I currently have on the command button:

Private Sub cmdBlkBelts_Click()
DoCmd.OpenReport "rptBeltLevels", acPreview, , "[BlackBelt] = 'Yes'"
End Sub

Thank You!

 

[PC Datasheet]

Add the line:
Reports!RptBeltLevels.Caption = "Black Belts"

 

[Lirva Smith via AccessMonster.com]

Sorry! But I've been trying to get this to work for some time now and I'm
lost.

I assume you mean to add that line in the command button: like this:

Private Sub cmdBlkBelts_Click()
DoCmd.OpenReport "rptBeltLevels", acPreview, , "[BlackBelt] = 'Yes'"
Reports!RptBeltLevels.Caption = "Black Belts"
End Sub

I've done that. Now, how do I get it to display on the report when it
opens, because nothing is currently showing. I have a label on the report
called "lblTitle", is there something I have to place on the report as well.

By the way I am using Access 2000 if that helps. Thanks for you assistance.

 

[Duane Hookom]

I don't recall you mentioning anything earlier about "lblTitle".

You could use code in the On Open event of the report like:

If Instr(Me.Filter,"Black") >0 Then
Me.lblTitle.Caption = "Black Belts"
End If

You can add more If's or use the filter property in other ways.

 

[PC Datasheet]

Change to this:

Reports!RptBeltLevels!LblTitle.Caption = "Black Belts"

 

[Lirva Smith via AccessMonster.com]

I'm sorry I didn't mentioned that I'm learning VBA. I'm trying to have the
title change for the next command buttons but the same title keeps
appearing.

This is what I'm working with. Also, I have changed the code on each
command button with the appropriate caption.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
If InStr(Me.Filter, "Black") > 0 Then
Me.lblTitle.Caption = "Black Belts"

ElseIf InStr(Me.Filter, "Red") > 0 Then
Me.lblTitle.Caption = "Red Belts"

Else
Me.lblTitle.Caption = "All Belts"
End If

End Sub

Thanks again!

 

[Lirva Smith via AccessMonster.com]

Oh my ...I just can't get this to work.

I assume the code you suggested should be inserted in the Form not the
Report. I placed it on the form after the report opens.

Can I kindly ask that you guys be a little more specific this is fairly
new to me and I'm trying really hard to understand.

Thanks!

 

[Lirva Smith via AccessMonster.com]

It must be something with the code on the report...I think!

Because if I change the second line to "If" instead of "ElseIf" it shows
ONLY the second caption (Red Belts) no matter which command button I click
on, but if I remove the Else it shows the first caption (Black Belts).

 

[PC Datasheet]

Since you said each button should assign a different title, the best place
for your code that assign's the report's title is the code for the buttons.
Delete any code you put in the report's Open event and use the following
code:

Private Sub cmdBlkBelts_Click()
DoCmd.OpenReport "rptBeltLevels", acPreview, , "[BlackBelt] = 'Yes'"
Reports!RptBeltLevels!LblTitle.Caption = "Black Belts"
End Sub

Note your other butons will need to assign a different title. Just put the
title where this code says "Black Belts".

 

[Lirva Smith via AccessMonster.com]

So I remove all codes from the report, and add the code as you instructed.
But the report is now not showing anything at all.

 

[Duane Hookom]

I generally don't write code in a form that modifies properties in an open
report. I don't think it is good practice.
I don't understand what you are doing "changed the code on each command
button with the appropriate caption"

You have done a nice job of specifying a "where clause" in your
DoCmd.OpenReport method. This where clause "[BlackBelt] = 'Yes'" will
automatically set the [Filter] property of your report to: [BlackBelt] =
'Yes'

The code I suggested checks the [Filter] property of your report and if it
finds the word "Black" in the property, it changes the caption of lblTitle
to "Black Belts".

You can view the Filter property by adding a text box in the report Header
Section with a control source of:
=[Filter]

You code to open the report assumes you have a text field named "BlackBelt"
that might have a value of "Yes".

This code will operate regardless of the form or code that opens the report.
It simply looks at the value you have used to Filter the report and makes a
decision based on the filter.

lblTitle is a label control and not a text box right?

 

[PC Datasheet]

Please post the code you deleted and your code in the button's click event.

 

[Lirva Smith via AccessMonster.com]

This is what I deleted form the form"

------------------- CODE ----------------------
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
If InStr(Me.Filter, "Black") > 0 Then
Me.lblTitle.Caption = "Black Belts"

ElseIf InStr(Me.Filter, "Red") > 0 Then
Me.lblTitle.Caption = "Red Belts"

Else
Me.lblTitle.Caption = "All Belts"
End If
End Sub

This is what I currently have on the command button:
------------------ Code --------------------------
Private Sub cmdBlkBelts_Click()
DoCmd.OpenReport "rptBeltLevels", acPreview, , "[BlackBelt] = 'Yes'"
Reports!RptBeltLevels!lblTitle.Caption = "Black Belts"
End Sub

Thanks!

 

[PC Datasheet]

That should do it! Do you have any code anywhere else that does something to
lblTitle?

 

[Lirva Smith via AccessMonster.com]

Nope! I don't.

I am going to take a good look again and try this whole example on another
form that I have. Will let you know how it turns out!

Thanks for all your help!