Not Enough Memory caused by Me.Requery??

[Woodsjar]

Hello All,

I am using Access 97 SR-2 running in Windows XP

I am getting this error “there isn’t enough memory to update the display. Close unneeded programs and try again” after closing a pop-up form box on a main form.

I have two forms Change form which is the main form and Add_ABC form as the pop-up form. Below is the illustration of this:

Change
Change_ID PK
ChangeCategory
ChangeNo
ABC (Y/N Field)

Add_ABC
ABC_ID
Change_ID FK
Quantity
DateReleased

The main form (Change) opens a pop-up form (Add_ABC) when Y is selected. What I want to happen when the pop up form is opened is to have the PK on the main form match the FK on the pop-up form. Also, I want the main form to stay on the current record after I do data entry and close the pop-up form.. I also want the ID of the newly entered record on the main form to show on the pop-up form so I added an Me.Requery

I placed a code on the after update of ABC field to get the desired result. Below is the code:

Private Sub ABC_txt_AfterUpdate()

Dim i As Integer
i = Me.Change_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "Change_ID=" & i
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If Me.ABC_txt = "Y" Then
DoCmd.OpenForm "Add_ABC", , , "Change_ID=" & i, , acDialog

End If

End Sub

I got these codes from different posts as I don’t have enough knowledge of VBA. The codes are working fine but the problem is I always get the out of memory error after I close the pop-up form.

I think the problem lies on the code, because whenever I remove the me.requery, the memory error is not showing up but of course the desired result is not happening either.

Maybe the code is too complicated. Is there a way to achieve the same result I want with fewer codes and without the memory error?


Thanks All

 

[Woodsjar]

Private Sub ABC_txt_AfterUpdate()

Dim i As Integer
i = Me.Change_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "Change_ID=" & i
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If Me.ABC_txt = "Y" Then
DoCmd.OpenForm "Add_ABC", , , "Change_ID=" & i, , acDialog

End If

End Sub

Update: When I remove the code

Me.Requery

With Me.RecordsetClone
.FindFirst "Change_ID=" & i
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The FK on the pop-up form matches the PK in the main form for saved records and there is NO Out of Memory error encountered. The problem is when I do data entry on the Main form and then continue to the pop up form, the FK doesn't show. The FK only shows on the pop up form for records already saved on the main form.

If there is a code that automatically saves entries as it is typed on the main form then the newly created PK will surely show on the pop-up form. I wouldn't need Me.requery and the other codes which I believe is causing the Out of Memory Error.

Any ideas? suggestions please

Thanks

 

[mahenkj2]

If there is a code that automatically saves entries as it is typed on the main form then the newly created PK will surely show on the pop-up form.

I am not sure but below lines might be helpful to you. You can put this in the on click event of button which opens the pop up form before open command.

RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

 

[Woodsjar]

RunCommand acCmdSaveRecord
or
If Me.Dirty Then Me.Dirty = False

Thanks. I tried this code as you said and also on the mainform but it doesn't save the record.

Any other suggestions/ ideas please. I am now doing a test of the DB but the memory out error is really persistent.

 

[Woodsjar]

Still haven't solved this problem. Any other suggestions from other members please.