Not enought memory error when opening a form 3

[Mayhem9]

Hello,

I have been working on a form and now I am getting a "There isn't enough memory to perform this operation..." error.

I am running 4GB of RAM and have no other programs running and have closed down all the sleepers in the system tray. Task manager shows 3% memory use. Opening other forms are fine and I can open the relevant table as well.

I have tried double clicking on it, opening in design view and even tried using a macro. All result in the same error. When the form is highlighted and I click on the code icon in the menu bar, I get the same error. However, If I open up a module I can then open the code.

So far I have tried the following:

1 - Compile the code (no errors)
2 - Compact and repair database
3 - Detect and repair
4 - Bad language

None are working - any suggestions?

Cheers,
Darren

 

[MajP]

No you described it correctly. All we are saying is that it is likely that in your modules you have this procedure copied twice

Public Function BrowseFiles()
code
End Function

and in another module or the same module you will likely find again.

Public Function BrowseFiles()
code
End Function

If you do not see it, go ahead and post the new db.

 

[Mayhem9]

Hi MajP,

Sorry if I came across as negative or unappreciative of your suggestions. It certainly wasn't the case. I obviously didn't fully understand what you were saying and thought I had taken the necessary steps to ensure there wasn't a duplication in the form and I thought you were saying the name couldn't exist in the form code as well as a module. This is why I made the point hat it was working previously.

Whilst I didn't have any duplicates in the same form, I actually had another copy of the form (named Tool_Log_Old). I have deleted this form and it has resolved my issues.

However, I am getting an intermittent error when trying to compact and repair the database. I close everything, choose compact and repair and get the following error:

You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'Laptop'. Try again when the database is available.

It happened three out of four times and I am beginning to wonder if the form is going to crap itself again, as it did when I initially posted this question. On two occasions I even got the Access has stopped working, taken its bat and ball, and gone home!

Thanks for your help,
Darren

 

[PHV]

You should put your Public function(s) into a standard code module instead of the form's code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

As PHV points out public functions should go in a standard module. Would have solved the problem.

Two public functions with the same name in two different form modules is still two functions with the same name.

It sounds like time again to create a blank db, and import the tables, queries, forms, and modules. I do this often, just to keep things clean. Also it is good to reboot when you start seeing these kinds of errors.

Also you can compact with

http://support.microsoft.com/kb/295334

 

[Mayhem9]

Thanks to you both for your responses,

Using Ctrl F didn't turn up any other instances of BrowseFiles
Even when searching the whole project ?

This is how I actually made the connection between the two versions of the form. When in doubt try different options!

For some strange reason I didn't see this one either and only noticed it as I had three email alerts this morning but could only see two new ones.

You should put your Public function(s) into a standard code module instead of the form's code.

I'll try to figure out how to do this. Is this similar to or link in with the database information table that you referred to in another thread MajP?

It sounds like time again to create a blank db, and import the tables, queries, forms, and modules. I do this often, just to keep things clean. Also it is good to reboot when you start seeing these kinds of errors.

Oh well practice makes perfect I guess! I assume that this time I can import the form that I had to rebuild at the start of this thread as opposed to rebuilding it again?

Cheers,
Darren

 

[MajP]

Darren,
1) A standard module is like your modBrowseFile. It is a place that you normally put code that you want to use in multiple places in the application. Most of the functions and procedures are Public so that you can call them from any form, report, or other module.

A Class module is like faytComboBox. It is a module that creates a custom object. The object can have properties and methods, and events.

A form or reports Module is a special Class module. Along with the properties, events, and methods it also has associated with it a physical object: the form, report, or in excel a worksheet, in Word a document, power point a presentation ...

So you already have been "
I'll try to figure out how to do this."
Your modBrowseFile is a standard module with public methods that you can call from anywhere in your application.

So in truth I am not really sure what the problem was. You should be able to have multiple copies of the form in your database as long as the procedures in the form modules are private. If I have toolLogOld and a copy toolLogNew and they both have

Private Sub AddImage_Click()
Me!ImagePath = BrowseFiles
If Trim(Me!ImagePath & "") <> "" Then
Me!ImageFrame.Picture = Me!ImagePath
End If
End Sub

There should be no problem. The method is private.

But here is my guess. You had this method in your modBrowseForFile

Public Function BrowseFiles()
Dim sSave As String
....
End Function

And you also had that same public method in the old copy of your toolLog. Hence you would have two methods with the same name: one in the standard Module and one in the form's module.

