A form constantly asks to be saved whenever any changes are made to VBA in other forms (1 Viewer)

[EL_Gringo]

I have several forms with VBA. Whenever I make changes to VBA in one of them, a specific form asks to be saved, even though no changes have been made to its VBA. I tried compact and repair database and decompile, no success.

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[gemma-the-husky]

Maybe when you open that form you do something in code that affects the form design. Eg, change colours, or labels, default values, anything, and that's why it asks you about saving the changes. Even if you explicitly set a value that is the same as it was, that might set the "changed" indicator on the form.

 

[EL_Gringo]

If you want to provide db for analysis, follow instructions at bottom of my post.

Will give it a try, but it will take some time. Thanks for the suggestion.

 

[EL_Gringo]

Maybe when you open that form you do something in code that affects the form design. Eg, change colours, or labels, default values, anything, and that's why it asks you about saving the changes. Even if you explicitly set a value that is the same as it was, that might set the "changed" indicator on the form.

Thank you for the suggestion. Please see the addendum to my original question.

 

[EL_Gringo]

I have several forms with VBA. Whenever I make changes to VBA in one of them, a specific form asks to be saved, even though no changes have been made to its VBA. I tried compact and repair database and decompile, no success.

Addendum: I forgot to mention that the offending form is a subform on the main for, which is always open. This main form has - in different tabs - three subforms, and only one causes the problem. And no, the problem is not limited to changes on the main form or one of the other subforms.

 

[gemma-the-husky]

If you don't change the main form, does it still want to save the subform?

If you are sure you aren't doing anything to make it change, the best thing might be to recreate it, delete or rename the original, and see if the problem goes away.

 

[The_Doc_Man]

Let's start from first principles. Are you explicitly using VBA to make structural changes to a form? Normally you need to be in design mode to even be able to do that, so the question becomes - how do you open the form using VBA to MAKE the changes?

 

[EL_Gringo]

Let's start from first principles. Are you explicitly using VBA to make structural changes to a form? Normally you need to be in design mode to even be able to do that, so the question becomes - how do you open the form using VBA to MAKE the changes?

No structural changes are made to the form from anywhere. It is just open in its main form, sitting there. When I work on some pieces of code of other forms, the 'offending' form is not affected whatsoever. I might just do as little as deleting a line of code on another form, and, when saving that form, the offending one wants to be saved as well.
By the way, some of my continuous forms do have code that sets their height (Forms(FormName).InsideHeight), and vertical scrollbar on/off depending on the number of records to be shown.

 

[EL_Gringo]

If you don't change the main form, does it still want to save the subform?

If you are sure you aren't doing anything to make it change, the best thing might be to recreate it, delete or rename the original, and see if the problem goes away.

Yes it does, even if I don't change anything on the main form or one of the other subforms. You might be right and I will have to recreate the form from scratch.

 

[Pat Hartman]

Please post the entire code module for the form with the problem.

 

[The_Doc_Man]

I might just do as little as deleting a line of code on another form, and, when saving that form, the offending one wants to be saved as well.

OK, a little more fact-finding. If you have TWO main forms open and sitting around doing nothing, and then make a change to the code in a THIRD form, does the second (main) form also want to be saved?

 

[EL_Gringo]

I have several forms with VBA. Whenever I make changes to VBA in one of them, a specific form asks to be saved, even though no changes have been made to its VBA. I tried compact and repair database and decompile, no success.

OK, a little more fact-finding. If you have TWO main forms open and sitting around doing nothing, and then make a change to the code in a THIRD form, does the second (main) form also want to be saved?

I have only one Main form open with the mentioned three subforms. One of the subforms wants to be saved when I make changes to a third form which has no reference to it or to the main form

 

[EL_Gringo]

Please post the entire code module for the form with the problem.

Here it is, a bit lenghty...

 

[EL_Gringo]

Update: I erased all code from the offending form, decompiled and recompiled, problem gone. Inserted the same VBA saved in a text-file (Notepad), problem again. I then deleted the sorting modules (see posted file), problem gone. Which is strange, because I use the same approach for sorting in the other two subforms on the main form, with no problems. I will now proceed to compare those sorting modules line by line to check for differences.

 

[gemma-the-husky]

At least your problem is "moving". A moving issue means you are making changes.

 

[Pat Hartman]

@EL_Gringo Your validation code is in the wrong event. It is like closing the barn door after the horses have escaped. The record has already been saved with the bad data by the time the form's BeforeUpdate event runs. Doesn't matter that you put focus back in the control and raise an error message. If you just close the form at that point, the damage has been done.

Move the validation code to the form's BeforeUpdate event and use
Cancel = True
To stop Access from saving the bad data. Put that line after or before the SetFocus

Not all the code seems to be validation so leave the code you want to run AFTER the VALID record is saved in the AfterUpdate event.

Keep plugging on finding the error. I didn't see anything obvious in the code I read but having so much in Spanish (I think) interfered. Plus, I don't know what the forms that are being opened do.

 

[EL_Gringo]

@EL_Gringo Your validation code is in the wrong event. It is like closing the barn door after the horses have escaped. The record has already been saved with the bad data by the time the form's BeforeUpdate event runs. Doesn't matter that you put focus back in the control and raise an error message. If you just close the form at that point, the damage has been done.

Move the validation code to the form's BeforeUpdate event and use
Cancel = True
To stop Access from saving the bad data. Put that line after or before the SetFocus

Not all the code seems to be validation so leave the code you want to run AFTER the VALID record is saved in the AfterUpdate event.

Keep plugging on finding the error. I didn't see anything obvious in the code I read but having so much in Spanish (I think) interfered. Plus, I don't know what the forms that are being opened do.

Pat, thank you for drawing my attention to the validation problem, I will check that part. And sorry for the Spanish, I have the bad habit to use whatever comes to mind first - a wonder there is no German as well
As to the primary problem, please see my update - there seems to be a deeper problem with my application.

 

[EL_Gringo]

Update 2: After substituting the offending code - at least what I thought it to be - from all subforms on the main form, the app now constantly asks for the main form to be saved whenever I change code in other, unrelated forms.
It seems there is a deeper problem.
I checked the System Objects (SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects and found more than 350 (!) Type 5, queries, all starting with '~sq_'. However, my application has only one saved query, and some of these '~sq_' are even from a totally different application (which as well has only one saved query). I did not use this other app as a starter for this one, and the other app does not have this problem.
I do have both apps open at the same time and am wondering if that could cause a problem.

 

[Pat Hartman]

Feels like corruption. Have you done a C&R and a decompile?

When you write SQL strings as the RecordSource for forms/reports or RowSource for combos or listbox, Access builds a querydef and saves it with a name starting with a tilde. None of these are shown in the Navigation Pain (sic). Why does Access do this you might ask? Since you asked, it is more efficient The first time the querydef is run, Access creates the execution plan and saves it so it doesn't need to be recreated each time the object is opened.

I know a lot of experts hate the QBE. They're all about pristine formatting and doing it their way. Granted the QBE is lacking. The SQL view has a seriously poor editor and the QBE view takes a little getting used to and is awkward for subqueries.

But, I've been writing SQL since the 70's. At that time, there was no option. All the SQL I wrote was embedded in my COBOL programs. Even the DB2 utilities had poor text editors. I had to compile the program to find my typos and test the query. I dreamed for YEARS of a tool such as the QBE to save me from the tedium of typing embedded SQL. And then in the early 90's, I found Access. It was love at first sight, mostly because I discovered I could link to DB2 and create forms that actually updated the tables on the IBM mainframe. I thought I had died and gone to heaven. So, even now coming up on 50 years later (obviously not all Access), bad as the QBE is, I still prefer it to having to remember and type without error the names of all the tables and fields i work with. Maybe because I'm a consultant, this part of it is more important but in any given week, I might have to touch a half dozen applications, that I haven't written or even seen for years. Who can remember the names of all those tables and fields? So, even when I will actually embed the SQL because it will be dynamic and I have to change it on the fly, I still start with QBE to at least build the Select clause. Point and click is the friend of the person who can't remember s***. AND it is slightly more efficient to use a querydef anyway. QBE used to be a lot more efficient because the process of creating the execution plan requires a great deal of memory and the consequential bloat meant that even the user's copy of the FE needed to be compacted regularly but MS cleaned that up a few years ago. That's why I use batch files to distribute my FE going back to the early days. They always replace the FE every time it opens. The LAN transfer time is inconsequential and the upside is you never have to worry about bloat no matter what you do and you also reduce your potential for corruption.