TempVars Value Error

You cannot Always omit “Value”

I often see people are advised they do not need to refer to a Control’s Value implicitly like this:-

Dim strValue As String
strValue = cboCurrent.Value

This advice is given because “Value” is the default, therefore it is unnecessary to append it, So you could write the above like this:-

Dim strValue As String
strValue = cboCurrent

However this is not entirely true in Every case!

I noticed this “Unexpected behaviour” a few years back, when I tried to convert one of the Northwind database’s macro’s into a VBA routine. Some of the Northwind macros save information into TempVars. The conversion routine used the combobox name only, omitting the Value property and resulted in an Error.

I would guess it’s not widely known that by omitting the “Value” property when saving to TempVars causes the TempVar to assume you are trying to save an object to it, as most VBA programmers avoid useTempVars, and would seldom experience this problem.

You can reproduce this behaviour yourself:-

Try and add the value of a combobox to a TempVar with this Code:-

TempVars.Add "CurrentUserID", cboCurrent

You will trigger the following error:-

Error 32538 —  TempVars can only store data. They cannot store Objects.

This is because the TempVar thinks you are trying to assign an object to it (the combobox) and not the value of the object (the combobox Value).

What’s interesting is, if you assign the combobox value to a string variable, (you can do this without appending the control name with the “Value” property) then you assign the string variable to the TempVar and it works fine:-

Private Sub Command0_Click()
Dim strValue As String

strValue = cboCurrent

MsgBox " >>> " & strValue

TempVars.Add "CurrentUserID", strValue
End Sub

TempVars Value Error

TempVars Value Error

Video 1 (2:31)

I discovered a minor issue when converting a Macro to VBA code. You may have to include the “Value” Property for your text box or combo box (whichever control the the macro is associated with) – this seems a bit odd to a seasoned Microsoft Access programmer because the value property is normally a default and you don’t have to add it (Append it). However it causes a problem because if the macro code assigns a variable to one of the newfangled “TempVars” then for some reason these temporary variables can interpret the assignment as being an object and not text. This confuses the the newly created code, the code created by the Microsoft utility for converting macros into VBA. Running the newly created VBA code causes the following runtime error:- Runtime error 32538 – TempVars can only store data they cannot store objects.

Video 1 (2:31)

Nifty Access YouTube Thumb Nail
Play Video

New Blog about "VALUE HERE:-

… …

Macro Conversion to VBA Causes Error

Macro Conversion to VBA Causes Error

Video 2 (5:15)

In the section of this video:- “Is this Control a Hyperlink – Nifty Access” starting at time index 2 minutes, (the video should automatically start at that time index due to the Marvellous features of Elementor!) I demonstrate the conversion of a Microsoft Access Macro into Microsoft Access VBA. The video utilises the free Northwind sample database provided by Microsoft. This is an excellent demonstration you can perform yourself once you have downloaded the Northwind sample database.

Video 2 (5:15)

Nifty Access YouTube Thumb Nail
Play Video

… …

Access 2007 Login Form

Access 2007 Login Form

Video 3 (5:53)

The Northwind sample database provided by MS Access 2007 has been upgraded to demonstrate many of the new features available in MS Access 2007. In this video we take a look at the log-in form and the new MS Access 2007 tempVar…..

Video 3 (5:53)

Nifty Access YouTube Thumb Nail
Play Video

… …

MS Access 2007 Login & TempVars 1

MS Access 2007 Login & TempVars

Video 4 (6:46)

In this video we take a look again at the log-in form and the new MS Access 2007 tempVar in more detail. The conversion from a macro into VBA wasn’t as good as I hoped, and left code with a requirement for some slight modifications which are shown in this video.

Video 4 (6:46)

Nifty Access YouTube Thumb Nail
Play Video

… …

MS Access 2007 Login & TempVars 2

MS Access 2007 Login & TempVars

Video 5 (6:53)

I cant fathom out what the code in the after update event of the combo box is for. It appears to save the name of the combo box into the temporary variable, the temporary variable which contains the users record number from the user table, and then this number is used in later forms to identify the person taking the orders and other search admin functions.

Video 5 (6:53)

Nifty Access YouTube Thumb Nail
Play Video

… …

Tidy up the Login Form

Tidy up the Login Form

Video 6 (2:22)

It’s always a good idea to tidy up your code and your forms as you go, and in this particular case, some code which is possibly redundant is commented out using a custom command bar and a buttons.

Video 6 (2:22)

Nifty Access YouTube Thumb Nail
Play Video

… …

Leave a comment

Do you need a hand in the right direction?

You are in the right place.