Go Back   Access World Forums > Microsoft Access Discussion > Macros

Thread Tools Rate Thread Display Modes
Old 10-20-2017, 12:09 PM   #1
Uncle Gizmo
Nifty Access Guy
Uncle Gizmo's Avatar
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,785
Thanks: 427
Thanked 841 Times in 800 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
You cannot Always omit “Value”

Often 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).

There is a Video providing a bit more information on my website here:-

There is a Video explaining how to download the northwind sample database here:- TempVars Value Error - Nifty Access

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

Useful thread on TempVars and macros here :- TempVar in Form Issues... NO VBA

|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
NauticalGent (10-20-2017)
Old 10-20-2017, 07:03 PM   #2
Pristine Curmudgeon
Gold Supporter
NauticalGent's Avatar
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,678
Thanks: 362
Thanked 216 Times in 190 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: You cannot Always omit “Value”

Good info Tony. I can’t recall the details but I have experienced the same behavior from time to time myself without TempVars. Because of this I ALWAYS use .Value just to eliminate that possibility.

With regards to TempVars, I really don’t know how it got implanted in my head to avoid them and I have never taken the time to learn about them. One thing is for sure: SOMEBODY managed to convince me to avoid them!
“I had the RIGHT to remain silent...but I didn’t have the ABILITY.” - Ron White
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
Uncle Gizmo (10-21-2017)

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DTS omit duplicate rows. rsbutterfly16 SQL Server 5 12-21-2006 08:18 AM
How do I omit duplicate addresses? ismilelots Reports 0 01-30-2006 03:07 PM
omit data mike wild Queries 1 12-09-2003 02:36 PM
Omit Comma Lily Reports 4 02-05-2001 03:33 PM
Omit Comma Lily Forms 3 01-17-2001 08:19 PM

All times are GMT -8. The time now is 04:50 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World