Results 1 to 3 of 3
  1. #1
    redpetfran is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    2

    Refering to variable form names inside a variable

    I have created a variable and it is equal to below



    VAR = [Forms]![frmX]![fsubX]![VALUE]

    This is part of a larger piece of code that works fine. My problem is to expand it to the whole database, I need to somehow reference different form names depending on a variable.

    All the forms that will use this are named with the three letters frm and then a value from a table (in the above case X). All subforms on each form are named in line with this with the letters fsub and then the same table value (in the above case X). They are linked with the form in single form view and the subform in continuous form view.

    For example I have a table containing:

    ID Name
    1 X
    2 Y
    3 Z
    4 A

    And there are forms named:
    frmX (with fsubX contained on it)
    frmY (with fsubY contained on it)
    frmZ (with fsubZ contained on it)
    frmA (with fsubA contained on it)

    Can I somehow use this information to put a variable inside this variable if you will:
    VAR = [Forms]![frm&MYVAR&]![fsub&MYVAR&]![VALUE]
    This obviously doesn't work because there are no quotes - I cant use quotes because I want the variable to equal the value of the VALUE control (which is present on each of the subforms in the set)
    This problem has occurred because instead of having the code on an event procedure on every form, I have been experimenting with calling a function. Instead of having 4 instances of exacly the same code, I want to type it in a module and reference the module on click. When I try to do this with the unchanged event procedure, access does't understand the references to my variables.

    originally when the code was on the onclick as an event procedure I used
    VAR = [VALUE]
    and it worked. But now it errors if I try to use this. It also errors if I use:
    VAR= Me.[VALUE]
    The code is on the onclick of a button on the subform which also contains [VALUE]

    Please tell me if you need anything else.
    Thanks

    Peter

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    "Value" is the name of a control or field? If so, not a good idea (confusing with the Value property). This is shooting from the hip, but try:

    VAR = Forms("frm" & MYVAR).Controls("fsub" & MYVAR).Form![VALUE]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    redpetfran is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    2
    Thanks for the reply.

    This wouldn't work because I cant reference the control that needs using in this variable

    VAR = Forms("frm" & MYVAR).Controls("fsub" & MYVAR).Form![VALUE]

    This would need MYVAR variable which would be:

    MYVAR = Forms("frm" & MYVAR).Controls("fsub" & MYVAR).Form![VALUE]

    The MYVAR variable needs to be equal to a control on the form "fsub"&MYVAR on "frm"&MYVAR - ie its a circular argument. Are there any other methods? All i need to do is reference the control on the form the button is clicked on but it will not let me just type the control name. I can if the code is connected to the form module and is not an outside function.

    the actual code is below

    Code:
     
    Dim Specl As String, Tss As String
     
    Tss = "TS"
    Specl = [RPSpec] 'this needs to be the control [RPSpec] on the subform the button is clicked on 
    but it gives 'external name not defined error' when in a module as a function.  It works fine 
    as code connected to the form.
     
    DoCmd.OpenForm "frmMaterialSpec", acNormal, "", "[MaterialID]=[Forms]![frm" & Specl & "].
    [Form]![MaterialID] And [FailureShortHand]='" & Tss & "'", acFormReadOnly

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Variable Table Names in a Module
    By Jessica in forum Modules
    Replies: 1
    Last Post: 04-19-2010, 07:38 PM
  2. Replies: 1
    Last Post: 04-13-2010, 12:18 PM
  3. Form Variable in Hyperlink
    By russ0670 in forum Forms
    Replies: 5
    Last Post: 01-15-2010, 08:09 AM
  4. Passing a variable to a form
    By cjamps in forum Forms
    Replies: 0
    Last Post: 03-02-2009, 05:32 AM
  5. Variable Criteria
    By JamesLens in forum Queries
    Replies: 0
    Last Post: 01-02-2009, 04:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums