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

[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.

 

[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.

 

[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.

 

[EL_Gringo]

Late but...
Thanks to all for trying to help. To Pat, yes, I did both. As well, I created a new, blank database, imported all elements and relinked by hand to the BE. Same problem. I give up now, as the problem is annoying, but not lethal to my app.
I'm now facing a new problem which IS lethal. It has to do with running the app on machines with an Access language other than English. I'll post separately.
Again, thank you all for your time and effort.

 

[The_Doc_Man]

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.

Normally, those things that start with "~sq_" are dynamically created control queries for forms or reports that have, for example, .RowSource objects. Do you have any dynamically created or dynamically modified row source objects?

 

[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.

Last update and problem solved

Thank all of you for trying to solve this and for the other good advice. And I'm truly sorry to report back only now.

The devil is in the detail: In fact, one of the subforms manipulated a value in a textbox of the main form. I have no idea why that should trigger the 'has changed' of the main form, but it did. I have other forms with subforms doing the same without this phenomenon, but after erasing the offending line, the problem disappeared.