Shall Stop Orphan Records in SubForm!

[cap.zadi]

Hi

The main form record set moves to next without having data in the Subform fields even though clicked for Required "Yes"



how to prevent the main form to go new record until subform fields are filled up?

[Micron]

More than one way. Assuming you're filling in the main form first, when a key field exists on both forms and data is entered in that field of the main form, the AfterUpdate event of the control could populate a related subform field. If fields are set to 'required' at the table level, Access will complain when you try to move off of the record using main form navigation. You can also put validation messages at the table level. This method requires table design modifications for even simple things like changing the message, so it's probably not a popular method but it may be the only one that will prevent Access from moving off the record without a bunch of customization.

You can also use form level validation by checking that the subform controls have values when the main form does, in the main form BeforeUpdate event. However, I can't recall if you can prevent navigation using the built in navigation controls and the event may fire if the user clicks on the subform as the main form will lose the focus. They'd get an annoying message when attempting to do exactly what is supposed to be done. I know that one way is to remove the built in nav controls and substitute your own. Then you can check for required fields and prevent a record move.

The situation you're describing isn't really a flaw if that's what some think (at least I think not) as the notion of creating a main record without child records isn't always a bad thing. Having a main entity (such as a customer) without related records wouldn't always violate data integrity rules.

[cap.zadi]

Hi

Thanks for the such a wonderful explanation. Thanks

[Micron]

np. hope it helps.
Nice when somebody is appreciative.

[cap.zadi]

My Pleasure.

Thanks