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. 4 7605
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.c omwrote in message
news:20******** *************** ***********@25g 2000hsk.googleg roups.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.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.
On Mon, 6 Oct 2008 06:11:54 -0700 (PDT), franc sutherland
<fr************ **@googlemail.c omwrote:
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.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.
On Oct 6, 2:53*pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
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.c omwrote in message
news:20******** *************** ***********@25g 2000hsk.googleg roups.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.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.
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.
On Oct 6, 2:57*pm, Tom van Stiphout <tom7744.no.s.. .@cox.netwrote:
On Mon, 6 Oct 2008 06:11:54 -0700 (PDT), franc sutherland
<franc.sutherl. ..@googlemail.c omwrote:
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.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.
Thanks Tom,
I did just that and it worked great.
Thanks for your help,
All the best,
Franc. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Charles Ranch |
last post by:
Hello,
I'll bet this is an elementary question. I have a text file I am
converting and posting to a SQL database. But the first 15 records in
this text file are going into a NOTES field in the SQL db and I want to
append all of these together before I post to the database. In other
words, I don't want to create 15 records, just one. How do I append the
records and then write the appended record to the database? I can look
at it...
|
by: Paul Wagstaff |
last post by:
Hi there
I have 2 tables: tblAccuracy & tblClearance
Users add new records to tblAccuracy using frmRegister. Under specific
conditions I need to append the current record from frmRegister into
tblClearance. I was thinking of placing the code on the form's BeforeUpdate
event so that it will fire whether the user closes the form or attempts to
create another record.
|
by: Aaron |
last post by:
Hello fellow programmers,
I am trying to run an append/update query from code, a command button
on a form initiates the queries.
the format i am using is;
_____________________________________________________
SELECT "criteria"
|
by: Larry Rekow |
last post by:
As part of a macro, I'm trying to automate appending a table with new
records.
let's say the table 2 has some new records in it, but also has a lot
of identical records to table 1.
I would like to append table 1 with all of the new records, and do it
unattended in a macro.
Is there a way to specify in the append query to only append the
|
by: Thelma Lubkin |
last post by:
I use code extensively; I probably overuse it. But I've been using
error trapping very sparingly, and now I've been trapped by that.
A form that works for me on the system I'm using, apparently runs into
problems on the system where it will actually be used, and since I
used so little error-trapping it dies very ungracefully.
I will of course try to fix whatever is causing the error and add
error-trapping to the functions where the...
| |
by: Captain Nemo |
last post by:
I'm still using Office 2000 myself, but some of my clients have Office 2003.
I've recently added a piece of code to create an instance of Word, open a
document, fill in the blanks and become visible so the document can be
printed and/or modified.
This all takes place within one form, in which the Word.Application and
Word.Document objects are both private form-level variables. Just to be on
the safe side I included this piece of code in...
|
by: robertmeyer1 |
last post by:
Hey,
I have 2 append queries. The first is based off of code given and works great. For the second, I followed the format of the 1st and adjusted it to meet my needs. Query 1 works great. It appends each specific clientId to the table when the command button from form 1 is selected (code below). The 2nd query works as well. But it will only append 1 employee record to the table. I thought this must be because it is only allowing for 1...
|
by: Matt |
last post by:
Hi All,
Is there a way to get the information in this message through VBA
code?
I run the query from code, but it does not throw an error, so my
error
trapping code will not catch it. I would like to somehow know when
this happens.
|
by: MeeMee |
last post by:
Hi
I have a problem appending data into an oracle table from access. I imported the new data from an excel sheet into a table in access and useed an append query to add the data into a linked oracle table. The linked table has a composit primary key (Code, Org). The table in access has new data that are not in the oracle table and the data in the PK fields are not duplicated. I also made sure that both tables have same data types.
I...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |