MS Access: How do I populate other fields in other subforms using a form?

0
votes

I have the following tables with fields that are common across them. E.g.

Branch Table

  • Retailer ID (PK)
  • Retailer Name
  • Address 1
  • Address 2

Internal Table

  • Retailer ID (PK)
  • Retailer Name
  • Internal ID

Bank Accounts

  • Retailer ID (PK)
  • Retailer Name
  • Bank Code
  • Bank Account Number

I have created a "master" form using Branch Table and including the forms of Internal Table and Bank Accounts as subforms. My intention is to have a user type in the Retailer ID and Retailer Name fields to prevent repeated manual entries of the same fields. Under the Property Sheets, I have linked the fields using Link Master Fields and Link Child Fields. However, it seems that the form is not performing as intended.

After entering test data for Branch Table (the master), moving on to the fields for Internal Table results in the following error:

"The Microsoft Access database engine cannot find a record in the table with key matching field(s) . (Error 3101) " Explained as such "In a one-to-many relationship, you entered data on the "many" side for which there is no matching record on the "one" side. For example, this error occurs if you join a Customers table and Orders table on a CustomerID field, and then add an order using a CustomerID that does not exist in the Customers table."

Because of this, I can't automatically add another entry for the other tables.

1
databaseformsms-accesssubforms

1 Answers

0
votes

Field Retailer ID of the child tables cannot be PK.

Create another field (Autonumber) in theses tables to be the PK.

Then go to the GUI designer for Database Tools, Relations, and set up referential integrity between Branch table and the two child tables using the Retailer ID fields. These will now, in the child tables, hold the FK to the master table.