If I’m reading this correctly, you’re saying you have the main parent table as the main part of the form, and you want the child records display in a sub form as you navigate around. Access works this way by default and you’ll not have to write any code at all.
And furthermore if you add child records to the sub form, the foreign key value will be set correctly for you. Again no code is needed.
So, build a standard form based on the main parent table. Then you can build a continuous form Based on the child table and drop that into the above main form.
You then open up the main form in desing mode and setup the sub form property sheet. In that property sheet simply set the link master and link child fields. The link master will be set to the primary key in the main form, and the link child is to be set = the foreign key field from the table in the sub-form.
Once the above is done, then the whole thing should work without any coding and all.
As for the child adding error message:
Assuming you have a main customer form, then the user can’t add a order until they navigate to a new customer record. If they don’t navigate to a new customer record then they will be editing an existing customer in this main form. So in all cases a main customer record will always be added before they can even enter a customer order.
I suppose a user could navigate to a blank customer record, then jump the cursor (focus) into the sub form where the orders are and start typing. Even in this scenario a blank customer record will be added when the focus moves from the main costumer form to the orders sub form. ( So the access UI handles the adding of the parent record when you’re using a classic main form + a sub form setup)
So from a user interface point of view, it’s not really going to be possible for the user to enter orders without a main customer record existing already. Because of this, you’re not going to get an error message about the parent customer record not existing. You’re only going to get this kind the error if the person opens up the orders form as a non sub form, and then tries to add a new order record. In this case you will get an get an error message. Therefore I really don’t see the need to trap this error message. I would not allow the user to open up the sub-form as stand alone. If for some strange reason you do allow the sub-form to be opened up separate from the main form, then open the form with allows additons = false (and again your prevent the need for the error message).
I suppose if for some The reasons users are allowed to open that’s sub form or the orders form separately and you’ve not turned off the allow additions, then they would be able to navigate to a new blank record, and that means the FK to the customer record would not be set correctly. If there’s provisions in this orders form to select a customer, then you could most certainly put the following code in the before update event of this orders form:
If isnull( foreign key field name goes here) = true then
Msgbox “ you must select a customer before you can enter an order”
Cancel = true
End if
Once again in a sense were not really trapping the error message that a child record is about to be added without the parent record existing. We are simply using the UI interface to tell the user to do some action that will prevent that error message from ever occurring in the first place.