Define tempVars from a Table?

[Alastair]

I have a table which is formatted as shown:

ID, My_Var, My_Value, Notes

This table holds variables that I want to declare to use throughout my application. I have been told in another thread that tempVars are the best way to do this.

I have written the following code, which works on a limited basis:

Private Sub btnSetVAr_click()

TempVars.Add "udvVar", Me!My_value.Value

End Sub

This defines a single variable on each button press, fine to work out how the code works, but not much use. What I really need to do is when the initial menu screen loads to call a routine to assign all the variables stored in the table using a loop to do this. The idea is to make all variables values easy to edit or add to, rather than have to edit code each time we need to change them.

The variables table holds 14 records so far, such as:

My_Var, My_Value

EuroRate, 0.885
ConDisc, 0.9
MollDisc, 0.8

As well as holding numbers, they hold strings and date values.

So, on loading the initial form, use an event to assign the variables from the table using the tempVar name as the value held in "My_Var" and it's value as held in "My_Value"

Has anyone done something similar, or is there a better way?

 

[DavidAtWork]

can't you just open a recordset based on your table of variables and assign the names and values from the recordset collection

Do While Not(rs.EOF)
    TempVars.Add rs.Fields("My_Var") , rs.Fields("My_Value")
    rs.Movenext
Loop

David
PS, I've never used the TempVars function before, just assuming your method of assigning values is correct

 

[Alastair]

can't you just open a recordset based on your table of variables and assign the names and values from the recordset collection

Do While Not(rs.EOF)
    TempVars.Add rs.Fields("My_Var") , rs.Fields("My_Value")
    rs.Movenext
Loop

David
PS, I've never used the TempVars function before, just assuming your method of assigning values is correct

David,

Thanks for your post! What you suggest sounds promising. I have tried the following code, but I am getting an error I'm sure it's some syntax wrong somewhere:

Private Sub DefineVars_Click()

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset

Set dbs = CurrentDb

Set rsTable = dbs.OpenRecordset("tblDBVars", dbOpenTable)

Do While Not (rsTable.EOF)
TempVars.Add rsTable.Fields("My_Var"), rsTable.Fields("My_Value")
rsTable.MoveNext
Loop

'Cleanup
rsTable.Close

Set rsTable = Nothing

End Sub

The error is: "Runtime error 32538 - TempVars can only store data. They cannot store objects"

It looks to me like I need to alter the field name syntax somehow but I am not sure on the correct syntax for this at the moment? I have looked at the code again and notice after I receive the error the values are stored in both rs.Fields("My_Var") and rs.Fields("My_Value") (the line of code is highlighted yellow and if I hover the mouse over these definitions it shows the results) but it doesn't like something!

The field for the first value is always a string and for the second value it is either a number, string or date, but not an object? Can anyone suggest what is wrong with the code? I think it's the second value that's causing the problem as my previous code used the .value suffix.

 

[DavidAtWork]

Yes, use the method below which is a classic way of handling recordsets.

Dim db As Database
Dim Rs As Recordset
Dim sqlStr as String
 
Set db = CurrentDb()
sqlStr = "SELECT My_Var, My_Value FROM [yourTableName]"
Set Rs = db.OpenRecordset(sqlStr)
 
Rs.MoveLast
Rs.MoveFirst
If Rs.RecordCount > 0 Then 
    Do While Not Rs.EOF
        TempVars.Add Rs.Fields("My_Var") , Rs.Fields("My_Value")
        Rs.Movenext
    Loop
    Else: Msgbox "No records found", vbOKOnly + vbCritical
End If
 
Set Rs = Nothing
Set db = Nothing

David

 

[Alastair]

David,

Wow that was quick - I will give this a try later thanks very much for your help!!

 

[Cronk]

I've never had need to use a TempVar.

A quick look at the syntax seems to be that a temp var needs for each stored variable a name and a value.

Check the syntax, maybe
tmpVar.add rs("Field1").name, rs("Field1")

NB rs("Field1").value and rs("Field1") both refer to the field content.

 

[Alastair]

Hi David,

I know it's been a while but I have only just got around to trying your code. I just needed to make one slight alteration. For anyone else whose interested here it is:

TempVars.Add Rs.Fields("My_Var") , Rs.Fields("My_Value").value

The .value is needed adding after the last value otherwise I got the runtime error regarding objects referred to in one of my posts above.

Other than that it worked great thanks very much for your help!