Add ONE variable word to report header

[Ann Scharpf via AccessMonster.com]

I am trying to find the most straightforward way to add a single word to a
report header based on user input. I've been looking through the posts and
not seeing quite what I'm talking about.

I have a switchboard where the user clicks one of four buttons to generate a
report. I would like to prompt the user "Is this a revision?." If the user
clicks YES, I would like to add the word "Revision" to the report header.

I thought that maybe the easiest way to do this would be to have two versions
of the report - one with "revision" and the other without. But then I have 8
reports to maintain instead of four. So that doesn't seem like a good idea.
Even if this is the best way, I'm not quite sure how to execute it.

I'm still fairly new at Access and need basic instruction if I have to do a
module kind of thing to make this work.

Thanks very much for any input you can give me.

Ann

 

[Marshall Barton]

I am trying to find the most straightforward way to add a single word to a
report header based on user input. I've been looking through the posts and
not seeing quite what I'm talking about.

I have a switchboard where the user clicks one of four buttons to generate a
report. I would like to prompt the user "Is this a revision?." If the user
clicks YES, I would like to add the word "Revision" to the report header.

I thought that maybe the easiest way to do this would be to have two versions
of the report - one with "revision" and the other without. But then I have 8
reports to maintain instead of four. So that doesn't seem like a good idea.
Even if this is the best way, I'm not quite sure how to execute it.

I'm still fairly new at Access and need basic instruction if I have to do a
module kind of thing to make this work.

What is the report header? Is it a label control in the
report's Report Header section or is it a text box?

In the former case, then change the label to a text box.

At this point, a quick and dirty way to do that is to set
the text box's control source expression to something like:

="Report of whatever this is " & IIf(MsgBox("Is Revision, 4)
= 6, "<Revision>", "")

 

[Ann Scharpf via AccessMonster.com]

I continued to just read posts to find interesting new stuff. I found this
post, which seems a viable solution to my problem:

http://www.accessmonster.com/Uwe/Forum.aspx/access-reports/23972/Adding-a-note-to-a-report


_________________________

 

[Klatuu]

There are a whole lot of ways to do this. Some of them depend on what
version of Access you are using. If you are on 2003, then you can use the
OpenArgs argument of the OpenReport method and put the response in the
OpenArgs. Then in the Open event of the report:

If Me.OpenArgs = "Revision" Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

If you have a prior version, the OpenArgs is not available. So, you could
get the response to your question in your form and store it in an invisible
text box. Then in the Open event of the report:

If Forms!MyFormName!MyHiddenControl = "Revision" Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

Or, rather than doing it in the form, you could do it in the Open event of
the Report with a MessageBox:

If MgbBox("Is This A Revision", vbQuestion+vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

 

[Ann Scharpf via AccessMonster.com]

Hi, Klatuu:

I thought your second suggestion looked intriguing and wanted to try it. I
went into my report properties and into the open event. I selected the "code
builder" option and pasted your text. This is what I ended up with after the
paste:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If
End Sub

Now the report will not open at all when I double-click on it. I can only go
into the design view. What did I do wrong? Thanks.

Ann

________________________

There are a whole lot of ways to do this. Some of them depend on what
version of Access you are using. If you are on 2003, then you can use the
OpenArgs argument of the OpenReport method and put the response in the
OpenArgs. Then in the Open event of the report:

If Me.OpenArgs = "Revision" Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

If you have a prior version, the OpenArgs is not available. So, you could
get the response to your question in your form and store it in an invisible
text box. Then in the Open event of the report:

If Forms!MyFormName!MyHiddenControl = "Revision" Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

Or, rather than doing it in the form, you could do it in the Open event of
the Report with a MessageBox:

If MgbBox("Is This A Revision", vbQuestion+vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

I am trying to find the most straightforward way to add a single word to a
report header based on user input. I've been looking through the posts and

[quoted text clipped - 15 lines]

Ann

 

[Klatuu]

A couple of things. First, you Report Title should be a Lable control. In my
haste, I named it as if it were a text box, sorry. Anyway, I just did a
quick test and it does work. What you need to do is use the name of your
label control in the code instead of mine. It was only an example.
So change txtReportTitle to whatever the name of your control is. I think
that should work, let me know.

Hi, Klatuu:

I thought your second suggestion looked intriguing and wanted to try it. I
went into my report properties and into the open event. I selected the "code
builder" option and pasted your text. This is what I ended up with after the
paste:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If
End Sub

Now the report will not open at all when I double-click on it. I can only go
into the design view. What did I do wrong? Thanks.

Ann

________________________

There are a whole lot of ways to do this. Some of them depend on what
version of Access you are using. If you are on 2003, then you can use the
OpenArgs argument of the OpenReport method and put the response in the
OpenArgs. Then in the Open event of the report:

If Me.OpenArgs = "Revision" Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

If you have a prior version, the OpenArgs is not available. So, you could
get the response to your question in your form and store it in an invisible
text box. Then in the Open event of the report:

If Forms!MyFormName!MyHiddenControl = "Revision" Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

Or, rather than doing it in the form, you could do it in the Open event of
the Report with a MessageBox:

If MgbBox("Is This A Revision", vbQuestion+vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If

I am trying to find the most straightforward way to add a single word to a
report header based on user input. I've been looking through the posts and

[quoted text clipped - 15 lines]

Ann

 

[Ann Scharpf via AccessMonster.com]

Now is around the time when I start to fear I'm trying my advisor's patience.
I tried to do what you said and the report still won't open, except in design
mode. This is what I now have in my Open Event:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

 

[Klatuu]

Not at all, Ann. I am puzzled, because it appears you have done it
correctly. It worked just fine for me. So lets work through it. First, I
don't know that it makes a difference, but what version of Access are you on?

Now, are you opening the report from a form and it wont open or are you
opening it on its own? I did not tie it to a form, just clicked on preview
after I finished the design. See if that makes a difference.

I am wondering if, in fact, the message box is coming up, but is hidden
behind something else. Shouldn't be, but sometimes we have to contimplate
the absurd.

If you still can't see it be opening it directly, put a breakpoint in your
code to be sure it is getting to that point:

Private Sub Report_Open(Cancel As Integer)
'Put breakpont on the line below
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

While you are doing that, I am going to try opening the report from a form
to be sure I am not passing out bad advice.

BTW, I am using 2003 in 2000 format.

 

[Klatuu]

I just tried it from the form, and it worked. Let me know how your testing
goes.

 

[Ann Scharpf via AccessMonster.com]

Let's see if I can answer your questions in sequence.

I am also running 2003 with database in 2000 format.

I am opening the report by double-clicking on the report in the reports list.
When I click on the preview button from the design view the screen flashes,
but Access still does not display the prompt or generate the report.

I have tried to minimize all the windows after I try either report generation
method. There is no hidden dialog box anywhere.

I have just been tasked with doing a quickie database, so will have to wait
until I finish that little project to try your breakpoint suggestion. Will
get to that in a few hours.

Thanks a lot, Klatuu!

Ann

Not at all, Ann. I am puzzled, because it appears you have done it
correctly. It worked just fine for me. So lets work through it. First, I
don't know that it makes a difference, but what version of Access are you on?

Now, are you opening the report from a form and it wont open or are you
opening it on its own? I did not tie it to a form, just clicked on preview
after I finished the design. See if that makes a difference.

I am wondering if, in fact, the message box is coming up, but is hidden
behind something else. Shouldn't be, but sometimes we have to contimplate
the absurd.

If you still can't see it be opening it directly, put a breakpoint in your
code to be sure it is getting to that point:

Private Sub Report_Open(Cancel As Integer)
'Put breakpont on the line below
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

While you are doing that, I am going to try opening the report from a form
to be sure I am not passing out bad advice.

BTW, I am using 2003 in 2000 format.

Now is around the time when I start to fear I'm trying my advisor's patience.
I tried to do what you said and the report still won't open, except in design

[quoted text clipped - 12 lines]

 

[Marshall Barton]

Now is around the time when I start to fear I'm trying my advisor's patience.
I tried to do what you said and the report still won't open, except in design
mode. This is what I now have in my Open Event:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

Does the report open correctly if you remove those three
lines?

 

[Ann Scharpf via AccessMonster.com]

If I understand correctly, you are asking whether the report opened
successfully before I added these lines to the open event:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If
End Sub

And the answer is, yes, it did.

Now is around the time when I start to fear I'm trying my advisor's patience.
I tried to do what you said and the report still won't open, except in design

[quoted text clipped - 5 lines]

End If
End Sub

Does the report open correctly if you remove those three
lines?

 

[Marshall Barton]

That's not quite what I am asking. Take those lines out of
the report as it is now and see what happens.

Note that you are using a name, txtReportTitle, that implies
you are using a text box. If that's the case, this will not
work because a text box does not have a Caption property and
you can not change a textbox's Value in the Open event.
Double check that the control really is a label.
--
Marsh
MVP [MS Access]

If I understand correctly, you are asking whether the report opened
successfully before I added these lines to the open event:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.txtReportTitle.Caption = Me.txtReportTitle.Caption & " Revision"
End If
End Sub

And the answer is, yes, it did.

Now is around the time when I start to fear I'm trying my advisor's patience.
I tried to do what you said and the report still won't open, except in design

[quoted text clipped - 5 lines]

End If
End Sub

Does the report open correctly if you remove those three
lines?

 

[Ann Scharpf via AccessMonster.com]

Actually, I didn't go back into my database but cut & pasted those lines out
of Klatuu's earlier post. The lines that ARE in my open event are:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

This is the ONLY change that I have made to my report, so if I remove them, I
am just back to the original version which, as I said, did open just fine.

I did use your "quick and dirty" method of adding a text box with a control
source to prompt the user to add the word REVISION. This worked on three of
my four reports. The last report is significantly more complicated than the
others. (It's a composite of four unbound reports.) When I try to add the
field with control source to this report, Access doesn't print the word
REVISION in the header but the word ?NAME.

So, I am trying to find a way to make this last report print the word
REVISION at the top of the first page.

Another difference between the reports is that the three that work have a
report header and the one that doesn't is lacking the report header. So I
made a copy of the report and switched the page header info to the report
header. Didn't have any effect.

I really would like to thank both of you for trying to help me.

Ann

That's not quite what I am asking. Take those lines out of
the report as it is now and see what happens.

Note that you are using a name, txtReportTitle, that implies
you are using a text box. If that's the case, this will not
work because a text box does not have a Caption property and
you can not change a textbox's Value in the Open event.
Double check that the control really is a label.

If I understand correctly, you are asking whether the report opened
successfully before I added these lines to the open event:

[quoted text clipped - 15 lines]

 

[Ann Scharpf via AccessMonster.com]

Klatuu:

I really am just being a chimp here and copying and pasting your text into my
open event. I am not sure whether that is what you intended me to do but I
don't understand the code so I'm just plopping away.

In any case, when I added the line about the breakpoint, there was no effect.
The report would not open from the report list. Nor did the preview button
work in the design mode.

I am leaving for the weekend now. If you happen to post any response, I will
try it on Monday morning. As I said to Marshall, thanks so much for trying
to help me.

Ann

Not at all, Ann. I am puzzled, because it appears you have done it
correctly. It worked just fine for me. So lets work through it. First, I
don't know that it makes a difference, but what version of Access are you on?

Now, are you opening the report from a form and it wont open or are you
opening it on its own? I did not tie it to a form, just clicked on preview
after I finished the design. See if that makes a difference.

I am wondering if, in fact, the message box is coming up, but is hidden
behind something else. Shouldn't be, but sometimes we have to contimplate
the absurd.

If you still can't see it be opening it directly, put a breakpoint in your
code to be sure it is getting to that point:

Private Sub Report_Open(Cancel As Integer)
'Put breakpont on the line below
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

While you are doing that, I am going to try opening the report from a form
to be sure I am not passing out bad advice.

BTW, I am using 2003 in 2000 format.

Now is around the time when I start to fear I'm trying my advisor's patience.
I tried to do what you said and the report still won't open, except in design

[quoted text clipped - 12 lines]

 

[Marshall Barton]

Comments inline below.
--
Marsh
MVP [MS Access]

Actually, I didn't go back into my database but cut & pasted those lines out
of Klatuu's earlier post. The lines that ARE in my open event are:

Private Sub Report_Open(Cancel As Integer)
If MgbBox("Is This A Revision", vbQuestion + vbYesNo) = vbYes Then
Me.Label4.Caption = Me.Label4.Caption & " Revision"
End If
End Sub

This is the ONLY change that I have made to my report, so if I remove them, I
am just back to the original version which, as I said, did open just fine.

The reason I want you to actually remove that code is to
double check that something else isn't getting in the way of
chasing down this problem. Logic may say it can't happen,
but I really want to make sure that the above perfectly
sensible code is somehow blowing up the whole report.

I did use your "quick and dirty" method of adding a text box with a control
source to prompt the user to add the word REVISION. This worked on three of
my four reports. The last report is significantly more complicated than the
others. (It's a composite of four unbound reports.) When I try to add the
field with control source to this report, Access doesn't print the word
REVISION in the header but the word ?NAME.

If you got that to work anywhere, you must have fixed my
missing quote mistake. I don't see any reason for it to
work one place and not work in another. The only way I can
think of getting ?Name is if the quotes were wrong, so maybe
you didn't fix my mistake in the one that doesn't work.

So, I am trying to find a way to make this last report print the word
REVISION at the top of the first page.

Another difference between the reports is that the three that work have a
report header and the one that doesn't is lacking the report header. So I
made a copy of the report and switched the page header info to the report
header. Didn't have any effect.

I don't think that makes a difference, but if it did, you
would need to use Klatuu's idea, which, except for your lack
of VBA skills, is better all the way around.

 

[Ann Scharpf via AccessMonster.com]

Ah, now that I look at things again (I was pretty tired on Friday afternoon)
I see that I DIDN'T use your solution. I used the one I linked to in my 2nd
or 3rd post. It was from jahoobob:

Place a Text Box in the report where you want the note. Set the Control
Source to [Enter note] (or whatever message you want.) When the report is
opened the user will be prompted "Enter note"

I had tried yours and it didn't work for me ... I assume because of the
quotes problem you refer to here. Can you tell me how to fix the quotes and
I'll try your method again?

Thanks a lot.

Ann

Comments inline below.

Actually, I didn't go back into my database but cut & pasted those lines out
of Klatuu's earlier post. The lines that ARE in my open event are:

[quoted text clipped - 7 lines]

This is the ONLY change that I have made to my report, so if I remove them, I
am just back to the original version which, as I said, did open just fine.

The reason I want you to actually remove that code is to
double check that something else isn't getting in the way of
chasing down this problem. Logic may say it can't happen,
but I really want to make sure that the above perfectly
sensible code is somehow blowing up the whole report.

I did use your "quick and dirty" method of adding a text box with a control
source to prompt the user to add the word REVISION. This worked on three of
my four reports. The last report is significantly more complicated than the
others. (It's a composite of four unbound reports.) When I try to add the
field with control source to this report, Access doesn't print the word
REVISION in the header but the word ?NAME.

If you got that to work anywhere, you must have fixed my
missing quote mistake. I don't see any reason for it to
work one place and not work in another. The only way I can
think of getting ?Name is if the quotes were wrong, so maybe
you didn't fix my mistake in the one that doesn't work.

So, I am trying to find a way to make this last report print the word
REVISION at the top of the first page.

[quoted text clipped - 3 lines]

made a copy of the report and switched the page header info to the report
header. Didn't have any effect.

I don't think that makes a difference, but if it did, you
would need to use Klatuu's idea, which, except for your lack
of VBA skills, is better all the way around.

 

[Marshall Barton]

="Report of whatever this is " & IIf(MsgBox("Is Revision",
4) = 6, "<Revision>", "")

The missing quote was between "Is Revision and the comma.

The reasons I suggested using the MsgBox instead of a prompt
string is because the Message box can be made to use Yes and
No buttons and because the user does not have to type
anything.
--
Marsh
MVP [MS Access]

Ah, now that I look at things again (I was pretty tired on Friday afternoon)
I see that I DIDN'T use your solution. I used the one I linked to in my 2nd
or 3rd post. It was from jahoobob:

Place a Text Box in the report where you want the note. Set the Control
Source to [Enter note] (or whatever message you want.) When the report is
opened the user will be prompted "Enter note"

I had tried yours and it didn't work for me ... I assume because of the
quotes problem you refer to here. Can you tell me how to fix the quotes and
I'll try your method again?

Comments inline below.

Actually, I didn't go back into my database but cut & pasted those lines out
of Klatuu's earlier post. The lines that ARE in my open event are:

[quoted text clipped - 7 lines]

This is the ONLY change that I have made to my report, so if I remove them, I
am just back to the original version which, as I said, did open just fine.

The reason I want you to actually remove that code is to
double check that something else isn't getting in the way of
chasing down this problem. Logic may say it can't happen,
but I really want to make sure that the above perfectly
sensible code is somehow blowing up the whole report.

I did use your "quick and dirty" method of adding a text box with a control
source to prompt the user to add the word REVISION. This worked on three of
my four reports. The last report is significantly more complicated than the
others. (It's a composite of four unbound reports.) When I try to add the
field with control source to this report, Access doesn't print the word
REVISION in the header but the word ?NAME.

If you got that to work anywhere, you must have fixed my
missing quote mistake. I don't see any reason for it to
work one place and not work in another. The only way I can
think of getting ?Name is if the quotes were wrong, so maybe
you didn't fix my mistake in the one that doesn't work.

So, I am trying to find a way to make this last report print the word
REVISION at the top of the first page.

[quoted text clipped - 3 lines]

made a copy of the report and switched the page header info to the report
header. Didn't have any effect.

I don't think that makes a difference, but if it did, you
would need to use Klatuu's idea, which, except for your lack
of VBA skills, is better all the way around.

 

[Ann Scharpf via AccessMonster.com]

Thanks SO much, Marshall! This works like a gem, even on my "problem child"
report. This is EXACTLY what I was hoping to do, because it is easier than
requiring the user to type or paste the word "revision" four separate times
for the four reports.

Thanks for sticking with me till I got it.

Ann

="Report of whatever this is " & IIf(MsgBox("Is Revision",
4) = 6, "<Revision>", "")

The missing quote was between "Is Revision and the comma.

The reasons I suggested using the MsgBox instead of a prompt
string is because the Message box can be made to use Yes and
No buttons and because the user does not have to type
anything.

Ah, now that I look at things again (I was pretty tired on Friday afternoon)
I see that I DIDN'T use your solution. I used the one I linked to in my 2nd

[quoted text clipped - 43 lines]

 

[Ann Scharpf via AccessMonster.com]

Marshall:

This works fine but when you PRINT the report, the message box appears again.
Is it possible to get the message box to appear only when you click the
button to create the report but not when you print it?

Ann

="Report of whatever this is " & IIf(MsgBox("Is Revision",
4) = 6, "<Revision>", "")

The missing quote was between "Is Revision and the comma.

The reasons I suggested using the MsgBox instead of a prompt
string is because the Message box can be made to use Yes and
No buttons and because the user does not have to type
anything.

Ah, now that I look at things again (I was pretty tired on Friday afternoon)
I see that I DIDN'T use your solution. I used the one I linked to in my 2nd

[quoted text clipped - 43 lines]