Solved Form/Subform prevent saving data on main form when changing focus on subform

[Romio_1968]

Hello there

The Form called Add_Frm_Title is opening from the main menu using the "Adauga Titlu" button on the Main landing form.

Add_Frm_Title contains a subform called TtileAuthor_Junct_Sfrm
Add_Frm_Title is linked to Title table and the Subform to a Junction Table, accessed via a query, that adds existing or new authots to the Title_ID

There are two behaviours here that bugs me.
The user must first have to fill all the required fata on the Add_Frm_Title. When trying to move focus on the subform, the Add_Frm_Title is tested for the required data, and only when all fields (controls) are properly filled, the control is passed to the subform, allowing the user to start adding data in in.
So here comes the first problem
If the Title does not have an author (there are publications with no author at all), the record in Add_Frm_Title is saved by clicking the button labeled "Salveaza Titlul". After testig the required fields, if everithing is OK, the Title is saved,
But the data is also saved even when the control is passed to the subform, and this is an undesired behavior that I should prevent. The user may change his mind or just roamning on the form and data can be accidentaly saved.
Moreover, this unwanted save leads to an other issue. The process of adding a title is aborted by hitting the New button. If there was no interraction with the subform, the title is not added to the Title table. But if the subform was filled, all the data will remain, in the Title table and teh Junction table (if anything was added)

Anothe annoying thing is related to the same save button.
Beside the fact that the data is actually saved by passing control to subform, in which case the button is redundant, it does not have a clean behavior.
If by any mean I try to add inside the Save button code any kind of instruction to go to a new record, when passing control to the sub, after that unwanted data saving, the main form is reset. This is as much unwanted as the data saving is.
As well, assuming that all the data on the form and sub is properly filled, by cliccking Save all the data remains on the form until the New button is hit. Sure, I can add some "The data was saved, please press New", but is not that clean.

Thank you.
Please don't bother on other issues then those related to the described unwanted behaviours.

 

[bastanu]

Please review the updated file, should work as you describe.

Cheers,

 

[Romio_1968]

Bastanu, actually it doesnt.

When opening a fresh form from Main, works fine until passing control.
If the required fields are not filled, the error message pops up asking to fill them before going to the form.
If all the fields are properly filled, control is passed to the sub, but the title is saved, so the behaviour rests.
I know that this is the defaul way Access works, but I thought that there mau be a solution to prevent data to be autoseved wven changing focus from form to subform. So this issue remains.
Same thing is happening when control is passed from sub to form. The data entered in the sub is saved on the Junction table.
Clicking the half redundant save button will save data, but it doesnt clear the fields.
Moreover, noe the New button crashes the normal behavior.
After clicking it, the fields are cleared and (aparently) the form goes to a new record
But all the validations when passin control to subform are messed. The required fields are filled, but the error message pops. No more highlighted fields, also,

Don't bother to modify the form.
Yoy can just explain the steps, if you find a way to prevent the record autosaving.

Thank you

 

[bastanu]

I' m afraid the only way to move between forms and not fire the BeforeUpdate event is to use unbound forms ...
Cheers,

 

[Pat Hartman]

@Romio_1968 Access is a Rapid Application Development (RAD) tool. It does what it does, the way it does it and there are things you can control and things you cannot control. The first thing to understand is that there is a lot of code running in the class module of the form that you didn't write and you can't modify. The form's class module exposes "hooks" as Event procedures. These events "fire" when certain actions happen. You CAN control the save behavior of a form but only as far as it fits logically in a relational database.

In a relational database, the parent record ALWAYS MUST be saved before the child record can be saved. WHY? Because in order to connect the parent and child, the child record MUST contain the PK of the parent as a FK so that we know what parent record it belongs to. Access handles populating the FK (foreign key) by using the master/child link fields you define in the subform control's properties. Since the FK is required for linking, it makes NO sense for the subforms to be allowed to save data first because they will not have a FK value. Access, in the latest version, seems to be enforcing this automatically. In earlier versions, YOU were responsible for raising an error if someone tried to add a record in the subform when no main form record had been added. You used the subform's BeforeInsert event to control this behavior if you understood the form's event model. If you didn't understand the form's event model, you put code in multiple events and even went so far as trying to lock the subform until the parent record had been created.

