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?
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.