Programmatically set text in textbox on Access Report

I have a report which is loaded by clicking a button on a form. The subtitle field should contain the same text as a label on the form. Currently I'm linking this textbox directly to the contents of that label, but then it generates an error if the form is closed (or not open in the first place, that is if the report is opened directly). I would like to avoid this by setting the textbox to have a constant string value, taken from the aforementioned form label. However, to set the text in the textbox, the textbox must have focus. It can't have focus in design mode or print preview mode, and these are the only modes available for reports. Does anyone have a workaround in which I could set the contents of that textbox programmatically, so that users can save the report they have generated without having it display errors or incorrect text?
LVL 1
876Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
In a standard module, under module tab paste:

Public TileStr As String
---------
In OnClick event of preview button, include:

TileStr =Me!txtTitle

And, in report OnOpen event include:

Me!txtReportTitle=TileStr

Mike
876Author Commented:
Hi, I gave that a try. Worked fine up until setting the contents of the textbox on the report (I tried the line of code above, tried substituting 'TileStr' with a constant string, and tried changing the '!' to a '.', none of which changed the outcome), where I got the following error:
Run-time error '2448':
You can't assign a value to this object.
Mike EghtebasDatabase and Application DeveloperCommented:
Corrections...  and some questions for you:

Public TileStr As String                             'Q1:  Do you have in right place?
---------
In OnClick event of preview button, include:

TileStr =Nz(Me!txtTitle,"")                        'Q2: is txtTitle on your form and it is unbound?
MsgBox TileStr & "   <-- string read from the form; remove after a test"
---------
And, in report OnOpen event include:

Me!txtReportTitle=TileStr                     'Q3: is txtReportTitle on your report and it is unbound?
MsgBox TileStr & "   <-- string in report; remove after a test"

Mike
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

876Author Commented:
Re: Q1: It's sitting by itself in a module. I got no runtime errors upon assigning its value from the form.
Re: Q2: txtTitle is actually a label named lblDisplay. It is unbound. I set the contents programmatically based on selections made in other fields.
Re: Q3: txtReportTitle is called txtSubHead. Its ControlSource is as follows:
=Forms!frmProcessRelationships!lblDisplay.Caption
..as it is linked to the value in the label on the form.

I have changed my code as you suggested. I now get the following error:
438: Object doesn't support this property or method
on this line:
TileStr = Nz(Me!lblDisplay, "").

I get the same error on the next line (printing TileStr in a message box), so there must be a problem with accessing that member of that module for some reason...
Mike EghtebasDatabase and Application DeveloperCommented:
corrections...

In OnClick event of preview button, include:

TileStr =Me!lblDisplay.caption & ""                    
MsgBox TileStr & "   <-- string read from the form; remove after a test"
---------
And, in report OnOpen event include:

Me!txtReportTitle =TileStr    
' remove =Forms!frmProcessRelationships!lblDisplay.Caption  from its control source

Mike
876Author Commented:
I made the changes you recommended, including unbinding the textbox. When running the following line from the report:
Me!txtSubHead = TileStr
I get the following error:
Run-time error '2448':
You can't assign a value to this object.
I'm not sure if this is to do with it not having focus, or some other problem...
Mike EghtebasDatabase and Application DeveloperCommented:
in

Me!txtSubHead = TileStr

I take that txtSubHead is a text box.  If it is a lable (we need to call it lblSubHead on the report and in the code) like

Me!lblSubHead.caption = TileStr
876Author Commented:
txtSubHead is indeed a textbox. I don't think I'd be able to bind it to my form if it was a label (but I'm not 100% sure about that).
Mike EghtebasDatabase and Application DeveloperCommented:
The control source of txtSubHead needs to be empty, is it?
876Author Commented:
It is empty.
Mike EghtebasDatabase and Application DeveloperCommented:
Try this

In Report OnOpen Event: (also try this code in OnFormat of report header)

Dim temp
temp=Nz(TileStr,"is null")
temp=iif(temp="","is empty",temp)
Msgbox temp                        '<-- to check to see if the variable is recognized
Me!txtSubHead ="ABCD"        '<-- to check to see if ABCD appears in txtSubHead


---------
In OnClick event of preview button, include:

TileStr =Me!lblDisplay.caption & ""                    
MsgBox TileStr & "   <-- string read from the form; remove after a test"

Let me know about two MsgBoxes and ABCD on the report.

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
876Author Commented:
Tried this in the Open event:
temp contained "is empty" when it came up in the msgbox
Me!txtSubHead ="ABCD" threw exception 2448 as documented earlier.

Tried it in the OnFormat event:
temp contained "is empty" when it came up in the msgbox
No unhandled exceptions; "ABCD" appears as the subheading!!

In preview button's Click event:
TileStr was successfully set to the value of the label.

I don't want to change the subheading if the report has been loaded without the use of the form. I'll put a flag in that global module to state whether the form loaded the report...

Thanks very much for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.