duplicates query help & strategy for update queries with SetWarnings = False

Hello all,

So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to include the occurence #. For example, in a pay types table, if "Discover" is duplicated (and you can't get rid of one due to it potentially being in use), then I'd like to run an update query that would update the 2nd one to: Discover (2). (Yes, I know you should just set the pay type description to NOT allow duplicates, but the original db did allow it, and the import db will not allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import utility: When running a series of update queries to update data from one database to another, I use a docmd.setwarnings false statement before running each update or append query.

The problem is, if a query fails due to data validation rules, other misc. table rules, etc., the "setwarnings = false" command is suppressing the one warning that you actually do want to see, and it's blowing right by that with no messages. If you don't put the setwarnings=false, then the user get's 2 dialogs for every update query, which is not at all desirable since we're talking 50 or so append queries. Does anyone have an alternative? Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that the user can never be warned about the 2 standard messages: Running a query that will change data, and Confirming the appending of records. But...I have to know if a query fails to append any data due to key viloations, or any other reasons.

Any strategy here would be helpful. I don't even think you can trap an error code, because if any data fails to append, it doesn't trigger the On Error events. Any ideas?



Thanks!

Andy

.

Relevant Pages

  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... I was surprised to see duplicate entries in some of the static tables for dropdown selections, so I have to solve this one now. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: Copy Record within Same Table
    ... if you can write a Select query to pull the records you want to duplicate, ... then turn the query into an Append query and append the recordback to ... > has two fields containing values (barcode1 and/or ...
    (microsoft.public.access.modulesdaovba)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ... On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? ... 2nd problem, and this is a biggie, maybe a showstopper for my import utility: When running a series of update queries to update data from one database to another, I use a docmd.setwarnings false statement before running each update or append query. ...
    (comp.databases.ms-access)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I'm calling a function that will set the occurence number. ... If there's a duplicate, ... I used the query wizard to create a "find duplicates" query, now I need to adjust the results of that query to add an occurence counter/number, and I'd be set. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: Identifying Duplicate Records
    ... Indeed, the duplicate records ... Discovering the Find Duplicate query, ... or bring up the dupe records to look at them. ... have a find duplicate records wizard. ...
    (microsoft.public.access.formscoding)