-----Original Message-----
Hi Rob,
You cannot update existing records with an append query,
you would need an update query. There are many ways to
approach this. One way would be to first check to see if
the record already exists in the QA database, and if it
does, you could run your append query and if not you
could run the update query. Another way would be to run
both an update query and append query, but set the
criteria for the append query such that it will only
append a record if it does not already exist in the
table. Both are discussed below in a little more detail.
I'll describe the second case first, because it would
probably be easiest if you aren't familiar with VB. You
could run both an update and append query in sequence
using a macro, but use the Dlookup or a subquery to limit
the append query to only append if no matching record
already exists in the QA table (a subquery would probably
be better). If using a subquery, it would be a
concatenated string in the criteria line beneath ID
similar to:
Not In (SELECT [SalesID] FROM [tblQA])
Of course you would only need the []'s if the field name
or table name contain spaces, and the criteria would be
in addition to the criteria you already have to limit the
append to the current record in the form (use AND to join
the two conditions). If you use this method, you would
want to be sure to run the update query first.
If using VB, there are multiple ways to check to see if
the record exists. Personally, I would probably open a
recordset (in VB) based on sql that would return the
record if it exists, or empty if it doesn't.
Then, again if it were me, I would check to see if a
record exists, and if so edit the record in the
recordset; or, if not, I would append a new record to the
recordset.
If you haven't worked with recordsets in VB, the first
method would probably be easiest, but if you want to try
it, you could post back and I or someone else could give
you some sample code (if so post the table and key field
names and we will be able to use the appropriate names in
the code).
I hope this helps. Post back if you have further
questions about either of the methods above.
-Ted Allen
-----Original Message-----
I run an append query on a form's timer event that appends
new order information from Sales to a table in my Quality
Assurance Database. The table in the QA Database is used
for manufacturing and testing purposes. My problem is that
when Sales edits order details, those changes are not
carried to the QA table when the append operation occurs.
Therefore I get erroneous data that I have to manually
edit. Is there a setting that will allow the records that
already exist in my table to be edited when the append
query runs?
Thanks
.
.
