Out of memory error

[BRZ-Ryan]

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.

    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

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.

[ItsMe]

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?

[BRZ-Ryan]

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.

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

[BRZ-Ryan]

I copied all forms\tables\modules\queries to a new project and now it works. Looks like a bug with Access, not my code.

[ItsMe]

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.

[BRZ-Ryan]

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.

[ItsMe]

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

[BRZ-Ryan]

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.

[ItsMe]

You can requery an unbound listbox. You just need to make sure that its properties do not equal an empty string or incorrect data.

[BRZ-Ryan]

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.

[ItsMe]

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.

[Missinglinq]

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

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

[ItsMe]

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

[Missinglinq]

...Do list Boxes not accept empty string...

Not sure, I've just always set them to Null to do this.

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

[ItsMe]

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