Associating new records in a subform with record in parent form

Question

I am working on a purchasing database in MS Access 2010 for a non-profit, and have encountered a problem that I can't seem to solve using other resources.

I have two tables relevant to this discussion, one of purchase orders, and one of line items. Each line item record has a purchase order number (which is actually a text field, as letters are allowed) associated, with 'enforce referential integrity' turned on, one PO to many purchase lines.) In order to edit PO's, I have a form, which I originally created with the wizard, but have modified heavily since then. In the PO editing form is a subform, which displays a datasheet view of the purchase lines table, filtered by the PO that is current in the main form.

Initially, when I created a new record in the subform, it automatically set the linking PO field to the current PO. This way, new purchase line records were assosciated with the current PO. Recently though, the new records are being created with no value in the linking field, and unless I manually select the PO in that column, the record gets filtered out of the view when it is saved.

So the question is, what property controls how new records are initialized in a subform of this type? Is there another problem that is likely to cause this behavior?

Unfortunately I didn't notice this problem right away, so I don't know what changes came in between it working and failing. Furthermore, it seems to be intermittent, working some days and not others (but never changing within one day.) I assume that this just means I'm missing another variable.

Some things that I have tried, based on my research before asking here:

Previously, the 'Link Master Fields' property of the subform was set to the 'ID' field of the parent form. I have now created a text box in the main form that is linked to that field and set it as the master field. To be honest, I don't understand how that would help, but multiple how-to-guides recommended it.

Making the linking field required in the purchase lines table. This prevents the record from disappearing, but doesn't change the initialization.

enforcing referential integrity between the two fields. As I said, it is now enforced, but originally it wasn't. This didn't cause any noticeable change.

Manually saving the record before entering the subform. Similarly saving the subform record before I exit it.

Thanks you kindly for any advice.

Answer 1

I know this post is older, but I just recently ran into this issue and found a solution.

I imagine your database is split. Problems stem when your Access version is greater than the version your database was built in.

Delete the tables from your front end and then relink to them. Reset your forms Master/Child fields and everything should work as normal.

Hope this helps somebody else in the future.

Answer 2

On the subform, make sure both the Link Child Fields and Link Master Fields properties are set to the database fields that the relationship was created on. If the parent form is bound to a record source that includes the key field that the relationship was created on, you shouldn't need to specify a text box or any other control to link on, just the name of the field.