Results 1 to 8 of 8
  1. #1
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79

    VBA is reseting all my global variables...

    I have some code that creates a new form, opens it in design view, and programmatically adds controls to this new form. As these controls are added, I use the Module.CreateEventProc Method, and Module.InsertLines, to programmatically add VBA code to the controls that I just created. My code is being added to the my new form correctly, and is surprisingly functioning correctly. However, after I finish creating these forms, VBA will 'Reset' thereby erasing the values of all my variables.
    Suspected Operations causing this behavior:
    (Set frm = CreateForm) (DoCmd.OpenForm frm.Name, acDesign) (Module.CreateEventProc) (Module.InsertLines)

    Code:
    Public Function WriteStatusLabelCode(ByRef currentModule As Module, _
                                     ByRef ctrl As Control, ByVal statusIDStr As String)
        Dim lngReturn As Long
        Dim vbaStr As String
        '
        '   local declarations for the label
        '    
        '
        '   Create lbl_OnClick() Event
        '
        lngReturn = currentModule.CreateEventProc("Click", ctrl.Name)
            vbaStr = vbTab & "If passStatusIDStr <> """" Then " & _
                     vbCrLf & vbTab & vbTab & "Me.Controls(currentLabelStr).BackStyle = 0" & _
                     vbCrLf & vbTab & vbTab & "Me.Controls(currentLabelStr).BackColor = 16777215" & _
                     vbCrLf & vbTab & "End If" & _
                     vbCrLf & vbTab & "passStatusIDStr = " & statusIDStr & _
                     vbCrLf & vbTab & "currentLabelStr = """ & ctrl.Name & """ " & _
                     vbCrLf & vbTab & "Me." & ctrl.Name & ".BackStyle = 1" & _
                     vbCrLf & vbTab & "Me." & ctrl.Name & ".BackColor = 10092543"
        currentModule.InsertLines lngReturn + 1, vbaStr
        
        lngReturn = currentModule.CreateEventProc("DblClick", ctrl.Name)
            vbaStr = vbTab & "If passStatusIDStr <> """" Then " & _
                     vbCrLf & vbTab & vbTab & "Me.Controls(currentLabelStr).BackStyle = 0" & _
                     vbCrLf & vbTab & vbTab & "Me.Controls(currentLabelStr).BackColor = 16777215" & _
                     vbCrLf & vbTab & "End If" & _
                     vbCrLf & vbTab & "passStatusIDStr = " & statusIDStr & _
                     vbCrLf & vbTab & "currentLabelStr = """ & ctrl.Name & """ " & _
                     vbCrLf & vbTab & "Me." & ctrl.Name & ".BackStyle = 1" & _
                     vbCrLf & vbTab & "Me." & ctrl.Name & ".BackColor = 10092543" & _
                     vbCrLf & vbTab & "passStatusID = CInt(passStatusIDStr) " & _
                     vbCrLf & vbTab & "DoCmd.OpenForm ""frmViewStatus"""
        currentModule.InsertLines lngReturn + 1, vbaStr
        'currentModule.InsertLines lngReturn + 1, vbTab & "passStatusIDStr = " & statusIDStr     'Sets the public variable passStatusIDStr to the label's ID
        
    End Function





    So after I finish creating all my forms, I run some code to "re-learn" all my global variables. So Im reasonably happy at this point. Lastly, I run some code that deletes all the forms I just made. At this point VBA again 'Resets'.
    Suspected Operations causing this behavior:
    ( DoCmd.DeleteObject )

    Code:
        Dim obj As Object, dbs As Object
        Set dbs = Application.CurrentProject
        '   Search for open AccessObject objects in AllForms collection.
        For Each obj In dbs.AllForms
            'Debug.Print obj.Name
            If obj.Name Like "tempForm*" Then
                DoCmd.Close acForm, obj.Name
                DoCmd.DeleteObject acForm, obj.Name
            End If
        Next obj
    In this code, once the DoCmd.DeleteObject completes, VBA freaks out because it "forgets" that obj is Dim'd As Object, or it freaks out because it says the For Loop isn't initialized...


    Question:
    Is anyone familiar with these methods are typical methods or reasons that cause VBA to reset? I believe that any bit of code that makes changes to other code will run. However, upon completion the Project sense that the code has somehow changed, therefore it must either reset/recompile or something along those lines... It's also worth noting that during Sub-Routines that programmatically write code, VBA will not let me enter Break-Mode. I read somewhere that since the code is making changes to the code that VBA cannot 'hang' therefore it cannot enter break mode in situations like this.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    I have never used code that builds objects and writes code. Why do this?

    I don't see any code declaring global variables. Global variables would be declared in module header.
    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
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Quote Originally Posted by June7 View Post
    I have never used code that builds objects and writes code. Why do this?

    I don't see any code declaring global variables. Global variables would be declared in module header.
    My global variables are declared in a separate module. (Public passStatusIDStr As String)

    I chose to do this because none of the pre-built controls/forms offer the type of customization that I require. I've looked into continuous forms, but they do not offer the type of interaction that I'm ultimately after. This method is actually the closest thing I have to my desired GUI. At the end of the day, I believe that I am should be developing these forms in another platform besides Access.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    Okay, and it seems you do have a working solution.

    Or maybe TempVars would be a viable alternative.
    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
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    I'm going to try to store my variables (that keep getting deleted) in a local table. If I can isolate the actual triggers for each of the resets, I think it's okay to do a quick lookup to re-define everything. I just got done talking to some Software guys that are much better than I, and they all raise an eyebrow at my code-writing-code. They say that's a good way to get myself into trouble. They're probably right.

    As for the For-Loop issue. I think that upon encountering the first obj that meets the criteria, my code does delete obj. However in the next line (Next obj) it is trying to reference something that just got deleted. I'm going to try to find a work around this by using some other indexing rather than obj to loop through the .AllForms Collection.

    Thanks June

  6. #6
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Code:
    Public Sub DeleteTempForms()
        '
        '   Currently this only deletes one tempForm. This will need to loop through all tempForms and delete them all
        '
        On Error Resume Next
        Dim i, iMax As Integer
        Dim obj As Object, dbs As Object, tempObj As Object
        'Call ScreenUpdatingOff
        iMax = Application.CurrentProject.AllForms.Count - 1
        
        Set dbs = Application.CurrentProject
        '   Search for open AccessObject objects in AllForms collection.
        Set obj = dbs.AllForms.Item(iMax)
        
        For i = iMax To 0 Step -1
        Debug.Print obj.Name
            If obj.Name Like "tempForm*" Then
                Set tempObj = dbs.AllForms.Item(i)
                If tempObj.IsLoaded Then
                    DoCmd.Close acForm, tempObj.Name
                End If
                DoCmd.DeleteObject acForm, tempObj.Name
            End If
            If i > 0 Then
                Set obj = dbs.AllForms.Item(i - 1)
            End If
        Next
        
        'Call ScreenUpdatingOn
    End Sub
    Got it to work! I was having issues where I was giving myself a Null-Pointer. By stepping through the Collection in reverse, I was able to step through the entire list without messing up in indexing. But, I would get the nebulous error: "Microsoft Office Access cannot complete this operation. You must stop the code and try again." To ignore this I threw in a On Error GoTo Next. I'm not sure if this will come back to bite me in the @%$, but hey, it's working.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Dim i, iMax As Integer
    This declares iMax as an Integer and i as a Variant
    Should be
    Code:
    Dim i As Integer, iMax As Integer


    If you want to turn off screen repainting,
    " 'Call ScreenUpdatingOff"
    " 'Call ScreenUpdatingOn"
    should be
    Code:
    Application.Echo False
    .
    .
    Application.Echo True

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    With regards adding 'reuseable' code (implied if you are writing code to add code) to click events and the like, generally better to have your code in a module, then simply call it from your form (i.e. instead of [event procedure], you will have something like '=myglobalfunction([Form])') - no need to add vba code to the form, so it will potentially load quicker.

    Also consider creating a class module to operate on collections (or better dictionaries) of controls. I have a class module which with one simple call, enables a continuous form to take on some of the attributes of a datasheet (user can resize columns and rows, change font size, move columns around and fix left hand columns for horizontal scrolling)

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

Similar Threads

  1. Declaring Global Variables with DLookup?
    By mrmims in forum Programming
    Replies: 2
    Last Post: 09-04-2016, 07:03 AM
  2. Passing global variables question
    By newbieX in forum Programming
    Replies: 6
    Last Post: 10-03-2014, 02:09 PM
  3. Cannot create Global Variables
    By Paul H in forum Programming
    Replies: 3
    Last Post: 05-20-2014, 11:27 AM
  4. Global Variables?
    By futurezach in forum Reports
    Replies: 4
    Last Post: 06-20-2013, 03:45 PM
  5. Setting global variables
    By Remster in forum Programming
    Replies: 1
    Last Post: 08-24-2011, 08:47 AM

Tags for this Thread

Posting Permissions

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