I have several MS Access applications that use Global Constants to store paths and file names for report templates, save routines, etc. I must now modify these applications so that in the event of a "disaster recovery" scenario, the application can be accessed through Citrix. All the paths to files or folders will change if this is required, so I want to have a single parameter I can toggle so that when the application loads, the globals (now variables rather than constants) can be populated depending on whether the app is running normally on the server, or on the Citrix platform.
I'd like to use a reference table to store BOTH the VARIABLE NAME and the VALUE to place in the variable. Normally in code I would assign the value to the variable explicitly in code, e.g.
modGlobalStringVariable = "Z:\Path\Subfolder\Template.xlsx".
What I would like to do is store BOTH the variable name "modGlobalStringVariable" AND the value "Z:\Path\Subfolder\Template.xlsx" in a reference table. Then I can just pull a record set from the reference table and loop through it, assigning the variable name AND value once each time through the loop, e.g.
Do Until rsVariableNames.EOF
strVarName1 = rsVariableNames!VarName
strVarName1._______ = rsVariableNames!VarNameValue
My question: What is the correct VBA Syntax to achieve this? The "________" is analogous to if I was using fields to populate a record set from a reference table, where I could go "Variable1 = rsRecordSet.Fields(strFieldName)"and each time through the loop, the strFieldName can change to correspond to the variable being populated. I need to know the syntax to make the first line of the loop plug in the Variable Name to be populated and the second line actually POPULATE that variable (rather than populating the string that HOLDs the variable name.)
Any suggestions welcomed.