Eliminate Duplicate Error Message with Append Query

[bostonfrog]

Using a table to backup data created by two different users for patients (tblPatients). Will automate this to allow the admin. in dept. to simply press a button and append all records to a backup table of Patients (tblPatients_Backup). Because there is a primary key (PatientID) in both the backup table and the ones being appended from, I don't want him to get a key violation error that says there are duplicate records and that a certain # of records won't be appended. To avoid him getting this error, In the append query, I am attempting to build a criteria in the PatientID field ([tblPatients]!PatientID <> [tblBackup]!PatientID). It often pops up a parameter dialog, which I can't fill in, and says 0 records appended. Also, why does my query appear at the top of the query build button dialog and not in the queries folder, and that no fields from it display, even after saving it?
Thanks.

 

[SgtJarrow]

Just build the append query as you like, the call is from vb code like this:

DoCMd.SetWarnings False
DoCmd.openQuery &quot;queryname&quot;
DoCmd.Setwarnings True

This will simply run the query and hide the pesky message box....anything duplicated is merely not added but everything else is.

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[bostonfrog]

Thanks for your response above, mstrmage1768. I do understand how to use this VB code, but I don't know how to build the append query. It's not working by itself. I've even tried writing it in SQL and then seeing how it looks in the query grid, but it does not work. I can't get the code right in the criteria row to NOT append records that already exist in the source table! My issue's with the append query structure, not the VBA.

 

[SgtJarrow]

If you build the query to include all the fields from your source table, and append to an identical table, there should be no problem....as long as the destination table also has the same primary key as the source. It is the existence of the primary key in the destination table that does not allow the append query to add the record to it.

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[bostonfrog]

I thought that by adding a criterion in the field that limits the appended record by stating: WHERE field1.sourceTable <> field1.destinationTable, it would only append those records that weren't already in the destination table. Can this not be done because I set primary keys on them already? I am trying to do this so he won't get a primary key violation error, and that only NEW records with different IDs get appended. Oh well, maybe not possible.

 

[SgtJarrow]

Again....let access handle the primary key violations....if you just mask the message saying there are violations with the DoCmd.SetWarnings I previously mentioned, all NEW ids will be added and all OLD ids will not be appended again...

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[bostonfrog]

Ok, thanks. I will try to build a form like that one, let the warnings be turned off, and see if appends just those that records that are new. This might in fact work. I just thought it could be done in query by setting this criterion.