A Safer Way to Use TempVars

[GPGeorge]

Here I go again, believing what Microsoft documentation says.

TempVars.Add method (Access)

Access VBA reference

learn.microsoft.com

The description of the "value" argument to the TempVars.Add method says:



IF you can use other data types, great... but that isn't what is advertised. I like to avoid stretching the boundaries on things.

Here's the chart in that reference:

Name	Required/Optional	Data type	Description
Name	Required	String	The name to use for the TempVar.
Value	Required	Variant	The value to store as a TempVar. This value must be a string expression or a numeric expression. Setting this argument to an object data type will result in a run-time error.

Note that it states, in column three, which labeled "Data type", that the data type of the value in a TempVar is a variant, whereas the data type of its name is a string.

Tempvars accept strings or numerics as input, but it is stored as a variant. This is a problem in some cases, such as where I pass dates to them or booleans. My solution is converting values retrieved from TempVars to other datatypes as appropriate before using them.

I use the functions above to convert them as needed, e.g.

SELECT DT.diabetestestid,
       DT.testdate,
       DT.testtime,
       DT.testtypeid,
       DT.testtimeofdayid,
       DT.testresult,
       DT.comments,
       tbldiabetestesttype.sortorder
FROM   tbldiabetestests AS DT
       INNER JOIN tbldiabetestesttype
               ON DT.testtypeid = tbldiabetestesttype.diabetestesttypeid
WHERE  DT.testdate >= Tempvarsdate("dtfromdate")
           AND DT.testdate <= Tempvarsdate("dttodate") )
         AND  Iif(Tempvarslong("lngtesttypeid") = 0, 0, [DT].[testtypeid])
               IN ( 0,   Tempvarslong("lngtesttypeid") )
ORDER  BY DT.testdate DESC,  tbldiabetestesttype.sortorder;

This SQL has three TempVars, two dates and a long integer. I acknowledge that in many cases, an integer would work as well as a long integer.

 

[MajP]

@KitaYama
I modified the code to do away with any tempvars and instead build class variables. I also included the ability to reference class objects. Now it can be done, but I would highly caution about setting an object. I set a form as one of my myTempVars. But a form or a control is only a pointer to the open instance. If the form or control would close then that MyTempVar is also terminated.

Here is my table.
tblMyTempVars tblMyTempVars

MyTempVarID	VarName	VarTypeName
1​	Product_Name	String
2​	Start_Date	Date
3​	Payment	Currency
4​	Default_Form	Form

When I run the code it creates my class

' ---===== DO NOT EDIT DIRECTLY =====---
' This class module is auto-generated; to recreate run:
'  Generate MyTempVars tblMyTempVars

Option Compare Database
Option Explicit


Private m_Default_Form As Form
Private m_Payment As Currency
Private m_Product_Name As String
Private m_Start_Date As Date


Public Property Set Default_Form(NewValue As Form)
   Set m_Default_Form = NewValue
End Property

Public Property Get Default_Form() As Form
   Set Default_Form = m_Default_Form
End Property

Public Property Let Payment(NewValue As Currency)
    m_Payment = NewValue
End Property

Public Property Get Payment() As Currency
   Payment = m_Payment
End Property

Public Property Let Product_Name(NewValue As String)
    m_Product_Name = NewValue
End Property

Public Property Get Product_Name() As String
   Product_Name = m_Product_Name
End Property

Public Property Let Start_Date(NewValue As Date)
    m_Start_Date = NewValue
End Property

Public Property Get Start_Date() As Date
   Start_Date = m_Start_Date
End Property

Notice the use of class variables and no tempvars.

I wrote the following function by hand, but could have simply used extensibility to build the function. This is ONLY Needed for use in a query.

Public Function GetTempVar(VarName As String) As Variant

' ONLY USED FOR QUERIES. DO NOT USE ANYWHERE ELSE IT DEFEATS THE PURPOSE

  Select Case VarName
  Case "Product_name"
    GetTempVar = MyTempVars.Product_Name
  Case "start_Date"
    GetTempVar = MyTempVars.Start_Date
  Case "Payment"
    GetTempVar = MyTempVars.Payment
  Case "Default_Form"
    'SINCE A QUERY CANNOT HAVE A FORM OBJECT POINTER, I RETURN THE NAME here.
    GetTempVar = MyTempVars.Default_Form.Name
  End Select
