Creating new form from existing form

[Dave E]

How large is the database? Is it split?

I have reduced the db to a main form with 2 subforms and a few small pop-up forms to show functionality.
2 tables, a few queries, and a fair bit of VBA code.
The db is not split.
Would compressing the db be worthwhile?

DaveE

 

[Dave E]

OK, the db has been compressed down to almost nothing...
It should get through
Before compressing it exhibited the same problem, as described earlier.

I have cut it down, removed a lot of forms/reports/tables/code. I have disabled some of the controls on the MainForm to avoid error messages for missings objects. MainForm is the primary form.

I look forward to comments about the problem (but not necessarily the design).

Thanks for any forthcoming help

DaveE

 

[Dave E]

I'm sorry if I missed a question somewhere along the line. Your last 2 questions were answered though; size and whether it was split...perhaps there was a time delay in you receiving the responses.

I have compacted the db so many times I've lost count. It was compacted after being reduced and, prior to that, it was decompiled/recompiled as recommended by your good self in the way you suggested.

The db, after zipping went from 3.7MB to 837KB.

DaveE

 

[MajP]

I'm sorry if I missed a question somewhere along the line

I think the question is now that we can see the database, can you walk us through how to recreate and see the issue. Use actual form and control names.

 

[Dave E]

There are only 2 issues that I can find.

The first involves the opening of the form in View mode and then selecting Design View by right-mouse-clicking the form's top bar or anywhere on the open space on the form. In some, but not all cases, the db will close leaving the database locking file open in the folder and preventing re-opening until the Microsoft Access (32bit) task is removed from the Background Tasks section of Task Manager.

The second issue is that doing the same thing - selecting Design View, can result in the form remaining open but All combos frozen (no action), although the Buttons along the top of the MainOptions screen remain operational. Again, the locking file is still open and the database requires the same Task Manager operation to re-open and work.

There are no specific controls that cause the problems. Just the action of switching the views of the MainOptions form will cause the problem. I am still unsure if this is an Access or Windows problem.

The mouse is wireless and the drivers up-to-date and doesn't misbehave with any other program.

 

[MajP]

Make sure I got the steps correct.
1. Open the MainOptions switch to design view using right mouse. Db may close
2. Open the MainOptions and switch to design view using right mouse. Form may remain open but combos locked.

Pretty sure the problem is in your API calls in the form. I did the above numerous times and cannot recreate the issue. However before starting I commented the loadcursor function out because I needed to make it 64 bit compatible. Unless you need it get rid of it.

 

[MajP]

That is exactly what the problem is. Open in design view and do not move the mouse over the subform that causes the finger pointing mouse (no call to the API). Close the form no problem. Do it again and make the finger pointing mouse come up (call the API function) and it crashes/locks. Fails every time. 100% repeatable as far as I can tell.
If you really need this API function for the fancy pointer I would reach out to @sonic8; he is probably the API guru on this site. @isladogs is also pretty good. I cannot help with that, not my area.

 

[Dave E]

You said - "The database is too large for the server - it says."
So I asked TWO relevant questions.
1. How big is the database? NOT how big will the database be after you have removed a bunch of stuff. That is not relevant to the error.
2. Is the database split into a FE and BE and you never answered that one either. A database that is not split is always larger than it needs to be and is also prone to corruption.

Neither may be relevant but when you are getting errors about the db being too large, they certainly need to be asked.

The problem seems to be the API so perhaps someone can offer a different alternative.

Hi Pat,

Looking back, chronologically, through this thread I see that I had already supplied the answers prior to your questions. Maybe that was why you didn't see them. Thanks for your help.

DaveE

 

[arnelgp]

i don't see any problem with MainForm?
btw i change it to pop-up (centered).

 

[MajP]

