Results 1 to 15 of 15
  1. #1
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187

    Out of memory error

    I had this project running fine then out of no where I am getting this out of memory error when trying to access a listbox (from a vba module file) but even if I put the code in the button itself, same error.



    If (Form_Management.lstUsers.ListCount > 0) Then

    if I skip that line, I would then get it at For i = 0 To Form_Management.lstUsers.ListCount - 1

    I've searched around and saw many recommendations of using Forms("Management").lstUsers, but when I try that I get "Microsoft Access cannot find the referenced form "Management" .

    My form is saved as Management, in the list of forms in my Access project, it is listed as "Management", in VBA under Microsoft Access Class Objects it is "Form_Management"

    Also, the way I have it set up is I have a 'navigation' form which I have my other forms linked to (using a tab bar on the top of the nav form to get to each form).

    When I do this, it only msgbox's me the Navigation form, not either of my 3 other forms. I don't get why it worked fine one minute then the next minute VBA is scratching its head.
    Code:
        Dim obj As AccessObject, dbs As Object
        Set dbs = Application.CurrentProject
        ' Search for open AccessObject objects in AllForms collection.
        For Each obj In dbs.AllForms
            If obj.IsLoaded = True Then
                ' Print name of obj.
                MsgBox obj.Name
            End If
        Next obj
    Edit: this works, it was the IsLoaded line
    Code:
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    For Each obj In dbs.AllForms
        MsgBox (obj.Name)
    Next obj
    Set dbs = Nothing
    Can someone please assist, I've been trying to debug this forever.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I am scratching my head trying to understand where your problem is occurring. What is the file extension of your Access DB? Can you post all of the code, to include your for I, the IF Then Statement, and the msgboxes?

    Also, Navigation is a control, not a form. Are you having trouble over a WAN or is this all local?

  3. #3
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    The navigation form appears in the same list as my other forms, I am using it as a navigation form.. I do have navigation buttons on this form, though.
    Code:
    Private Sub cmdAddRep_Click()
    Dim olApp As Outlook.Application
    Dim myItem As Outlook.TaskItem
    Dim myDelegate As Outlook.Recipient
    Dim strName As String, strEmail As String, iPos As Integer, i As Integer
    Dim strFirst As String, strLast As String, iPos2 As Integer
    Dim strAccess As String, arrAccess(3) As String, strBadge As String
    Dim intConfirm As Integer, strAccessLevel As String
    strName = InputBox("Please enter the persons name")
    If (strName = "") Then Exit Sub
    Set olApp = GetOutlookApp()
    Set myItem = olApp.CreateItem(olTaskItem)
    Set myDelegate = myItem.Recipients.Add(strName)
    myDelegate.Resolve
    If (myDelegate.Resolved = True) Then
        strName = myDelegate.Name
        iPos = InStr(1, strName, ", ")
        strFirst = Mid(strName, iPos + 2)
        iPos2 = InStr(strFirst, "(")
        strLast = Left(strName, iPos - 1)
        If (iPos2 > 0) Then
            strFirst = Mid(strFirst, 1, InStr(1, strFirst, "(") - 2)
        End If
        strLast = Left(strName, iPos - 1)
        
        strName = Trim(strFirst & " " & strLast)
        For i = 0 To Form_Management.lstUsers.ListCount - 1
            If (Form_Management.lstUsers.ItemData(i) = strName) Then
                intConfirm = MsgBox("That user is already in the list. If you want to continue, this users existing entry in the security database will be updated to the new values. Would you like to continue?", vbYesNo + vbExclamation)
                If (intConfirm = vbNo) Then
                    GoTo NotAdded
                Else
                    strBadge = Form_Management.lstUsers.Column(1, i)
                End If
            End If
        Next i
        
        strEmail = myDelegate.Address
        strEmail = Mid(strEmail, InStrRev(strEmail, "=") + 1) & "@XXXXX.com"
        strAccessLevel = "Attendee (temp)"
        
        AddToDB strName, strBadge, strEmail, strAccessLevel
    Else
        MsgBox "Could not find user '" & strName & "' in the address book. Please confirm spelling and try again. You may also enter the representative using their first initial and last name as it appears in their email address (ie. JDoe).", vbInformation
        Exit Sub
    End If
    Form_Management.lstUsers.RowSource = ""
    LoadRepresentatives Form_Management.lstUsers
    Form_EventEntry.lstAttendees.RowSource = ""
    For i = 0 To Form_Management.lstUsers.ListCount - 1
        If ((Form_Management.lstUsers.Column(3, i) = "Scheduler") Or (Left(Form_Management.lstUsers.Column(3, i), 8) = "Attendee")) Then
            Form_EventEntry.lstAttendees.AddItem Form_Management.lstUsers.ItemData(i)
        End If
    Next i
    NotAdded:
    Set myDelegate = Nothing
    Set myItem = Nothing
    Set olApp = Nothing
    End Sub

  4. #4
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I copied all forms\tables\modules\queries to a new project and now it works. Looks like a bug with Access, not my code.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by BRZ-Ryan View Post
    I copied all forms\tables\modules\queries to a new project and now it works. Looks like a bug with Access, not my code.
    I was going to see if maybe there was a memory leak somewhere. Let us know if you encounter the problem again. You may need to close out one procedure before moving to another, set objects to = Nothing, etc.

  6. #6
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    New issue, when I add\remove items from a listbox, it doesn't update until I tab to another form\tab and go back to that form.

    What type of items to I need to set to nothing? Right now the only thing I'm doing that for is DAO queries and my Outlook objects. Also, should I be manually closing my form when leaving the tab? It looks like they close on their own because Form_Load gets called when I tab to a form.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Anytime you Set something to = something, it is a good idea to have it = Nothing as soon as you are done with it. If you are in Class modules there is less risk of memory leak because as soon as you are done with the class module, the memory should be cleared. I looked at your code briefly and it seems good, at a glance. I am simply concentrating on the remark that there was a memory issue. So I would consider what items are declared and look at what can be freed up. What procedures can be closed, etc. List boxes can be expensive if there are many records/rows.

    You can requery your list box from VBA to avoid having to navigate to a different record, etc

    Me.lstListBox.requery

  8. #8
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    The listboxes I am using are not bound--I am manually populating\adding\removing and the list of people will be small (less than 20). The list was updating normally in the past now it stopped working. This is actually the issue I was trying to repair when my Out of Memory issue started happening.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You can requery an unbound listbox. You just need to make sure that its properties do not equal an empty string or incorrect data.

  10. #10
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    What is the proper way to clear an unbound list? I've always used Listbox.Clear but VBA in Access doesn't support this. I've been using Listbox.RowSource="", is this ok? Requery isn't updating the list for me. I would think it would only work if it's retrieving data form a table.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I would use Listbox.RowSource=""

    But you need some sort of Row
    Source and column width settings in order to display data. So, requery is not worthwhile until after the RowSource has some SQL to sink its teeth into.

    You might try adding Me.listbox = "" if it is still displaying data and you want it to be blank. I usually use .Visible = False and just make the whole thing disappear when no data is available. I use this technique so the User is not staring at an empty control.

  12. #12
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by ItsMe View Post

    ...Navigation is a control, not a form...
    As of Access 2010 it is both! It's a style of Form intended to be used like a menu for Forms, Reports, etc.

    Quote Originally Posted by BRZ-Ryan View Post

    ...I copied all forms\tables\modules\queries to a new project and now it works...
    Solving the problem by moving everything to a new, blank file indicates that the original file was corrupted.

    Also, if by 'clear' you mean to clear a selection from a Listbox, just set it to Null:

    Me.Listbox = Null

    Linq ;0)>

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    @ Missinglinq

    Do list Boxes not accept empty string? I didn't test it and thought they would.

    I mentioned Navigation Control being a control vs a form because even if you use the wizard to create a form that includes the control, it is still just a control on a form. This is my experience with it and also what my reference material says about the control. I will add this. The darn thing is tricky to manage and when you build one out using the wizard, it becomes a very rigid form. So, it might as well be its own form type.

    Anyway, not trying to create semantics here. Just going off of my experience....

  14. #14
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by ItsMe View Post

    ...Do list Boxes not accept empty string...
    Not sure, I've just always set them to Null to do this.

    Quote Originally Posted by ItsMe View Post

    ...even if you use the wizard to create a form that includes the control, it is still just a control on a form...
    I believe you still misunderstand; Access 2010 and beyond has a new type of Form called a Navigation Form. It's sole purpose is to allow a user to navigate not through Records, but through Forms, Reports, Queries, etc (whatever the developer decides to place on it) by simply clicking on a corresponding 'tab' that runs across the top of the Nav Form; sort of like a Tabbed Controls. I don't run 2010, but don't think I would use this feature even if I did; I usually 'roll my own' menu Form to do this kind of thing

    Linq ;0)>

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Missinglinq View Post
    ......don't think I would use this feature even if I did; I usually 'roll my own' menu Form to do this kind of thing

    Linq ;0)>
    I spent some time with the Navigation Thing and I came to the same conclusion. Even after I got it to function to my liking, it would blink and flash as I clicked on the various Tab things. I am sure there is a place for it and a reason why it is offered. For the Desktop Apps I am creating, I still prefer my own "Home Page". Thanks for replying Linq

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

Similar Threads

  1. Impossible Access error (Out of memory)
    By guydols in forum Programming
    Replies: 8
    Last Post: 11-29-2012, 09:52 PM
  2. Replies: 0
    Last Post: 11-29-2011, 01:06 PM
  3. Replies: 9
    Last Post: 11-09-2011, 11:34 AM
  4. disk space or memory error
    By Cmitchell in forum Database Design
    Replies: 1
    Last Post: 04-04-2011, 03:36 PM
  5. "Out of memory Error"
    By Fearl3$$ in forum Access
    Replies: 20
    Last Post: 03-25-2010, 09: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
  •  
Other Forums: Microsoft Office Forums