Now we get to the most important event of all the form level events, the form's BeforeUpdate event. This event is the last one to run before a record gets saved. It always runs if the form is dirty and CANNOT be bypassed. Therefore, this is the ideal event for controlling the record save - aren't those original MS programmers who defined these events wonderful? They gave you the tool you need should you choose to use it.

In the form's BeforeUpdate event, you validate the controls on that form and if everything required is present and the other sanity checks are successful, then you let Access save the record. If you want to cancel the save, you use one statement==

Cancel = True

This does not remove the changed data, it simply tells Access to not save the record and so Access won't save the record no matter what the user does. If the data cannot pass all the validation rules, the save will be cancelled every time the user tries it. In some very rare cases, you would also use

Me.Undo

In order to back out all changes made to the record. But, I do not recommend this since it is really annoying to the user to have you wipe out everything he entered when he just made a tiny mistake that he should have been able to fix. I restrict the Me.Undo to situations where I and just not going to allow the user to save, mostly because he doesn't have add/change/delete permission. But in that case, I would have used the dirty event to trap the typing as soon as the user dirtied the record rather than waiting until he had filled out everything and then zapped him. Form events are wonderful. They give you enormous control when you know how to use them.

Now we get to - What causes Access to save a record? No one pushed my save button but Access is still saving the record. The answer to this is that Access takes protecting your data to the extreme. It is going to protect you from yourself and even if you forget to explicitly save a record, it will save it for you. THIS is one of the reasons your validation code doesn't belong in the save button click event. You want the validation to run REGARDLESS of what prompted the save whether it be the user or Access.

Things that cause Access to automatically save -
Move focus from main form to subform - always saves the main form first if it is dirty
Move focus from subform to mainform - always saves the sub form first if it is dirty
Scroll to new record - always save the current record if it is dirty
Close form - always save the current record if it is dirty
Close application - always save the current record if it is dirty
Possibly other situations but as long as you actually have validation code and it is in the correct event, your code makes the final decision regarding whether a record gets saved.

So, the question becomes, why would you want to save partially complete records because that's what it seems like you are asking for? There are situations where I might want to do this because the subform is a set of data and I don't want the parent to be "complete" until the set of data has been added to the child table. I would add an incomplete flag to the main form record. Then in the BeforeUpdate event of the main form, I would calculate the child records to see if the set is complete. If it is, I would set the incomplete flag to no but otherwise to yes.

Then in the AfterUpdate event of the subform, I would do the same calculation - usually a query but if it is code, put it in a stand alone module so it can be called by both the main form and the subform. You NEVER want to duplicate code like this. The code always updates the incomplete flag in the main form.

 

[Romio_1968]

Pat Hartman, thank you for the extended tutoring. It is helpfull, indeed.
Meantime I have a better understanding of how, when and why the records are saved, and modified the code accordingly.
The user wil now not be able to see the child if the parent requirements are not met. That simplified a lot the code, since I don't have to deal anymore with user's chaotic clicking.
As a rookie, I wil not use the modules from the very begining. I will play with subroutines. Sometimes I am using Messages inside just to check for values or behaviours during developing and testing.
Anyway, your way of thinking is neat and your adivice is golden. The reusable modules are now one of my targets. But beiing a rookie as I stated, for now I am avoiding them because it is still dificult to deal with both errors that comes from the subroutin logic itself and the module specifiic errors on the same time. I am starting to implement modules (already doing this) wherever I have a stable subroutine or behaviour that can be repetitive.

 

[Pat Hartman]

The user wil now not be able to see the child if the parent requirements are not met. That simplified a lot the code, since I don't have to deal anymore with user's chaotic clicking.

That's a way. Not what I suggested and less safe but it is a way.

ALWAYS use error trapping in a procedure. That way, you won't be confused by what procedure threw the error. When a low level procedure encounters an error but doesn't have an error trap, the error is raised up to the procedure that called it until it eventually gets to the highest level of code where there is an error trap. Poor practice and very confusing to you and your users.

Most applications simply don't have that much reusable code. BUT, if you find yourself duplicating code, STOP, do not pass GO and create a separate procedure that you can call from multiple places.