Append Records from Related Tables and AutoNumber

[upwardgazing]

I'm using Access 2003 (Access 2000 file format) and I have two tables related one-to-many called tblTempEncounter and tblTempEncounterDetails. I need to move a record from the first table with it's related records from the second table into another pair of tables called tblEncounters and tblEncounterDetails. I've come up with a solution that seems really convulted to me, and it's snagging with multiple users. Will a single append query move data from two related tables into two other related tables and autonumber them? If not, is there a standard way to handle this situation?

Thanks so much for help in advance :-)

Drowning Newbie, aka Patrick Lollis

[Rabbit]

An append query can only append records to one table. That record can be created from multiple tables but it can only append to one table. My question is why the other tables?

[ADezii]

I'm using Access 2003 (Access 2000 file format) and I have two tables related one-to-many called tblTempEncounter and tblTempEncounterDetails. I need to move a record from the first table with it's related records from the second table into another pair of tables called tblEncounters and tblEncounterDetails. I've come up with a solution that seems really convulted to me, and it's snagging with multiple users. Will a single append query move data from two related tables into two other related tables and autonumber them? If not, is there a standard way to handle this situation?

Thanks so much for help in advance :-)

Drowning Newbie, aka Patrick Lollis

It can be done relatively easily with code, but I'm with Rabbit on this one, why?

[upwardgazing]

I built the data entry form on temp tables so that the user would have to go through all the check and balances I have coded into the data entry process before the data made its way into the permanent tables. I can ditch that part of the design if it's a bad idea, I just assumed that moving the data over would be easy.

[JustJim]

I built the data entry form on temp tables so that the user would have to go through all the check and balances I have coded into the data entry process before the data made its way into the permanent tables. I can ditch that part of the design if it's a bad idea, I just assumed that moving the data over would be easy.

A database designer who doesn't trust the users? What's the world coming to?

Hehehehe

Jim

[upwardgazing]

Here's what I've done so far:

1. qryAppendTempEncounter moves temp record to permanent allowing autonumber
2. txtNewEncounterID (invisible) is requeried. It is linked to qryNewEncounterID which searches for records by client, date, program, and location (presumably only one record) and returns the highest encounter number.
3. qryUpdateEncounterID pastes this encounter number into the temp encounter table, which cascades into the temp encounter details table.
4. qryAppendEncounterDetails moves details into the permanent table with the now matching Encounter ID.

Seems convoluted to me, but works with one user. When I go to multiple users, I'm getting craziness. Maybe I also need to ask what the basic checklist is to make sure multiple user interfaces play nice with one another. All I've done so far is paste the database into a shared network drive.

Thanks Again!