Run-time Error 2004 (not enough memory) on OpenForm command

[smsmail]

Hello,

I am getting run-time error 2004: "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again"

The form is a tab control form containing 12 subforms. I suspect that the possible problem exist in a subform that I last made modifications to. The modifications I made were to make some controls invisible and change their tab stop values. Could these changes have cause a problem?

Thanks so much for your help!

 

[dhookom]

I expect the issue might be the number of subforms assuming they are displaying values from tables/queries and might have lookups/combo boxes in them.

Duane
Hook'D on Access
MS Access MVP

 

[smsmail]

Hi Dhookom

That is a possibility. How would I prevent the error from occuring?

Need advice...still somewhat new at Access.

 

[dhookom]

Reduce the number of record/row sources. We don't have any idea how many of these your form/subforms/controls use.

You can some times swap subforms in and out as needed by the user.

Duane
Hook'D on Access
MS Access MVP

 

[smsmail]

Reducing the record/row sources? Does this mean that I want to make sure all record source objects are closed after execution?

 

[dhookom]

A record/row source isn't the same as a DAO or ADO recordset. Record/Row sources don't need to be closed. Recordsets do. Are the recordsets you aren't telling us about?

Are you going to provide more information on the number of record and row sources? Every lookup field, combo box, list box, and chart have a row source. Every form and subform typically have a single record source.

Duane
Hook'D on Access
MS Access MVP

 

[smsmail]

Good morning Duane,

Now I understand what you are saying. DUH!
The 12 subforms, each have two controls that are lookup fields (combo boxes). The controls are "Client Name" and "Job Number".

1. The client name rowsource query result in 1317 rows selected.

2. The job number control rowsource query result in 500 rows selected.

I have defined the rowsource query for these fields on the property sheet Data tab.

Question: When loading the 12 subforms in the tab control form, are the rowsource queries on the property sheet automatically executed? If so, that is a problem!

I tested one of the subforms. I execute the rowsource query in the On Click and Got Focus events and it works just fine. Should I proceed with the rest of the subforms or do you have a better suggestion?

Again, thanks for the mentoring! Still learning Access. There is a lot to it. I am a mainframer!

 

[dhookom]

Do all of the subforms need to be editable?
You might have better performance if you take the Row Source table and add it to the Record Source of the subform. Then change the combo box to a text box.

I believe all of the row sources are refreshed/populated when the form is opened.

I'm not sure what you mean by "I execute the rowsource query in the On Click and Got Focus events and it works just fine." I expect "execute" is used to describe action (delete, update, and append) queries.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

That is a lot of subforms. The first thing to do is only load the first tab subform, then load the others only when you select the tab.
Here is how

http://www.fmsinc.com/MicrosoftAccess/Performance/Forms/LateBinding.asp

If one of the subforms is causing a problem this would be a way to find it.

You could even go beyond this with the individual subforms. I Do not think you actually would need this but it can be done. When a form opens the rowsource of the controls load first. As Duane suggests you can write code that for combos and listboxes you do not even have a rowsource loaded until they get focus or another appropriate event. You can even do this with the forms recordsource to load it only when needed.

 

[smsmail]

Thanks Duane and MajP.

You guys are very much appreciated by persons like myself. I have definitely learned a lot by way of this site.

Duane:
What I mean by "I execute the rowsource query in the On Click and Got Focus events and it works just fine."

In the GotFocus event of the Client ID control of one of the subforms, I have the following code:

Private Sub ClientID_GotFocus()

    Me!ClientID.RowSource = "SELECT [CLIENT].[ClientID], [CLIENT].[ClientName] FROM CLIENT ORDER BY [ClientName];"

End Sub

I think this may work because now the fields will only be populated when it gets focus, and not when all subforms are opened. I will make these changes in all the subforms for both the client and jobnumber fields. I don't need the code in the OnClick event of the fields.

MajP, thank you for the link, that is a must have for me. I will definitely implement that code.

Any other suggestions are appreciated and welcomed.

smsmail
out of Washington D.C.