Updating 2 tables from 1 form

[bibbyd01]

I have a form that I want to use to update two tables with the same information.

I have a vehicle and driver table, that are linked by the registration, which is the primary key on the vehicle table. When a new record is entered, I want it to add the new registration to the vehicle table and the driver table. How do I set it to do this?

 

[jzwp22]

I'm not sure what your tables and relationships look like, but I would guess that a person can have many registered vehicles and a vehicle can have many drivers associated with it (many-to-many relationship).

tblVehicles
-pkVehicleID primary key, autonumber
-RegistrationNumber

tblDrivers
-pkDriverID primary key, autonumber
-txtFirstName
-txLastName

tblVehicleDrivers
-pkVehicleDriverID primary key, autonumber
-fkVehicleID foreign key to tblVehicles
-fkDriverID foreign key to tblDrivers

I would base your main form on tblVehicles and then have a subform based on tblVehicleDrivers. In that subform, you would have a combo box to select the driver(s) of that vehicle.

 

[The_Doc_Man]

First things first: Language can be a slippery little devil, causing misunderstandings. So if I misunderstood your question, please clarify.

Second, the ONLY time that you would ever want the same data having the same meaning in two different tables is if the common data happens to be a primary key in one of them and a foreign key in the other. If that is in fact the case, which I think it is from my re-read of the problem, drive that form off the JOIN of the two tables.

However, what bothers me is that unless you have a restriction that I don't know about, it would seem that drivers could have more than one vehicle registered, but the above assumes only one vehicle per driver and one driver per vehicle. If so, then the above is NOT the solution because in THAT case, your description makes me think your tables would be denormalized.