Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: Form for multiple tables with relationships

    OK I am at a loss with this one

    I have a form that is based off of a query that is using information from four tables that have a relationship. If I enter inforamtion into my "employees" table it is fine. If I try to enter info into a field that is from any of the other tables I get this error.

    You can not add or change a recorde because a related record is required in table "employees".

    If I go directly to the tables I can enter the data as long as I enter the employeeID which is the foreign primary key that establishes the relationship. I also can not see the record in the query unless their is information relatin to the employee in each table. Some employees will not have any "training" to enter into the "training" table for a few weeks after employment. Why can I not enter into all tables via one form?

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    Typically when you are creating a form, you only want to have it associated with one table. So if you are editting Employees, the Employee form is linked to the Employee table.

    To edit data related to Employees in separate tables (with a one to many relationship) you would create a Subform for the many side of the relationship and put it in the one side form. A classic example is Order and OrderDetails. You have an order with an order date, who entered the order, etc. then a subform with the items that were ordered.

    When you have a subform on a main form Access will ask you how the two are linked (related). Then with an appropriate link Access does the updating of the many side with the ID from the one side. So in the Order example, when you add order detail, Access will 'enter' the Order ID into the Order Details table.

  3. #3
    Join Date
    Mar 2004
    without seeing your query or table relationships it's difficult to give a good answer, but i do think the subform method might be the way forward.

    you can then have another form open on a command button or IF clause on the main form, allowing you to add or edit on all three forms.

    works for me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts