Search code examples
formsms-accessrelationships

Access 2010 - Multiple one-to-many tables


The situation I am dealing with seems pretty basic but I can't make it work...

Scenario:

One company has many branches and each branch has many addresses as well as many contacts: Tables relationship

I am trying to design several data-entry forms to fill information in three tables:

Companies, CompanyBranches, Addresses (excluding contacts by now to reduce complexity a bit). I have a cascade of several forms where the Company Details is called by clicking on the CompID, Branch Details is called by clicking on the BranchID etc.:

First - Companies List

Second - Company Details

Third - Branch Details

Fourth - Address

The problem is to add or modify records. When I click on the Add Branch button in the Company Details form, the Branch Details form appears with blank fields where I can enter the name of the new branch (BranchAddress subform is empty at the moment). But when I close the form after entering the branch name I get an error:

You cannot add or change a record because a related record is required in table 'Companies'.

Same thing when I want to add an additional address for a branch. What do I do wrong?

P.S. When I remove relation between CompanyBranches and Addresses tables then I can add branches with no errors.

P.P.S. I realized that I probably need to add a macro that is used on click event on the Add Branch button:

On Click event Macro


Solution

  • Finally I figured out what was wrong with my forms.

    First of all, I had to add CompID to the Branch Details form, as was suggested in this thread, and secondly make a bond between the Company Details form and the Branch Details form:

    In the Branch Details form, in the properties of the CompID text box under Data tab, in the Default Value row I added a link to the CompID value that is in the Company Details form: =[Forms]![Company Details]![CompID]. Now these two forms are linked by the CompID value, and this value will be written from the Companies table to the corresponding record in the CompanyBranches table at the moment of entering information in the Branch Details form.

    Hope my explanation is not even more confusing then my question... Anyway, thank you to all who participated in solving this issue.

    P.S. Same thing with Address Details form. It must have the BranchID in it and a link to the Branch Details form as the default value of the BranchID: =[Forms]![Branch Details]![BranchID].

    enter image description here