Unanswered: Help Using variable to update report text box
I've got a report that is fed by a form that selects the beginning date and time period (in weeks) for the report. Thnakfully, it works fine.
However, I need to label the report with the period covered. The period is defined by the public variables Vars.PeriodStart and Vars.PeriodEnd, which are both defined as dates.
When I try to set an unbound text box in the report with one of these variables, I always get an run-time error 424: Object required. I notice that the text box object does not even seem to have a value property to set, whereas all my form text boxes do have it.
I set up a blank form to try it, and now I get the same error whnever i try to set a form field. Yuck - especially because I've set lots of these in the past with no issues.
Heres the code behind the report:
Private Sub Form_Load()
Text0.Value = Vars.PeriodEnd
This seems so very simple, but it is driving me insane. Any thoughts?
It probably looks strange, but vars is the name of a module, wherein are the public defs for these varriables. I can see them in vba code everywhere, but it seems impossible to use any public (global) variable in an expression or report. According to MAAcess help, you must use modulename.variablename when addressing public variables from other modules - thence the vars.variable notation.
BTW - noticed many posts referring to globals. Could find zip mention of any kind of global variable in the Access XP help or in the access 97 bible- they both say that public "variablename as variabletype" makes a variable available to all modules. This has been fun - just want to title a report with the Date period covered, but it may not be possible...
Perhaps it is a type issue. However, the text blocks in reports do not seem to have a value property, so setting text in them is a bear. And without being able to use variables in expressions, makes it impossible to use something like = "Report Covers dates from " & vars.periodstart
Wonder how in heck you set variable text in a report. ;(
Unfortunately, if I drop the vars (module name) part of vars.variablename, then I always get a parameter prompt. It seems that a report or form always wants an expression or reference made to controls and fields in that form or report, or in other open objects only. I can always see the variables in VBA code areas, and they operate fine there. But once inside a form or report control or property, variables are "persona non grata." Why these global aren't, I do not know. Sure makes for challenges when simple would be better.
PS - finally solved by dilema by using a textbox with the data source set to:
Where data was one of the underlying fields in the detail section, and the report was grouped by [date]. Putting that in the report header (or grouper header) gives me the date for the first day of the period.
Whew! - and thanks for the advice on this one
- jtp (AKA globals aren't always global in Access!)
but your mention of "open objects" probably gives the answer: if you've declared public in a form-module you are out of scope when the form is closed.
you could open your form & hide it
declare global in a module-module (in old-speak: a "global" module - the last thing in the list of tables, queries, forms...). downside is that your "global" module loads on first call and stays loaded ...so keep it slim unless you can afford the memory hit.
Thanks Izy - as usual right on the mark! Fortunately, the vars mod is not a form, so scope is global. Hey, i'm a newbie but i got one thing right.
Hey, any ideas on how to carry and set some permanent constants (tax rate, program version, etc inside the program, easily updatable via form?
ok... declaring at the head of a "global" module using: global myGlobal as string
makes it available to my Access globally... code, queries, forms, reports, msgbox.
i just did it a minute ago as a reality check.
either way - you've got something that works, so it's not so important. it's still interesting tho. if you want to post the code from wherever you declare your not-so-global vars..........
meanwhile, update-able constants..... or is it fixed variables? hmmm
i guess you need a table:
myConstName as string
myConstString as string
myConstDouble as double
...etc for each of the variable-constant (constant-variable) types you want to play with.
stuff your name/value pairs in the table
use a form to edit the table values
open a recordset on startup to read the values into...
...yes, you guessed it: a bunch of variables declared in the head of a "global" module using: global myGlobal as string etc
there's no getting away from it - you've got to get your globals working as advertised.
Thanks Izy! The table is in place, as is the snazzy form to edit it. Now I just have to figure out a great way to read 'em in. Haven't messed with recordsets yet, but obviously my time is at hand.
1) All my docs talk about "Public variablename as variabletype", whereas all you guys who actually know something always talk about "Global...". Try as I may, I can't find any mention. However, If I define a global or public, seems to work fine. Wonder if there's a difference?
2) Also wonder why I can't use my variables in properties, expressions, datasources, etc. Only seem to be able to use them in VBA code. Whenever I try, I alway end up answering a paramer messagebox. A real pain and inconvenience...
private sub form_load() 'on your start-up form
'all "." and "!" chars in this sub are needed
dim dabs as dao.database
dim recs as dao.recordset
do while not .eof()
'now set your variable-constants
'for just a few values, select case will do the job
select case !strName 'get the name from the current record
Const1 = !strValue 'set the value from the current record
Const2 = !strValue 'set the value from the current record
BTW you can improve your variable-constants table with another field which specifies which of the data types is appropriate for each variable-constant using (e.g.) a manually entered lookup list:
1 - bool
2 - string
3 - double
then your snazzy constant-editor form can hide the unwanted edit-boxes