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
Colin Leech is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.