End Function

Try adding additional "TempVars" to the table.

 

[KitaYama]

@KitaYama
I modified the code to do away with any tempvars and instead build class variables. I also included the ability to reference class objects. Now it can be done, but I would highly caution about setting an object. I set a form as one of my myTempVars. But a form or a control is only a pointer to the open instance. If the form or control would close then that MyTempVar is also terminated.

Here is my table.
tblMyTempVars tblMyTempVars

MyTempVarID	VarName	VarTypeName
1​	Product_Name	String
2​	Start_Date	Date
3​	Payment	Currency
4​	Default_Form	Form

When I run the code it creates my class

' ---===== DO NOT EDIT DIRECTLY =====---
' This class module is auto-generated; to recreate run:
'  Generate MyTempVars tblMyTempVars

Option Compare Database
Option Explicit


Private m_Default_Form As Form
Private m_Payment As Currency
Private m_Product_Name As String
Private m_Start_Date As Date


Public Property Set Default_Form(NewValue As Form)
   Set m_Default_Form = NewValue
End Property

Public Property Get Default_Form() As Form
   Set Default_Form = m_Default_Form
End Property

Public Property Let Payment(NewValue As Currency)
    m_Payment = NewValue
End Property

Public Property Get Payment() As Currency
   Payment = m_Payment
End Property

Public Property Let Product_Name(NewValue As String)
    m_Product_Name = NewValue
End Property

Public Property Get Product_Name() As String
   Product_Name = m_Product_Name
End Property

Public Property Let Start_Date(NewValue As Date)
    m_Start_Date = NewValue
End Property

Public Property Get Start_Date() As Date
   Start_Date = m_Start_Date
End Property

Notice the use of class variables and no tempvars.

I wrote the following function by hand, but could have simply used extensibility to build the function. Now I can use my tempvars in a query.

Public Function GetTempVar(VarName As String) As Variant
  Select Case VarName
  Case "Product_name"
    GetTempVar = MyTempVars.Product_Name
  Case "start_Date"
    GetTempVar = MyTempVars.Start_Date
  Case "Payment"
    GetTempVar = MyTempVars.Payment
  Case "Default_Form"
    GetTempVar = MyTempVars.Default_Form.Name
  End Select
end function

Try adding additional "TempVars" to the table.

@MajP I sincerely appreciate your effort on this.
Your warning is noted. I'll do some tests

Million thanks.

 

[MajP]

@MajP I sincerely appreciate your effort on this.
Your warning is noted. I'll do some tests

The concepts are interesting, but unless you are doing this a lot you could probably build the class from scratch just as fast as building the table and assembling the other code into a project.

 

[arnelgp]

Try adding additional "TempVars" to the table.

