I’m here to help
- Oct 29, 2018
Okay, here's the situation we have, if I understand it correctly.No
Let's say the main form is bound to tblParent and the subform is bound to tblChild, and let's say both tables are currently empty.
So, you're basically saying you don't want the user to enter a child record without entering a parent first. This makes sense because if we allow a child record without a parent, then we'll have an orphan record. These orphan records won't show up in the subforms, even if they exist in the table, because the link fields in the mainform/subform setup will eliminate them from being display due to a missing foreign key to tblParent.
So, to enter the first child record in tblChild (subform), we'll need the first record in tblParent (mainform). Let's say the ID for the parent record is 1. Now, when we select a location in the subform, the foreign key for that first record will also be a 1, which matches the ID from tblParent (mainform). Hope you're following so far.
Now, the problem is you said you didn't want tblParent to have a record with ID=1 if there is no matching FK=1 in tblChild. But as you can see, you can't have a record in tblChild with FK=1 if there's no record in tblParent with ID=1.
So, the only way we can do this is if you let the user enter a record on the main form (ID=1) but delete it if the user doesn't then enter a record in the subform. However, you can only trap that event either in the Close event of the form or its Current event. The Close event will catch the situation I asked you in Question #1 above, and the Current event can catch the situation I asked you in Question #2 above.
Either that, or like I said, use unbound forms or forms bound to temporary tables.
Hope it makes sense...