Create new record from linked sources

[Snufflz]

I would very much appreciate some help with a request that has arisen on a database I have created.

The database is to list clients who have been involved in car accidents. At the moment the vehicle owner is entered into the database (table:all_records) and the passengers in the vehicle are entered into a separate table (tableassengers). These are linked through the client ID and all is well.

I have now been asked if it is possible to create a new client from each passenger record. That would mean copying the passenger data into the client fields and copying the rest of the accident/vehicle details from the original client record.

I can do quite a bit with Access but this one has me stumped.

Many thanks.

 

[JamesMcS]

How about a query to append the passengers into the all_records table, then an update query to populate the other all_records fields, still linking with client_ID?

 

[DCrake]

Why did you not have them all in one table in the first place and simply have a field that denoted whether the person was the driver or passenger.

 

[sphere_monk]

Assuming you have a form with the client info and a subform (multiple record) listing passengers, you could place a command button on the detail line of the subform (it would show multiple times, once for each passenger). When the user clicks the command button, fire off a query that uses the passenger info (it knows the passenger because you clicked on the button on that passenger's line), and info from the client record (on the master form) to append a record to the client table.

That's one way you could do it.

 

[Snufflz]

That's a thought. It has to be something the users can do with the press of a button which is why I was thinking of the macro/code route.

Thank you for your suggestion.

 

[Snufflz]

Thank you sphere_monk, I will give that a go. I was perhaps trying to over-complicate things.

Much appreciated.

 

[Snufflz]

Assuming you have a form with the client info and a subform (multiple record) listing passengers, you could place a command button on the detail line of the subform (it would show multiple times, once for each passenger). When the user clicks the command button, fire off a query that uses the passenger info (it knows the passenger because you clicked on the button on that passenger's line), and info from the client record (on the master form) to append a record to the client table.

That's one way you could do it.

Sorry to be a pain but I've created an append query to take information from both tables and that works OK. The problem is that the button on the passenger subform wants to append all the passengers and not each individual one.

Not every passenger will become a client and the users need to select each one at a time to make a new client record.

Do you think I've done something wrong?

Regards.