Update a text box on a form in VBA

[WazzaRoberts]

Hi, i have a simply problem but for the life of me cannot find a solution. all i wnat to do is change the tiltle (textbox) on a report when it opens. I open the report with the following code

DoCmd.OpenReport "rpt_Total_Costs", acViewPreview, , "dateCompleted >= #" & Format(dtpStartDate, "MM/DD/YY") & "#"

This report is used several times with different where statments, but i need to change the title to reflect which report the user is looking at.

I have tried setting the title in various ways but none seem to show on the report.

A simple problem causing me a headache

[missinglinq]

You could have your users enter a title when the report runs. Take a look at Post # 2 in this thread.

http://www.thescripts.com/forum/threadedpost2833663.html#post2833663

Or, if you want the title entered depending on where the report is invoked, you could create a global variable and assign the title accordingly.

Welcome to TheScripts!

Linq ;0)>

[WazzaRoberts]

I would like to have the title set through vb when the report is opened but i cant figure out the best way to do this. if i use the immediate window and check i can get the value of the text box on the report to show as what i want but it does not show on the report

ie i do this

DoCmd.OpenReport "rpt_Total_Costs", acViewPreview, , "dateCompleted >= #" & Format(dtpStartDate, "MM/DD/YY") & "#"
                reports("rpt_total_costs").Controls("txtMainTitle").Value = "Test"
                Debug.Print reports("rpt_total_costs").Controls("txtMainTitle").Value

and the value of the title text is "Test" so is there a way i can set this before i run the openreport command or is there a way of refreashing the report in preview mode, i tired using repaintobject but it dont want to play ball.

[Jim Doherty]

I would like to have the title set through vb when the report is opened but i cant figure out the best way to do this. if i use the immediate window and check i can get the value of the text box on the report to show as what i want but it does not show on the report

ie i do this

DoCmd.OpenReport "rpt_Total_Costs", acViewPreview, , "dateCompleted >= #" & Format(dtpStartDate, "MM/DD/YY") & "#"
reports("rpt_total_costs").Controls("txtMainTitle").Value = "Test"
Debug.Print reports("rpt_total_costs").Controls("txtMainTitle").Value

and the value of the title text is "Test" so is there a way i can set this before i run the openreport command or is there a way of refreashing the report in preview mode, i tired using repaintobject but it dont want to play ball.

One of the options pointed out by missingling was global variables have you looked at that?

[WazzaRoberts]

One of the options pointed out by missingling was global variables have you looked at that?

I could have the title for the report as a global variable but the issue is how do i get that variable onto the report?

[Jim Doherty]

I could have the title for the report as a global variable but the issue is how do i get that variable onto the report?

ok well if you've looked at global variables you wil understand that a global variable can be used thoughout your database and can be changed at anytime.

So the logic is this at what point do you change it and it is this lets assume your global module in a standard module is defined like this

Global MY_REPORT_TITLE as string

Then to change that you would in your case assign a value to it at the point at which you open the report from the form that you are using. You don't have to do it that way but I'm sure you'll see its logical for the purposes of this to see it in that order

MY_REPORT_TITLE ="Test"
DoCmd.OpenRepor t "rpt_Total_Cost s", acViewPreview, , "dateComple ted >= #" & Format(dtpStart Date, "MM/DD/YY") & "#"

In the ON OPEN event of the report you would give the report this command... in effect telling it to grab the value currently assigned to the global variable where it exists and apply it to the textbox. (I personally use labels and work with the Caption property ie: Me!mylabel.capt ion="whatever" its simply a matter of personal preference

IF MY_REPORT_TITLE<>""
Me!txtReportTitle = MY_REPORT_TITLE
ELSE
Me!txtReportTitle="Greetings this report has no title but I am going to display it anyway"
END IF

The above IF logic accounts for where you open the report not necessarily by using the particular form ie directly opening the report on its own

You may have guessed then that you can do this for all sorts including displaying any critiera that applies to the
report at that particular time which serves to give an indication as to what records the users can expect as part of that report. (You could think of this in terms maybe of having a textbox in the report footer or of course the report header)

It is worthwhile remembering that a global variable 'remains' in memory until you next change it (or until you get an unconnected db error which can clear global variables down.) It is good practice therefore to clear them at the first opportunity when not needed to conserve resources ...which in this case might be when the report is closed for instance by doing inserting this command into the reports ON_CLOSE event

MY_REPORT_TITLE = ""

You might think this is somwhat longwinded for a simple task but it gives you an insight into global variable useage
There are other ways of course including grabbing the value from another form control if the value is available. Its altogether a matter of design and what suits you

I hope this helps you

Jim

[WazzaRoberts]

Jim, I can now see the logic of using a global variable and then makig it active on the on_open procedure, but, theres always a but, i am now getting the error message "You cant assign a value to this object" when running the following code

Me.txtMaintitle = reportTitle

this is in the on_open procedure of the report and when reportTitle is viewed in the imediate window show the correct assigned title.

[Jim Doherty]

Jim, I can now see the logic of using a global variable and then makig it active on the on_open procedure, but, theres always a but, i am now getting the error message "You cant assign a value to this object" when running the following code

Me.txtMaintitle = reportTitle

this is in the on_open procedure of the report and when reportTitle is viewed in the imediate window show the correct assigned title.

Wazza,

Sincere apologies my friend I was so busy typing tracking and thinking of events and code and all things digital LOL!

I gave you the wrong EVENT you need to place it into the Report Headers ON FORMAT event NOT the on open event. You will get that error message in the open event FOR TEXTBOXES it cannot do it on open event ONLY when the header gets formatted, so place the code in the ON FORMAT event and it will work for you.

Now you know why I use labels just that little bit more versatile as they will accept a caption property change in the reports on open event :) ie:

Me!Label1.Capti on = MY_REPORT_TITLE

Other than that I'm glad it gave you the idea about Global variable useage

Once again apologies

Jim

[NeoPa]

As an addendum :
In earlier versions of Access it was also possible to refer to a global variable from within the design of a query or report. I believe that is no longer possible (from Access 2003). A way around that is to define a global function which still CAN be used in this way.
This limitation shouldn't effect Jim's coded solution as that uses the code to assign what is essentially a literal value to the control.

[WazzaRoberts]

thats everyone for thier help and suggestions, i have now changed the textbox to a label so i can assign its caption, using the global variable, in the on open function of the report and everything is working fine now.

[NeoPa]

Good for you Wazza. I'm glad you managed to get it working :)