Form / Subform Autosave and Events sequence

[kdw]

I have a Main form with 3 tabs. Tab1 contains fields that's part of the Main
form, and Tab2 and Tab3 contain 2 different subforms. The Main form contains
fields for entering employee info and Tab2 and Tab3 let the user enter
actvities.

I have 2 problems, which may or may not be related:

1. One of the fields on Tab1 is a lookup combo box that lets the user pick
the ProgramType the employee belongs to. It's not required. but if I don't
pick a value and I click on Tab2 or Tab3, I get the error "You can not add or
change a record because a related record is required in the table
tblProgramType." I understand that when I move off the Main form (by going
to Tab2 or Tab3) that Access automatically saves the record, but this error
confuses me. If I make a combobox selection, then everything is fine. Why?

2. Related to the #1, I tried to have a FormCheck procedure that check for
valid entries before letting the record be saved (for example, when I move
off the Main form into the subform). If I am going to get the above error, I
want to put in a friendlier message to tell the user to pick a value. but I
can't seem to find the right Event to put the code in. I have tried
BeforeUpdate, LostFocus. Subform OnEnter is too late. Any suggestions?

Thank you!

 

[Wayne Morgan]

I suspect the error is because you have a link between the two table on this
field. If you don't make an entry, then the value is Null and there is no
Null value in the lookup table. You may need to try modifying the link to
include all records from tblProgramType and only those records from the
lookup table that match.

 

[kdw]

Thanks Wayne for the pointer. I suppose i can force a default value for the
combo box. But what do you mean "include all records from the
tblProgramType"? tblProgramType is the lookup table.

Other than having a default value for the combo, I want to check for the
value in the combo and ask and user to pick one if they haven't. What Event
can I put the check in? Form_BeforeUpdate (on Main form) doesn't work when I
try to switch to the subform.

 

[Wayne Morgan]

Form_BeforeUpdate will only work if the record is "dirty"; otherwise, there
is no update to do. You could check in the tab control's Change event. This
event fires when you move from one page in the tab control to another.

The option I mentioned to "include all records..." would be in the link
between the two tables. This may be in the relationships window or in the
query feeding the form. If a query, right click the link line and choose
Join Properties. You'll see the option listed then. If in the Relationships
window, right click the link and choose Edit Relationship... then click the
Join Type button.

 

[kdw]

The Form_BeforeUpdate is not being called at all even when I entered some
info in the fields in the Main form and then move to the Subform( which I
believe should trigger an autosave and thus the BeforeUpdate?).

What is the event that happens BEFORE the main form get save and BEFORE the
focus is on the subform? I want to call the FormCheck procedure for valid
input but don't know when to put the call.

 

[Wayne Morgan]

It sounds as if you want the parent form's BeforeUpdate event. I just tried
placing a tab control on a form, putting the parent form's controls on one
page and the subform on another page of the tab control. I changed one of
the fields in the parent form and when I clicked on the page tab to go to
the subform page, the parent's BeforeUpdate DID fire. You say that you have
the code for the BeforeUpdate event, but do you have [Event Procedure] in
the On Before Update line of the form's Properties sheet, Event tab?

One thing to be aware of, you will have to make a change to a field. Simply
providing a DefaultValue for a new record will NOT make the record dirty.
You must also, manually or through code, make a change to at least one of
the fields in the record.

 

[kdw]

That did it! I must have taken the [Event Procedure] part out of the form
somewhere during this mess. Thank you Wayne for walking this through with me.

It sounds as if you want the parent form's BeforeUpdate event. I just tried
placing a tab control on a form, putting the parent form's controls on one
page and the subform on another page of the tab control. I changed one of
the fields in the parent form and when I clicked on the page tab to go to
the subform page, the parent's BeforeUpdate DID fire. You say that you have
the code for the BeforeUpdate event, but do you have [Event Procedure] in
the On Before Update line of the form's Properties sheet, Event tab?

One thing to be aware of, you will have to make a change to a field. Simply
providing a DefaultValue for a new record will NOT make the record dirty.
You must also, manually or through code, make a change to at least one of
the fields in the record.

--
Wayne Morgan
MS Access MVP

The Form_BeforeUpdate is not being called at all even when I entered some
info in the fields in the Main form and then move to the Subform( which I
believe should trigger an autosave and thus the BeforeUpdate?).

What is the event that happens BEFORE the main form get save and BEFORE
the
focus is on the subform? I want to call the FormCheck procedure for valid
input but don't know when to put the call.