Solved Update a Record If It Exists and Insert If It Doesn't Exist

[Pac-Man]

Hello,

I'm trying to use the following SQL to update a record in a table:

SQL = "UPDATE " & TblName & " SET " & TblName & ".SValue = '" & SValue & "' WHERE (((" & TblName & ".SKeyword)= '" & SKeyword & "'));"
'    Debug.Print SQL
    CurrentDb.Execute SQL

Where SQL, TblName, SValue and SKeyword are strong variables dimmed already.

I want to edit it so that it inserts a new record if it doesn't exist. Can it be done by modifying the SQL or do I have to use some if statement.

Best Regards,
Abdullah

 

[Galaxiom]

Use an UPDATE query with a LEFT OUTER JOIN from the source to the destination table, joined on the key field you are comparing.

It is sometimes called an UPSERT query. Although this word is meaningless in SQL it will give results in Google.

 

[isladogs]

Normally you would use an unmatched append query to insert the new records and an update query to modify the existing records.
However in certain circumstances you can combine these into one query commonly known as an UPSERT or UPEND query. For more info, see UpEnd Query - Mendip Data Systems
EDIT Ah Greg got there a bit quicker

 

[Pac-Man]

Thanks @Galaxiom and @isladogs for reply. I've seen the demo in your website before posting here. But I don't have a second table. I just use this code to update one record at a time by inputting the value in code.

PS. I'm using this code save some settings in a table. Mostly it only require updating the value but sometime i have to insert new record.

 

[gemma-the-husky]

It wouldn't really matter if you did it in 2 steps.

1. A dlookup to see if it's there
2. if it does, then an update query OR
if it doesn't than an insert query.

 

[Pac-Man]

It wouldn't really matter if you did it in 2 steps.

1. A dlookup to see if it's there
2. if it does, then an update query OR
if it doesn't than an insert query.

Thanks a lot. That will work for me.

 

[arnelgp]

dlookup

dlookup will return Null if it does not find and the Field datatype if it does.
to be consistent, just use DCount() it always return number (0 if the criteria is not met, and "count" if it does).

 

[Pac-Man]

dlookup will return Null if it does not find and the Field datatype if it does.
to be consistent, just use DCount() it always return number (0 if the criteria is not met, and "count" if it does).

Thanks a lot @arnelgp.