on your table definition you define Default_Form as Form, yet the function is only returning a string (it's name). you could have used String as VartypeName to "conform" to the definition. what you just made is a violation of your own definition.

 

[arnelgp]

i don't see other benefit of using Tempvars in a Class.

As a programmer who created the Tempvars, you should document in paper all variables of the system
including Tempvars. That is the old skool practice. Same documents is submitted/ turnover to the client when
the programming project is completed, together with other documentations.

see this simple class that uses tempvars:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "TV"
Attribute VB_Description = "Tempvar Class"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Dim m_strName As String

Public Sub Add(ByVal strName As String, Optional ByVal varValue As Variant = Null)
m_strName = strName
TempVars.Add strName, varValue
End Sub

Public Property Get Value() As Variant
Attribute Value.VB_UserMemId = 0
Value = TempVars(m_strName).Value
End Property

Public Property Let Value(ByVal p_value As Variant)
Attribute Value.VB_UserMemId = 0
TempVars(m_strName).Value = p_value
End Property


Private Sub Class_Terminate()
TempVars.Remove m_strName
End Sub

using a test sub:

Private Sub test()

    Dim t As New TV
    
    t.Add "my_tempvar", 100
    
    Debug.Print t 'result=100
    Debug.Print TempVars("my_tempvar").Value  'result=100
    
    t = 20
    
    Debug.Print t 'result=20
    Debug.Print TempVars("my_tempvar").Value 'result=20
    
End Sub

 

[MajP]

on your table definition you define Default_Form as Form, yet the function is only returning a string (it's name). you could have used String as VartypeName to "conform" to the definition. what you just made is a violation of your own definition.. The function was only used to demo the query. No real way to use a form in a query so I put the name. Again the point is notqqe

No you missed the point! You need the read the whole thread. I clearly demonstrate that it supports objects and state that the function is only to use MyTempVars in a query.

Private Sub cmdSet_Click()
  'LOOK HERE FOR AN OBJECT being used

  Set MyTempVars.Default_Form = Forms!frmTest

  MyTempVars.Payment = 55#
  MyTempVars.Start_Date = Date
  MyTempVars.Product_Name = "Chai"
End Sub

Private Sub cmdProve_Click()

'***************************  MYTEMPVAR returning an Object
  Me.txtOut = "Start Date:" & MyTempVars.Start_Date & vbCrLf & " Default Form: " & MyTempVars.Default_Form.Name
End Sub

You have to read the whole thread, but obviously you did not so I annotated it. The only purpose of the function is for use in a query, and you could return the name of the form or just do away with it in the query because unlikely you would ever need that

Public Function GetTempVar(VarName As String) As Variant

' ONLY USED FOR QUERIES. DO NOT USE ANYWHERE ELSE IT DEFEATS THE PURPOSE

  Select Case VarName
  Case "Product_name"
    GetTempVar = MyTempVars.Product_Name
  Case "start_Date"
    GetTempVar = MyTempVars.Start_Date
  Case "Payment"
    GetTempVar = MyTempVars.Payment
  Case "Default_Form"

    'SINCE A QUERY CANNOT HAVE A FORM OBJECT POINTER, I RETURN THE NAME. DO NOT BE CONFUSED
    GetTempVar = MyTempVars.Default_Form.Name
  End Select
End Function

 

[MajP]

i don't see other benefit of using Tempvars in a Class

Well then you missed the whole point of this entire thread. It is not meant for the client it is meant for you the developer.
Again. With this method you get

• Visibility
• Type safety
• IntelliSense
• Compile-time checks for typos

So much easier to develop with these features.

 

[KitaYama]

I just wanted to add because I prefer to be explicit in data type, I added 2 more functions. For now, all my tempvars are either string, Boolean or integer

Public Function GetTempVar_bool(VarName As String) As Boolen
.....
Public Function GetTempVar_int(VarName As String) As Integer
.....
Public Function GetTempVar_str(VarName As String) As String
.....

 

[MajP]

I just wanted to add because I prefer to be explicit in data type, I added 2 more functions. For now, all my tempvars are either string, Boolean or integer

Public Function GetTempVar_bool(VarName As String) As Boolen
.....
Public Function GetTempVar_int(VarName As String) As Integer
.....
Public Function GetTempVar_str(VarName As String) As String
.....

I see no need for that, if we are talking the same approach as I demoed. Again, you are only using the function in a query and you will not get any benefit from that over a single function.
Again you should be using the class in your code and getting type safety and only the function for queries.

My single function will return the correct sub type
To demo that variants are cast automatically to correct type.

Public Sub TestSubType()
  Dim x As Variant
  x = 1
  Debug.Print VarType(x)
  x = 0.99
  Debug.Print VarType(x)
  x = Date
  Debug.Print VarType(x)
End Sub

Returns

2
5
7

 

[arnelgp]

You have to read the whole thread, but obviously you did not so I annotated it.

obviously you are using Class to save the Object(form) and not in tempvars variable.
you can just use a simple class without the use of tempvars.

 

[MajP]

obviously you are using Class to save the Object(form) and not in tempvars variable.
you can just use a simple class without the use of tempvars

You are going to have to read the whole long thread. That is entirely missing the point of the demo and the whole conversation. The point is the code generates the class and yes it does not use tempvars. We know that already.

 

[KitaYama]

My single function will return the correct sub type
To demo that variants are cast automatically to correct type.

I was distracted by the following comment in TV class added by Mike Wolf in his code. (at top of GetVar function)

' By using Variants for the default value and return type, we give up some
' type safety for the convenience of having a single function

Thanks again for your sample database.