Append Query Error Trapping

Hello,
>
I am using Access 2003. I am having trouble trapping the "can't
append all the records in the append query" error message when
appending data to a query from a table which is linked to an excel
spreadsheet.
>
There are two tables. One is a list of general contacts, and the
other is a list of clubs. The clubs contain members who are within
the contacts table. When I add a list of new club members from the
spreadsheet linked table, using a Command button, it adds them to the
contact table and creates the entry in the linking table so that they
are also listed within the club table. It cycles through the entries
in the spreadsheet and uses the INSERT INTO statement to add the
values to the query which combines the contacts table and the linking
table between contacts and clubs.
>
This work fine. However, I would like to avoid duplicate contacts
being entered into the system. I set up an index on the contact
table, based on first name, surname and email address. I get the
usual append query error now when I try to add duplicates, but I can't
trap the error and bring up my own message. I am using the On Error
statement on the command button (this calls a function which is
working for everything else), and the On Error Event on the Form.
Neither of these intervenes and stops the standard message from
showing. When the standard error message box comes up and asks if I
want to run the query anyway and I choose, "No", I then get the
message from my customised error trapping.
>
I have used the DoCmd.SetWarnings False to remove all messages, but I
would like to avoid this if possible, as I would like to show a
message box at the end which displays the number of records added, and
the number not added. To do this, I imagine I will need to be able to
divert the loop when it is either succesful or not.
>
Any help would be greatly appreciated.
>
Thanks,
>
Franc.
>

>Hello,
>
>I am using Access 2003. I am having trouble trapping the "can't
>append all the records in the append query" error message when
>appending data to a query from a table which is linked to an excel
>spreadsheet.
>
>There are two tables. One is a list of general contacts, and the
>other is a list of clubs. The clubs contain members who are within
>the contacts table. When I add a list of new club members from the
>spreadsheet linked table, using a Command button, it adds them to the
>contact table and creates the entry in the linking table so that they
>are also listed within the club table. It cycles through the entries
>in the spreadsheet and uses the INSERT INTO statement to add the
>values to the query which combines the contacts table and the linking
>table between contacts and clubs.
>
>This work fine. However, I would like to avoid duplicate contacts
>being entered into the system. I set up an index on the contact
>table, based on first name, surname and email address. I get the
>usual append query error now when I try to add duplicates, but I can't
>trap the error and bring up my own message. I am using the On Error
>statement on the command button (this calls a function which is
>working for everything else), and the On Error Event on the Form.
>Neither of these intervenes and stops the standard message from
>showing. When the standard error message box comes up and asks if I
>want to run the query anyway and I choose, "No", I then get the
>message from my customised error trapping.
>
>I have used the DoCmd.SetWarnings False to remove all messages, but I
>would like to avoid this if possible, as I would like to show a
>message box at the end which displays the number of records added, and
>the number not added. To do this, I imagine I will need to be able to
>divert the loop when it is either succesful or not.
>
>Any help would be greatly appreciated.
>
>Thanks,
>
>Franc.

Use Execute rather than RunSQL.
>
Here's a comparsion between the two:
* * Action queries: suppressing dialogs, while knowing results
at:
* *http://allenbrowne.com/ser-60.html
>
If you use Execute without dbFailOnError, it just rejects the duplicates
that cannot be appended and continues.
>
A better solution *might be to design the query so as to avoid attempting to
add the duplicates. Typically this involves either a frustrated outer join
(same as the Unmatched Queryh Wizard creates), or a subquery to skip the
undesired records.
>
--
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.
>
"franc sutherland" <franc.sutherl...@googlemail.comwrote in message
>
news:20afb5d8-25c9-4b52-aa6c-b73e7ef008c0@25g2000hsk.googlegroups.com...
>>>>>>>>

On Mon, 6 Oct 2008 06:11:54 -0700 (PDT), franc sutherland
>
<franc.sutherl...@googlemail.comwrote:
>
If you're using the Execute method to run your append query, be sure
to add the dbFailOnError argument. THEN the error handler will fire if
you're inserting a record that violates a unique index.
>
-Tom.
Microsoft Access MVP
>>>>>>>>