2) Yes just import the objects into a clean db
File -> Import External data

Make sure you recheck your references if you added any references.

 

[Mayhem9]

Hi MajP,

Yes - there is "Public Function BrowseFiles()" in the modBrowseFiles module but it also exists in the current version that is working, so I am unsure as to what the problem was.

In adding in the filter/search function that you generously provided I also got an error regarding the object procedure when I compiled the code. "Private Sub cmdFilterBasic_Click()" was highlighted and I was informed it wasn't defined.

I clicked on Event Procedure and it added "Private Sub cmdFilterBasic_Click()" and "End sub" around the code that I had inserted. Once I deleted the duplications, I selected compile and there were no errors. I tested the form and all was well.

So when I create an event in a form is it best to open the code window via the properties window?

Cheers,
Darren

 

[MajP]

So when I create an event in a form is it best to open the code window via the properties window?

You do not have to, but it is safer.
Here is why.
1)If you create from scratch, you will not automatically get
onClick: [EventProcedure]
in the form property.

So even though you have an event written it will not fire, because you do not have a property to say fire the event procedure. But if you come in from the property window, this happens automatically.
2) Also a mispelling or extra space will cause an "undefined error"
Private Sub cmdFilterBasi_Click()

Yes - there is "Public Function BrowseFiles()" in the modBrowseFiles

That is expected. You should have a public function in this module. But if you had the same function in your old tool log (the same function in two locations), you would have gotten the ambigous name error. And then by deleting the old toolLog that error would have been fixed.

Again I always compile before testing the form. This way I find a lot of errors first. And again always use "Option Explicit".

 

[MajP]

http://www.4shared.com/file/217898219/b4aa23fe/ShortCutMenuControl_V2__2003_.html

See if you can run this demo. You may find it interesting for you tool types sub types.

 

[Mayhem9]

Thanks MajP,

I checked and Require Variable Declaration was selected. I checked a few other modules including class modules and it was also checked, so am I correct in assuming that once it is checked in a database it is applied to all modules?

There should be a list of these tips somewhere on this site. I doubt very strongly that I would have learned to compile code after any changes, to add option explicit, creating event procedures through the properties window or not to put look ups into tables just from looking at other databases. This is especially true of the North Wind db, which has look ups in the tables.

Perhaps I should start a thread asking for people to add their must do tips or "ten commandments". It would be a great resource for the site

I'll have a closer look at the "bells and whistles" database. Thanks for uploading this.

Cheers,
Darren.

 

[MajP]

Once you set the option, it will set it on all future modules on all databases. It just will not go back and fix ones that did not previously have it.

Yes VB/vba has a lot of short cuts that can turn into real painful (long cuts). You spend way more time trying to find the error then time saved in typing. Also vb has a very good debug capability for finding problems in code. Most people are completely unaware how to use it.

There is probably a FAQ somewhere out there, if not I may start one.

Here are some other not so short cuts
1) Access (vba) lets you give everything the same name (actually encourages it when you use the wizards) Try to debug code when the form, report, table, query, and a control are all called "Tools".
2) VB supports two types of notation for collection, which most people do not understand when to use which. Bang and Dot.
forms("myForm").controls("cmboOne").value
forms!myForm.Controls!CmboOne.value
3)To add to the confusion of 2 you also have default properties which you are not required to write.
So
forms!myForm.Controls!CmboOne.value
can be written
Forms!CmboOne
4)Access allows you to give fields names with spaces or use reserved words.
Tool Type
This will crash in code unless you remember to inclose in []
By the way "Type" is a bad word to use as a field name, and you use it. Many objects have "Type" as a property so it is a Reserved word. It would be also be bad if you mad furniture to have a field or table called "Table", or if a farming database to have a field or table called "Fields". Other bad words: value, name, hieght, width,
5) Vb allows you to name the field and the control the same name. Try to avoid
field: toolType
textBox: txtBxToolType

6) Vba also has many different objects that have the same name example Recordets.
You have a DAO recordset and an adodb recordset. If you do not specify it guesses. So for things with competing names declare specifically
dim rs as dao.recordset
or dim rs as adodb.recordset
not
dim rs as recordset

I could go on. In order to make things easier, MS opens a lot of holes to fall into.

 

[Mayhem9]

Thanks MajP,

I am saving these pearls of wisdom with the others I have collected along the way. When I get some time I will try to see in a FAQ exists. I wasn't able to find one but I was rushing at the time.

Cheers,
Darren