Results 1 to 9 of 9

Converted Form's Macro into VBA... now experiencing error in VBA

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    317

    Converted Form's Macro into VBA... now experiencing error in VBA

    Experts:

    I need some assistance with fixing a VBA error after I converted a form's macro (embedded in a form) into VBA.

    Attached are two (2) databases "Version 1" and "Version 2".

    Information about "Version 1":
    - It includes form "F10_StaffMemberDetails" as well as a "Search" macro.
    - Upon opening the form, please click on the "Go to" drop-down in the top left of the form.
    - The sample data (in table "T01_StaffMembers" contains 3 records); when selecting the combo, the user can select toggle from the current record to either one of the other records.
    - This works very nicely!

    As I'm making a few changes to this sample form (downloaded from MS-Office), I decided to **convert the form's built-in macro** into VBA (I used the button "Convert Form..." available in the design ribbon).

    Now, next is some information about "Version 2":
    - Essentially, it is a copy of Version 1 minus the "Search" macro.
    - The macro to VBA conversion resulted in plenty of VBA code... at some point, I will have to see what I may no longer need.

    Here's what I need some assistance with today. Upon opening the form in Version 2, the "Go to" combo box now results in a **run-time error '35538' (TempVars can only store data. They cannot store objects)**.



    How can this VBA (in Version 2) be fixed so that the "Go to" combo works w/o throwing any issues?

    Thank you in advance!
    EEH
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,401
    I have never used TempVars and not seeing need for any in this procedure. Try:
    Code:
    Private Sub cboGoToContact_AfterUpdate()
    On Error GoTo cboGoToContact_AfterUpdate_Err
        If Not IsNull(Me.cboGoToContact) Then
            If (Form.Dirty) Then DoCmd.RunCommand acCmdSaveRecord
            If (Form.FilterOn) Then DoCmd.RunCommand acCmdRemoveFilterSort
            DoCmd.SearchForRecord , "", acFirst, "[StaffMemberIDpk]=" & Me.cboGoToContact
        End If
        
    cboGoToContact_AfterUpdate_Exit:
        Exit Sub
    cboGoToContact_AfterUpdate_Err:
        MsgBox Error$
        Resume cboGoToContact_AfterUpdate_Exit
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    The top two lines of EVERY module should be
    Code:
    Option Compare Database
    Option Explicit
    The above two lines sill also help find errors.
    When I select a person from the "Go To" combo box (cboGoToContact), the error I get is "Run-time error 2474 TempVars can only store data. They cannot store objects" ( Because of the "Option Explicit" line)


    In the Sub "cboGoToContact_AfterUpdate()", Find the line (line 165 in the IDE)
    Code:
     TempVars.Add "ActiveControlValue", Screen.ActiveControl
    Change it to
    Code:
     TempVars.Add "ActiveControlValue", Screen.ActiveControl.Value






    A lot of the converted macro code really should be re-written. For instance
    Code:
        'If (Form.Dirty) Then
        If Me.Dirty Then
            Me.Dirty = False    '<<--Better
    '        DoCmd.RunCommand acCmdSaveRecord
        End If

    Code:
        If (MacroError.Number <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
            Exit Sub
        End If
    Since you are not executing a macro, I doubt very much that you will get a MacroError Number (so I would delete this code - or at least comment it out).



    EDIT: I just saw June7's post..... I also have never used TempVars
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,401
    Line Option Compare Database should always establish automatically. I am surprised it was not there. Perhaps that is result of converting macro.

    Forcing Option Explicit in new modules can be done in the VBE > Tools > Options > check Require variable declaration.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    317
    All -- thank you for helping me to figure this one out. It works great now.
    Although beyond this question, should all of the VBA be "cleanup up" given that the macro added stuff that either may not be needed or, based on your feedback, "I have never used TempVars and not seeing need for any in this procedure"?
    Basically, are there other lines of code that could be removed from this form in order to keep the VBA nice and clean?
    Thanks,
    EEH

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,401
    Adding the two lines already described should be part of 'clean up'.

    Then use Debug > Compile from the VBE and see if that exposes some issues.

    I have never used Screen.ActiveForm nor Screen.ActiveControl - I would explicitly reference objects by name but that is my preference.

    Not against TempVars, I've just never used. They may have been needed by the macro but not in VBA generated by the conversion.

    Code in form Load event makes me think there is more to this db than you posted, or at least more to the original you are modeling from. If there isn't more then that event code is not needed but leaving it doesn't hurt.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    317
    June7... thank you, very helpful. I ran the compile from VBE and the first function that threw an error is the "cmdEmail".

    Code:
    Private Sub cmdEmail_Click()
    On Error GoTo cmdEmail_Click_Err
        'Converted Office form's macro into VBA
        On Error Resume Next
        
        DoCmd.SendObject , "", "", [Contact Name] & IIf(Nz([E-mail Address]) <> "", " [" & [E-mail Address] & "]"), "", "", "", "", True, ""
        
        If (MacroError.Number <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
    cmdEmail_Click_Exit:
        Exit Sub
    cmdEmail_Click_Err:
        MsgBox Error$
        Resume cmdEmail_Click_Exit
    End Sub
    How should I modify VBA to no longer get error (line highlighted is "DoCmd.SendObject")?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,401
    Missing value to return when IIf() is false. The "" (empty string) for the unused arguments are not necessary but don't hurt.

    DoCmd.SendObject , , , [Contact Name] & IIf(Nz([E-mail Address]) <> "", " [" & [E-mail Address] & "]", ""), , , , , True

    or

    DoCmd.SendObject , , , [Contact Name] & " [" + [E-mail Address] + "]", , , , , True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    317
    Works perfectly now... you rock!!!

    Thank you, June7.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. experiencing 3024 error in the past week?
    By yeah in forum Programming
    Replies: 4
    Last Post: 05-17-2018, 07:57 AM
  2. Cannot find Converted Macro VBA code
    By Lou_Reed in forum Access
    Replies: 10
    Last Post: 08-14-2017, 06:53 AM
  3. Auto run converted Macro
    By steven7 in forum Macros
    Replies: 3
    Last Post: 06-09-2016, 11:26 AM
  4. Using macro converted to VBA in other functions
    By gaker10 in forum Programming
    Replies: 4
    Last Post: 08-19-2014, 07:44 AM
  5. Converted macro to VBA not working
    By gemadan96 in forum Forms
    Replies: 5
    Last Post: 06-04-2014, 10:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums