append query to different table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jpr - 28 Mar 2006 15:10 GMT

Friends, I would like some help with a code that allows me to run an
append query only if a specific field is not already stored into
another table.

Example.
I add a new customer to my database using table1.
I type his SSN. The code should check if this SSN is already present in
another table (table2). If yes, so the code should skip the append
query and just update any modified record included the SSN.

If not, the code should run the append query.

All this should be done in the background while working in a form with
its recors source to the table1.

Can anyone help me? Thanks.

Reply to this Message

Ron2005 - 30 Mar 2006 21:27 GMT

Two thoughts:

1) If the SSN is primary/unique key in the second table, then you can
simply do the append everytime, and then run a second query to do the
update. The append will not add if the key is primary or indexed and
no duplicates allowed. And the second update will update it if perhaps
it was already there. This is a messy way but it will work.

2) Better approach:
a- perform a dlookup or dcount on the second table for a query that has
the ssn as criteria.
b- If the count is 0 then run the append query to update the file
c- if the count is >0 then run the update query to update the file.

Reply to this Message