Prevent saving when switching between subforms

[wisni1rr]

Hi again guys. I have a series of tables linked together with 1:1 relationships to a unique key with referential integrity enabled on the relationships. My data entry form is made up of 7 subforms. Because of the field limitation I cannot house all my entries on one form.

My problem is that when my user switches to another subform access tries to save the record. Access cannot save the record because there are required fields in some of the other subforms. Thus not allowing my user to switch subforms. If I disable referential integrity it creates orphan records.

Is there a way that I can disable the auto-save that access uses between subforms and use a simple save button in its place. This way my user can enter all the data needed then his save and prevent a required field not being filled error.

Or

Can I somehow dynamically update the foreign key? Keep in mind that this is in a new record setting.

Any suggestions would be appriciated.

Furthermore, My Primary key is AutoNumber and my Foreign key is set to LongInteger. I have set both the child and master properties on each subform.

[Mihail]

I post a similar question in this forum but with no answer. (http://bytes.com/topic/access/answer...-update-record)

So I start to think that is not possible to stop the automatic update.

Yes. You can update the foreign key.
Assign to the control you need, as default value, the foreign key's value.

MyControl.DefaultValue = ValueOfForeignKey

Use the DefaultValue property in order to be able to exit from a new record, without troubles, if nothing else is typed.

[George Tekos]

there is the navigation form's panel (typically the buttons on the left). every button is one form. if you press another button in the middle of a form's completion, the form switches and if there is no ristrictions, submits the data(something i didnt want).BUT
if you make the form open by a button, AND open as "dialog"...so it opens the form you choose, in a dialog box, where you have no option to press something else, except for SUBMIT it and EXIT...
so it works perfect!!

[wisni1rr]

Thanks for the thought process guys.

I've decided to make a new form that the user inputs basic information on the clicks a button and saves it. Then a second form opens with the fields from the first form and all other fields I need. Since the basic info entered on the first form is saved it has already asigned a primary key. Now all keys are asigned appropriately and it will not allow orpan records.

[NeoPa]

I don't know if this helps (as I don't understand much of the question), but when moving the focus away from a form in Access the form will never maintain it's dirtied (changed but not saved) state.

It will do one of three things :

1. Save the data automatically (Default).
2. If the Cancel parameter is set to True in the Form_BeforeUpdate() event procedure then the form will not lose focus. The changes will still be there, but the focus will not have moved away.
3. If the changes are all cancelled (either by code in the same event procedure or by the operator pressing [ESC] prior to moving focus away from the form) then the focus will move away but there will be no remaining pending changes.

In no situations will it be possible to move focus away while maintaining any pending changes though.

[wisni1rr]

Thanks, NeoPa!

As always, your post was a brilliant way to explain what I was thinking but using the actual terminology.

Have a Happy New Year to all posters!

[NeoPa]

Thank you. I'm pleased that helped :-)

Happy New Year to you too.