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.SetWarnin gs 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.
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.SetWarnin gs 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.
Comment