Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Red face 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

    End Sub

    This seems so very simple, but it is driving me insane. Any thoughts?

    - Thanks in Advance!

    -JTP

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Try putting your unbound text box in the report header. The control source should be Forms!FormName!StartDate and Forms!FormName!EndDate in the second text box.


    David

  3. #3
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Box is in header - how to use variable?

    Thank you DJN! However, the unbound box is in the report header. There is no form to refer to - the calling form gets user input from a calendar, processes and sets start and enddate variables.

    Any ideas why setting a text box value from a variable seems impossible?

    - JTP

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a search of this site shows your message is the only usage of the notation:
    vars.whatever
    ...are you sure?

    otherwise...

    is it a scope problem?

    can you return your var in a msgbox
    e.g. in report_open event?
    ...or anywhere at all?

    izy

  5. #5
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Unhappy

    thanks Izy!

    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. ;(

    -JTP

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you declared the variable as
    Global Whatever as WhateverType
    in a (old-speak) "global" module, i dont see the purpose of using moduleName.Var whatever Gates says. global is global is global!

    ...drop the vars. and try to get your variable into a msgbox... until you can get it into a msgbox it aint going to show in a report.

    izy

  7. #7
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Reply to post

    Thanks Liz!

    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:
    =([date]-(Weekday([date]))+2)
    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!)

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    good you found a fix

    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

    or

    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.

    izy

  9. #9
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Smile

    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?

    - jtp

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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.

    izy

  11. #11
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Reply re globals

    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...

    Thanks for all the great ideas!

    - JTP

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you'll find plenty of recordset stuff on this site - it comes up every day.
    try a search for openrecordset

    one example if you are using DAO:

    assumes tblCnst with fields:
    strName = "Const1", "Const2", etc
    strValue = "fred", "mercury" etc

    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
    set dabs=currentdb
    set recs=dabs.openrecordset("tblCnst")

    with recs
    .movelast
    .movefirst

    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
    case "Const1"
    Const1 = !strValue 'set the value from the current record
    case "Const2"
    Const2 = !strValue 'set the value from the current record
    'etc etc
    end select

    .movenext
    loop

    end with

    set recs=nothing
    set dabs=nothing
    end sub

    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
    etc...
    then your snazzy constant-editor form can hide the unwanted edit-boxes

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •