Append Query Error Trapping

[franc sutherland]

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.

[Allen Browne]

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" <fr**************@googlemail.comwrote in message
news:20**********************************@25g2000h sk.googlegroups.com...

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.

[Tom van Stiphout]

On Mon, 6 Oct 2008 06:11:54 -0700 (PDT), franc sutherland
<fr**************@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

>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.

[franc sutherland]

On Oct 6, 2:53*pm, "Allen Browne" <[e-mail]:

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" <[e-mail] in message

news:20**********************************@25g2000h sk.googlegroups.com...

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.

Hi Allen,

Thanks for that fix. It worked a treat. And what a great website!

I have read your section on sub-queries and had that Eureka moment.
I'm going to try to incorporate this into my module now so that
existing contacts which are already in the system but which are listed
in a club's membership in the linked spreadsheet will be added to the
club members table using their existing contact id.

All the best,

Franc.

[franc sutherland]

On Oct 6, 2:57*pm, Tom van Stiphout <[e-mail]:

On Mon, 6 Oct 2008 06:11:54 -0700 (PDT), franc sutherland

<[e-mail]:

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

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.

Thanks Tom,

I did just that and it worked great.

Thanks for your help,

All the best,

Franc.