Placing variable number of variables into fields in table

Question

I am trying to make a generic notInList function that is called when the user types a value in a combobox that is not part of the list. In middle of that function, I want to insert the new value(s) into a table.

For some of the combo-boxes, more than one field has to be filled out in the table. (The user is asked a follow-up question about the not-in-list value, and the answer to the follow-up also has to be put into the table). The values that have to be inserted in the table are stored in variables in the code.

The way I have been dealing with this so far is through a table that has one record for each combo-box id, field, and variable name (the name of the variable that contains the value to be inserted into the field) combination. The code loops over all the records that relate to this combo-box and builds one list of field names and one list of variable names to be used in a SQL statement (Insert...values...).

However, I can't figure out how to use the name of the variable (retrieved from the table) to get the value stored in the variable. AddVar is the column in the recordset that contains the name of the variable I am trying to get the value of. I tried eval(rs!AddVar) but that doesn't work.

I am able to get the name of the variable from the table, but then I am stuck. How can I get the value (a string) stored in that variable?

Answer 1

"AddVar is the column in the recordset that contains the name of the variable I am trying to get the value of. I tried eval(rs!AddVar) but that doesn't work."

Try:

rs.Fields(AddVar).Value

Edit: Sorry, I didn't read closely enough --- I thought your problem was limited to retrieving the name of the variable. But you want to retrieve the value of a variable whose name is supplied at runtime. I don't know how that is possible with VBA. See Access World Forums --- "VBA doesn't support runtime evaluation of variables by name".

Consider a Dictionary data structure to store key/value pairs.

Public Sub MyDict()
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    d.Add "var1", 1
    d.Add "var2", 2
    Debug.Print "Value of 'var1' is " & d("var1")
    Set d = Nothing
End Sub

Answer 2

You might be interested in the Dlookup() function. Basically, you just pass it a field name, a table name, and some optional criteria, and it will give you the first result it finds.

Consider the example:

=DLookUp("[LastName]", "Employees", "[EmployeeID] = 7")

This will go into the "Employees" table, find the first record where "[EmployeeID] = 7", and return to you the [LastName]. Hope this helps.

http://support.microsoft.com/kb/208786