save autoID as ID in additional tables

[dscudder]

I am creating an incident database. There are three principal tables. General information about the incident is in the main table. The incident ID, created there, is an auto-number. The two detail tables record information about the one or more patients and employees involved in the incident, which can then be linked to all other data about patients and employees for incident analysis. Both detail tables have many-to-one relationships with the main incident table.

After entering the general incident information, I need to enter information in the form about patient and employee involvement in the incident. How do I get the tables with patient and employee information to retain the incident ID created at the time the general information is entered in the main table, without reentering it. I have done this twice before, years ago, but I cannot remember how I did it. In the intervening years I have not had to work with data entry forms much.

I have read several closely related threads, but I can't quite figure out how they apply. Do I create a calculated field which simply copies the ID from one table to another? Do I put that calculated field in a query, in the data entry form, where? Please point me to the posts that address this sort of issue and help interpret them to this case. I am sure I am overlooking something very simply here but I am stuck. Perhaps there is a sample db which addresses this.

Thanks,

David

 

[boblarson]

If you use a main form for the general incident information and then a SUBFORM on the main form for the other details, you can link the subforms with the main form by use of the subform control's MASTER/CHILD property the subform control's properties.

 

[dscudder]

Thanks. This is what I am trying to do? '... link the subforms with the main form by use of the subform control's MASTER/CHILD property the subform control's properties.' I can't quite figure out how to do that.
David

 

[boblarson]

If you drop the subform on the main form it will automatically come up with the linking options. If you have it there and need to set them, go to the design view of the main form, click on the subform control (the control which displays/holds the subform on the main form) and then in the DATA tab you should see MASTER and CHILD as properties. Click into one of them and an ellipsis (...) appears to the right of it. Click on that and the little dialog comes up where you can select the field from the main form and the field from the subform which should be linked. That is normally the ID field from the main form and the foreign key field in the subform.

 

[dscudder]

Thanks. I have been to that screen. If the ID number is not already present in both tables, it does not seem to work for me. What am I missing? If I have the field name for the ID in both tables does what you suggested automatically enter the Id from the main table in the sub-table?

Sorry to appear so dense.

David

 

[boblarson]

See the attached MDB sample to see how it works.

And here's a screenshot showing the links - the OrderID (PK) in this case is an autonumber in the tblOrders table and a Long Integer (FK) in the Order Details table.

 

[dscudder]

thanks so much. I will try this in the AM
david

 

[dscudder]

Thanks so much. I see how it all fits together. I am not sure I will be successful my first try. I also see that what I feared was an unusual issue is a standard issue. Therefore it should be covered in a good Access text such as the Access Bible. Thanks again.

 

[dscudder]

Thanks again. It worked perfectly the first try.