Adding same values to multiple tables at once

[JFo]

Quite a general question here for any forms... what is the best way to add a record to two tables at the same time?

Say for example you had a tblLocation table that had a foreign key that is CityID... what is best way to add a city and therefore add the CityID to both the tblLocation and tblCity at the same time using one form (lets call it frmLocation) just by clicking a single command button?

[June7]

So during data entry you discover the city is not in a combobox list and want to add the new city record and also finish the data entry record? Check out the combobox NotInList property: http://www.fontstuff.com/access/acctut20.htm

[JFo]

yes this looks to be one of the things I was looking for, I will try and have a play with the code from the examples and see if I can get it to do what I want it to do

Thanks

[JFo]

Ok that is great for combos and not really what I was after but works a treat and I will be using it for sure

what I need though is say you have a many to many relationship lets say a relationship like

Inspections <--> Equipment

where
1 inspection can have many pieces of equipment
and
1 piece of equipment can be in many inspections

Therefore you need a junction table when entering each piece of equipment for any given inspection.

The way I have designed this so far is to start an inspection on a form that has a command button that adds another piece of equipment to the inspection by equipment type therefore I need the Inspection ID and the Equipment ID to not only add to their respective tables but also need them to add to the junction table for each piece of equipment

Let me know if you need any more info

Thanks

[June7]

Not sure I understand. If tables are properly related and form structured correctly, no need for code to add records, unless it is a new piece of equipment not already in the equipment source table and that's where the combobox NotInList event plays a role. You need to use form/subform arrangement. Main form bound to the inspection table and subform bound to the junction table where you will select equipment related to the inspection. The subform container control will synchronize records of the two forms with Master/Child link properties. No button to add equipment to the junction table is needed.

[JFo]

Thanks June7 I don't think I am explaining myself properly. The not in list property is great and I will be using it for different areas including equipment type you are correct.

What I am trying to do is when I start entering data for a new inspection I will select from the equipment list this part is fine and not in list can be used here. So already I have the InspectionID which goes into inspection table. I then choose to add a piece of equipment to the inspection. Therefore I select the equipment type from the drop down list and so the EquipmentTypeID must also go into the Equipment table and the new EquipmentID must also go into the new equipment table as this is a different piece of equipment even though it is the same type. This is all OK however I need a way to link the inspection to the Equipment and due to the many to many relationship I need to enter the EquipmentID and the InspectionID in the junction table

This will need to be completed for every piece of equipment in the inspection, furthermore the different pieces of equipment may be involved in other inspections as well

Thanks

[JFo]

So I need a way to generate the new InspectionID and EquipmentID for a new inspection and piece of equipment in their respective tables and insert them into the EquipmentInspectionJunction table for each piece of equipment per inspection

[June7]

Are the ID's autonumber datatype? My previous comments unchanged. Use form/subform with Master/Child linking.

[JFo]

Yes they are Autonumber...

I think I follow that logic I could have a hidden subform with the junction values and pass from main form to subform and make mainform bound to Equipment table and subform bound to junction table. Is this right?

thanks

[June7]

The subform would not be hidden, it would be where you enter records relating equipment to inspection in the junction table.

Main form bound to Inspection table, subform bound to junction table, Master/Child link on Inspection keys, combobox on subform to select equipment. Linking on the Inspection keys will cause Inspection foreign key in junction table to populate as soon as an equipment is selected. Build it. Try it.

[JFo]

OK I will try, thanks