I am trying to refer to an existing public variable and change it’s value based upon a table which stores the variable name and value. I have searched everywhere and can find nothing that can help me with any pointers. Please be gentle with me. Many thanks in advance.

I currently have a table with 3 fields, an ID, a variable name, and a variable value. In VBA I have already declared each Variable and is the same as the [variable name] in the table. The purpose of the VBA is to assign the variable the value based upon the table.

Currently I loop through the records in the table and identify the declared variable using the ID’s in the table. For example in the table:

ID VariableName VariableValue

1 strStop x

2 strPause y

3 strGo z

Then I have created a vba recordset that loops through each record in turn, and using a Select Case on the ID, populates the declared variable with the value found in the recordset. In simple form:

Dim strStop as string
Dim strPause as string
Dim strGo as string

Do until rst.EOF
   Select Case rst!ID
      Case 1
         strStop = rst!VariableValue
      Case 2
         strPause = rst!VariableValue
      Case 3
         strGo = rst!VariableValue
   End Select
   rst.MoveNext
Loop

What I would really like is to completely remove the ‘Select Case’ and populate the already declared variables with the values contained in the table. Something along the lines of:

Dim strStop as string
Dim strPause as string
Dim strGo as string

Do until rst.EOF
   Name of declared variable (rst!VariableName) = rst!VariableValue
   rst.MoveNext
Loop