I am having a problem with linking a sub form to a master form in Access 2007 as follows:
• TableA consisting of an Autonumber primary key field and a Text field.
• TableB consisting of an Autonumber primary key field, a Number field and a Text field
• A one-to-many relationship from the TableA Autonumber field (one side) to the Number field of TableB (many side)
• A MultiItems sub form with its RecordSource set to TableB and showing all the fields of TableB.
• A normal form with its RecordSource set to TableA and showing all the fields of TableA. Additionally the form also has a sub-form control with its SourceObject set to the MultiItems sub form. The LinkMasterFields is set to the TableA Autonumber field, and the LinkChildFields is set to the TableB Number field.

The normal form works correctly when run in Form View apart from one problem:
• A new TableA record is generated by clicking the main navigator appropriately.
• Text is entered in the Text field of the TableB sub form.
• The Text field of TableA is clicked. At this point the Autonumber field of TableA is still showing (New), and the text entered in the Text field of TableB remains unchanged.
• Text is entered into the Text field of TableA. Immediately, the Autonumber field of TableA shows a number, and the text entered in the Text field of TableB disappears.

This means that the user must enter text in the TableA Text field before entering text in the TableB text field. If not, the text entered in the TableB Text field must be re-entered after entering text in TableA Text field, an undesirable situation.

I have tried to force TableA to update when entering the subform control to get the TableA Autonumber field to have a valid number, but to no avail. I have tried to stop entry into the subform control with a suitable message to the user when there is no valid value in the TableA Autonumber field, again to no avail. And I have tried to manage the linking manually by providing suitable code and leaving the LinkMasterFields and the LinkChildFields blank. This also did not work.

I would be grateful for any comments and possible solutions to this problem.