Sometimes you can get runtime errors when you are trying to assign the value of a control to some VBA object.
For example, consider this statement:
TempVars!NewTempVar = Form_orders.order_idWhat we are trying to do is set the NewTempVar in the TempVars system collection to the value of the order_id field in the orders form. This seems relatively straightforward, but we can get an error in this case:
And what this is saying is that we are trying to place a reference to a form control (in this case order_id) into a TempVar.
TempVars are a system object that can only contain string variable types. It’s often used to store global data. In this case, we are trying to assign a control to an object that requires a string type.
Normally, Access will IMPLICITLY convert this to a string for you. It would be similar to using the statement:
TempVars!NewTempVar = CStr(Form_orders.order_id)CStr is a function that does an explicit conversion of the value to a String type.
But in some cases, the implicit conversion doesn’t work. If there had been a variable already defined as a string, that would do the implicit converstion like this:
Dim testStr As String
testStr = Form_orders.order_idAnother way to implicitly convert a variable to a string is to append a string to it like this:
"" & Form_orders.order_idThis is the same with all variable types. Occasionally you can get into strange situations due to implicit conversions. Note the differences in the following situations in the immediate pane:
So you can see that in the first case, it is concatenating 2 strings, in the next 3 cases it is implicitly converting the values to integers.
Be aware of your variable types and what you are assigning. Often doing explicit conversions will help prevent errors and unexpected results.
Great article, Jonathan. One thing missing from the explanation is the role that default members play in all of this. Mathieu Guindon has a great article on the topic on his Rubberduck VBA blog: “VBA Trap: Default Members” (https://rubberduckvba.blog/2018/03/15/vba-trap-default-members/).
While using CStr() fixes the issue here, it’s not the only way. Alternatively, you could use Form_orders.order_id.Value.
It’s another layer of the explicit vs. implicit approach to writing VBA. As a convenience, VBA will implicitly return the default member of an object when the context of the surrounding code makes that intent clear. However, when your intent is ambiguous, VBA will simply use the object instance itself–rather than the object’s default member.
This behavior has bitten me hard in the past since Dictionary and Collection objects produce differing contexts in this regard. See my article, “The Subtle Dictionary Key Bug” (https://nolongerset.com/the-subtle-dictionary-key-bug/), for more details.
Thanks for the awesome feedback Mike. Those are great adds to my thought for the day for those that want to dig much much deeper, mwhahaha. Yeah, I’ve had issues with dictonaries and collections too in trying to store keys and objects. Kind of mind blowing when you realize the dictionary key you tried to make is actually an object! That is some WEIRD stuff! So yeah, definitely need to be careful and I guess that’s why everybody likes to add Option Explicit statements.
I almost want to write up some code not using Option Explicit and using Option Base 1 with arrays and see if I can really create some evil code.