Update FK field in one table by copying PK field in another

[JustLearning44]

In a cemetery database, I have a table BurialsT which has PK field BurialID, (Autonumber) and a field BurialNumber (Long Integer). BurialNumber increments by using DMax because the numbers MUST be consecutive as they have to match the old paper Burials Register. (I’m not sure whether the Autonumber field is really necessary because the BurialNumber field is required and indexed with no duplicates – advice welcome on that too.)
Table OrderItemsT has its own PK OrderItemID and relates to table BurialsT through FK_BurialID.
I need FK_BurialID to update with the BurialID from the Burials table automatically when records are added via a data entry form. I’d like to do this in the simplest way possible please, preferably without using VBA.. Could anyone advise me please?

[NeoPa]

You're right that an AutoNumber is not necessary if you're providing a separate numeric index that can be used as the PK.

To get linked tables to enter the link automatically you should probably use a form with a linked subform. That will ensure the data for the FK is entered automatically.

[JustLearning44]

Thank you for answering so quickly! I’m not sure how that would work – would the subform be a sub of the BurialsDataEntr y form?
The way it is set up I have a form OrdersDataEntry with a subfrm OrderItemsDataE ntry. The user uses these forms to fill in those fields which are common to all orders. Some orders need further data added so I have 3 buttons which open 3 more forms where further information is added. Those forms are BurialsDataEntr y, DisintermentsDa taEntry and MemorialsDataEn try. It works fine except that the FK in OrderItems is not populated. So, are you saying that each of those forms needs a subform to populate the OrderItems table? I don’t understand how it would do that

[NeoPa]

Sorry for the delay getting back to you. This week has been manic at work.

Subforms are form controls which can contain form objects. A form running within a subform behaves differently from the same form running when opened normally. Properties of the subform control determine how the main and sub forms are linked. This linkage ensures the relevant linked field(s) are pre-populated for the FK in the subform.

Using the wizard to create your subform control will make it easier to set up in such a way as to ensure the field(s) are populated automatically. Opening the same form as a separate form will behave quite differently as there is nothing intrinsic to the form itself that enables the field(s) to be populated.

I hope that all makes sense.

[JustLearning44]

Thank you - I think I understand - I'll try to apply what you say to my database and let you know how I get on!

[NeoPa]

Please do. We like it when people get advice that helps them develop and move on to bigger and better things.