I have an append query that selects a number of records from a temp table to insert into a permanent table. As part of one of this query's columns it calls a module function which gets a unique 'barcode' value for each record. As part of this function it creates the 'barcode' but then checks to see if that barcode already exists - if it does exist then I want to halt the whole query and rollback any previous records inserted as part of the query that is being run. Can anyone help?
how do I rollback an append query?
- Thread starter rodneyb
- Start date
Rodney,
Is this a trick question?
I ask because, ostensibly, the solution seems to be to re-arrange the chronology of your code.
1. Create a barcode.
2. Check to see if it's a dup.
3. If it is, go back and try again.
Do this for as many records as there are to append.
Regards,
Tim
Is this a trick question?
I ask because, ostensibly, the solution seems to be to re-arrange the chronology of your code.
1. Create a barcode.
2. Check to see if it's a dup.
3. If it is, go back and try again.
Do this for as many records as there are to append.
Regards,
Tim
If I read your post correctly, you want to eliminate all new records if there are duplicates, not just the duplicated ones, so...
Step 1: If you want to guarantee the barcode value in your table to be unique, ensure that you force this by creating a unique index for it in table design view.
Step 2: Ensure that you have NOT disabled confirmation messages.
Step 3: Create a new Yes/No field in your table (lets call it [JustAppended]) and ensure that the values for this field are set to TRUE when first appended.
Step 4: When you run your append query and there is at least one duplicate barcode value in the records you are trying to append, you will get an error message stating that Access was unable to add some records due to key violations. This is your flag that there was some duplicates.
Step 5A: If no such prompt happens, use an update query to set the values of [JustAppended] for all records to FALSE.
Step 5B: If you get this prompt, delete all records with [JustAppended] = TRUE to restore your table to its starting point.
HTH
Step 1: If you want to guarantee the barcode value in your table to be unique, ensure that you force this by creating a unique index for it in table design view.
Step 2: Ensure that you have NOT disabled confirmation messages.
Step 3: Create a new Yes/No field in your table (lets call it [JustAppended]) and ensure that the values for this field are set to TRUE when first appended.
Step 4: When you run your append query and there is at least one duplicate barcode value in the records you are trying to append, you will get an error message stating that Access was unable to add some records due to key violations. This is your flag that there was some duplicates.
Step 5A: If no such prompt happens, use an update query to set the values of [JustAppended] for all records to FALSE.
Step 5B: If you get this prompt, delete all records with [JustAppended] = TRUE to restore your table to its starting point.
HTH
Similar threads
- Deleted Bruce 182381
- Queries