i don't see any problem with MainForm?
btw i change it to pop-up (centered

The problems is on MainOptions. That was my fault I wrote MainView. I corrected it.

Buttons along the top of the MainOptions screen remain operational.

1. Open the MainView.
2. There is a plantlist subform on the left. Take your mouse and move it in the subform. This brings up the finger pointer.
3. Switch to design view
4. If it does not crash. Close the db and it will be locked.

 

[MajP]

Although I got rid of the setcursor and loadcursor API calls for testing, I still crashed, although less often. I would try removing all API code and see if that helps. This version posted by @arnelgp for me is even more unstable then previous versions. This crashes when I simply close the form. So I really think the culprit is API.

 

[arnelgp]

i remove the Load cursor/Set cursor.
instead add a Transparent button overlaying on the two textbox.

 

[Dave E]

Although I got rid of the setcursor and loadcursor API calls for testing, I still crashed, although less often. I would try removing all API code and see if that helps. This version posted by @arnelgp for me is even more unstable then previous versions. This crashes when I simply close the form. So I really think the culprit is API.

Hi MajP,

I 'commented out' all references to LoadCursor and SetCursor from the form (the only only that calls the LoadCursor action) and the function in Module1, compacted and compiled it, but the problem persists. The trouble is that the issues happen at, what seems to be, random moments although it is more likely to happen as soon as the form is opened and then Design View clicked soon after.
Although I was optimistic about API, I'm not so sure now.

DaveE

i don't see any problem with MainForm?
btw i change it to pop-up (centered).

The main form - MainOptions, already had pop-up set and centred, together with fill screen etc...

 

[Dave E]

i remove the Load cursor/Set cursor.
instead add a Transparent button overlaying on the two textbox.

I commented out the Load/SetCursor and compacted and compile to no avail - the same issue persists. It is not a consistent bug and, just when you think you've found the problem, it screws you up...

Could you explain the second part of your post about using a transparent button on, presumably, the LatinName and CommonName test fields on the PlantList subform?

DaveE

 

[arnelgp]

on design view, click on PlantList subform.
On Property Sheet you will see there are 2 command buttons (cmdLatin, cmdCommon).
these command buttons are "in-front" of those two fields. so you think you are clicking on
the field, but what you are clicking is the button. the buttons Back Style is Transparent.
and the Cursor On Hover property is set to Hyperlink band.

 

[Dave E]

on design view, click on PlantList subform.
On Property Sheet you will see there are 2 command buttons (cmdLatin, cmdCommon).
these command buttons are "in-front" of those two fields. so you think you are clicking on
the field, but what you are clicking is the button. the buttons Back Style is Transparent.
and the Cursor On Hover property is set to Hyperlink band.

Hi arnelgp,

I see that, but it didn't stop the issues.
Thanks for trying though.

DaveE

 

[Dave E]

on design view, click on PlantList subform.
On Property Sheet you will see there are 2 command buttons (cmdLatin, cmdCommon).
these command buttons are "in-front" of those two fields. so you think you are clicking on
the field, but what you are clicking is the button. the buttons Back Style is Transparent.
and the Cursor On Hover property is set to Hyperlink band.

Hi arnelgp, No change, I'm afraid.

The problems is on MainOptions. That was my fault I wrote MainView. I corrected it.


1. Open the MainView.
2. There is a plantlist subform on the left. Take your mouse and move it in the subform. This brings up the finger pointer.
3. Switch to design view
4. If it does not crash. Close the db and it will be locked.

MajP,

I tried that too without any success. This is real challenge...

 

[MajP]

I tried that too without any success

Sorry that was not meant to be a fix, that was supposed to describe how to recreate the problem. I was pretty confident that it was the API call since if I did not move over the subform and went directly to design view it would not crash but crashed every time I hovered over the subform.
I do not think this is traditional corruption. I do not think a compact and repair, or a decompile will fix this. This form is complex. Did you save to and load from text all subforms as well as the mainform? If it is not the API then I think it is actually in the design and Access just does not like it. I am thinking this is a bug and not corruption. I will dig into it.
I have had problems like this with a TreeView control. If you did a certain step in design view it would fail and never recover again. It would always require starting with a clean control and rebuilding in the correct order.

 

[MajP]

I am baffled.
I did everything I could think of. Maybe someone else can look.
1. Removed all API code
2. Saved all objects to text
3. Created new database
4. Imported tables
5. Compact and Repaired
6. Read from text all objects

That is about as clean as could be. I looked at that remaining code and nothing popped. You do have some recordset looping in the form open and current events. I did not dig to far into that, but if it is the code it has to be something in an event like a mouse move, form open, or current. It is something that is happening automatically without a user interaction (not a click event or data update).

Attached is the built from scratch "clean" database but still the same problem.

 

[Dave E]

I am grateful for the time you've spent on this problem but I think, like me, you like a challenge.
I worked on the database last night without a single issue. It was a pleasure to be able to do that, but that only means I've been lucky.
I will continue to develop the database and look out for any more problems.

As far as I can tell, I have restored the db to the state before I submitted it, i.e. API working and code as original.

If it re-occurs then I will think about changing the MainOptions form design to simplify its operation.

In the meantime - thanks again for your input and time. And Pat's.

DaveE