Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Cycle through Access public variables with common names

I have a form with a series of common named controls that I want to update to the storerd values in similarly named public globals.

I want to set this up as a "for" loop  as I have a lot of field variables and have the syntax for the form fields as:
For i = 1 To 10
        Me("txtTextBox" & i).Value
    Next i

How do I structure global variable?
  The below is kind of what I want but obviously is incorrect syntax
Me("txtTextBox" & i).Value = pubText & i
0
ynot8669
Asked:
ynot8669
  • 2
1 Solution
 
Jacques Bourgeois (James Burger)PresidentCommented:
Declare your public variables as an array:

Public pubText(1 to 10) As String

Open in new window

This gives you 10 variables called pubText(1)...pubText(10)

Use them the following way:

Me("txtTextBox" & i).Value = pubText(i)

Open in new window

0
 
ynot8669OwnerAuthor Commented:
Great, you have confirmed where my thinking was going.

I couldn't make any other syntax work ........and tried quite few.

Thank You :)
0
 
Dale FyeCommented:
The other option, depending on the version of Access you are using, is to use TempVars.

TempVars is a collection of variables that you do not need to declare (dimension) and which can contain virtually anything.  To create a tempvar, simply use the syntax:

Tempvar.Add  "variableName", value

Note: I've also found that you can simply do:Tempvar!variableName = x

Then, you could use either of the following to refer to that variable.

TempVars!strAppName
TempVars.Item("strAppName")
TempVars.Item(0)

Advantages of tempvars are:
a.  the collection retains its values, even outside the scope of your code, even after encountering an unhandled error.
b.  You can actually refer to the variable by name
c.  you can use them in queries, which you cannot do directly with public variables.  I find this valuable in that I don't have to refer to form controls in my queries.  In the after update event of a control, say txtStartDate, I'll simply set the value of the tempvar:

Private sub txtStartDate_AfterUpdate

    if me.txtStartDate & "" = "" then
        'do nothing
   else
        Tempvars!StartDate = cdate(me.txtStartDate.Value)
   end if

End Sub

Then, in my queries, I can refer to tempvar!StartDate rather than referring to a form field.  This gives me the ability to set tempvars in the immediate window and test queries that would otherwise return nothing or use a popup for data entry.

Hope this helps.
0
 
ynot8669OwnerAuthor Commented:
Thanks Dale, I have used TempVars for most global variables but wasn't aware of the .item(x) access method.

I'm assuming you can vary (x) within a loop, if so it may well work out to be the way forward.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now