TempVars can only store data. They cannot store objects even though Microsoft does this?

[JamieRhysEdwards]

Hi All,

Bit of a weird one. I'm trying to make a "Go To Record" Combo box. I'm using the Asset Database template as reference and in their Contact Details form, it has the same thing (which works if using macro's). However, when I convert it to VBA and copy the code, I get the following error:

Run-time error '32538':

TempVars can only store data. They cannot store objects.

This is the code in the template:

Private Sub cboGoToContact_AfterUpdate()
On Error GoTo cboGoToContact_AfterUpdate_Err

    If (IsNull(Screen.ActiveControl)) Then
        Exit Sub
    End If
    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    TempVars.Add "ActiveControlValue", Screen.ActiveControl ' This is the offending line!
    If (CurrentProject.IsTrusted) Then
        Screen.ActiveControl = Null
    End If
    If (Form.FilterOn) Then
        DoCmd.RunCommand acCmdRemoveFilterSort
    End If
    DoCmd.SearchForRecord , "", acFirst, "[ID]=" & TempVars!ActiveControlValue
    TempVars.Remove "ActiveControlValue"


cboGoToContact_AfterUpdate_Exit:
    Exit Sub

cboGoToContact_AfterUpdate_Err:
    MsgBox Error$
    Resume cboGoToContact_AfterUpdate_Exit

End Sub

Mine is this for now:

    If (IsNull(Screen.ActiveControl)) Then
        Exit Sub
    End If
    On Error Resume Next
    
    If (Form.Dirty) Then
        Call DoCmd.RunCommand(acCmdSaveRecord)
    End If
    
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    
    On Error GoTo 0
    
    TempVars.Add "ActiveControlValue", Screen.ActiveControl
    
    If (CurrentProject.IsTrusted) Then
        Screen.ActiveControl = Null
    End If
    
    DoCmd.SearchForRecord , "", acFirst, "[ID]=" & TempVars!ActiveControlValue

Not too dissimilar, however, I saw this error initially in my code, I then converted the macro on the template to VBA and as mentioned above, got the error. How would I fix this given that Microsoft uses it in their macro (and access seems ok with this) but when it's converted to VBA it doesn't like it?

Thanks!

 

[theDBguy]

Would this work?

TempVars.Add "ActiveControlValue", Screen.ActiveControl.Value

 

[The_Doc_Man]

One TempVars slot can store one scalar item. Objects usually are a non-homogeneous collection of scalar items. However, if you know the source collection of the object, each object in that collection will have an item number, which would allow you to refer to the object knowing its original collection and member item number. If you always work with the same collection and you don't "churn" it too much, you can at least in the short term just store the item number, and that can be done by a TempVars slot. This might not work long-term if there is any object creation and deletion going on in the collection.

 

[JamieRhysEdwards]

@theDBguy - If I change that line of code to what you've said, it then just automatically defaults to the first entry of the ComboBox list (even though it displays the name I've chosen

 

[Uncle Gizmo]

I blogged about the problem with tempvars, when converting from a macro to VBA on my website, (link below).

There's also a video showing how to fix the problem.

TempVars Value Error - Nifty Access

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

www.niftyaccess.com

 

[Uncle Gizmo]

Extract from website:-

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.

And a Video explanation:-

TempVars Value Error

 

[Galaxiom]

Would this work?

TempVars.Add "ActiveControlValue", Screen.ActiveControl.Value

This might too. I'd be curious if it does.

TempVars.Add "ActiveControlValue", (Screen.ActiveControl)

Generally the parentheses around an object cause the Value to be returned.

This behaviour confuses many novice programmers the first time they pass an object to a single argument sub. Sometimes learners will routinely put parentheses around a parameter array of a sub. Makes very little difference until there is only one argument and the parentheses retrieve the value rather than the object.

 

[Gasman]

Easy enough to test

TempVars.Add "ActiveControlValue", (Screen.ActiveControl)
? tempvars("ActiveControlValue")
1

? screen.ActiveControl.Name
Rank

 

[theDBguy]

@theDBguy - If I change that line of code to what you've said, it then just automatically defaults to the first entry of the ComboBox list (even though it displays the name I've chosen

Sounds like, at least, the error went away, right?