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

Hi Allen,

This question may have been lost in the shuffle:

I was surprised to see duplicate entries in some of the static tables for
dropdown selections, so I have to solve this one now. Any ideas on the other
part of my question? If there's a duplicate, I want to rename the
description with a occurence number after the description. Such as "Federal
Express (1)" and "Federal Express (2)", etc.

P.S. 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. Just not quite sure how to do that.

Thanks!

Andy
"ARC" <acolonna35@xxxxxxxxxxx> wrote in message news:_nPIi.28100$eY.1116@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Allen,

That's perfect! Exactly what I was looking for, to be able to trap errors on the append, and not warn with the other 2 boxes!

I dont' have to worry much about a transaction. I'm having the user select the source and destination, and if the destination is present and contains data, I'm giving an overwrite / backup dialog. So each time they try, they'll get a blank database guaranteed.

I've done pretty much what you've said. 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. Or if a part category is missing in the parts table, I'm creating a category callled "Un-categorized", and setting any parts missing cat's to this category. Or if the data is line-items that link to parts, if the partID isn't there, I again put up a form so they can quickly replace missing data. For things like customer call logs, or customer reminders, I'm doing a join with the customer table, so it will only bring in the matched data. For critical financial items, I'm putting up forms so they can fix unmatched data.

For mainly static tables such as business source, customer type, etc., since I'm not allowing blanks anymore, I'm just doing an IIF(isnull(..., etc. where I'll set the description to "Not entered" if it's blank.

I was surprised to see duplicate entries in some of the static tables for dropdown selections, so I have to solve this one now. Any ideas on the other part of my question? If there's a duplicate, I want to rename the description with a occurence number after the description. Such as "Federal Express (1)" and "Federal Express (2)", etc.

Thanks!

Andy
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message news:46f3612c$0$28173$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
The best way do do imports might be to sort out the problems before the data appended to the real table. You create a table with all *Text* fields (so you get no data mismatch errors), no validation rules, no relationships to other tables, and an AutoNumber primary key field last in the table (so the data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes any data in the temp table (from previous imports), and performs the TransferText into the temp table. Your code then runs a series of tests for everything that could go wrong: zero-length text fields, wrong data type, bad dates, values that don't match anything in foreign key fields, and so on. You flag these records and load them into a list box or the form itself (typically in Continuous view), so the user can fix up these situations.

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(s). If any error occurs during this step (typically something you forgot to check for), you can trap this error by running using the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final table, so you probably want to wrap that operation in a transaction so you can roll the whole thing back. For an example of using a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <acolonna35@xxxxxxxxxxx> wrote in message
news:8cIIi.51180$Um6.6515@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.