Form Display Data from Two Tables.

[yu]

I have a main table and another table contain employee's medical information.

main table Medical information
------------ -------------------
Name | | Name
address | | First visit date
zipcode | | Last visit date
status | | ''
'' | | ''
'' | | ''
------------ |---------------- ^^
|
|
----------------------------
| Name Field
| Address zipcode
Form | Last visit date
| First visit date
| ''
|-------------------------------
I create a Fom base on the medical information table, and I want to retrieve employee address from the main table to display on the Form whenever I add a new record to the medical Information. So users don't have to type in their address and zipcode, but only need to enter "first visit date" and "last visit date" ..etc...

I did create a querry to do it on the Form. It allow me to view data from both table, But it doesn't allow me to edit the fieds, I get the error message
" the recordset is not updatable "

Any Suggestion would be greatly appreciated.

 

[raskew]

Suspect your table structure and relationships may be part of the problem.

The Employees table in the Northwind database can serve as an example. Note that each employee has a unique EmployeeID (Autonumber-key field), and that no employee is duplicated.

Look at the relationships window to see how the Employees table is related to the Orders table in a one to many relationship based on the EmployeeID. This would correspond to your Employee/Medical Information situation.

Given this relationship, you can create an updatable query (the fields that are bound to another table will not be updatable).

Re your form: Medical information, standing alone, isn't of much use unless it's linked to a particular individual. Therefore, base your form on the Employee table, with a subform containing the medical information. Provided you setup the relationship described above, the forms wizard will build this for you--first select fields from the employees table, then desired fields from the Medical Information table.