INSERT IF NO EXISTS in access

Question

I have an [Orders] form where you register orders to be saved in an [Orders] table. Within that form there are 2 fields; you chose a provider's name from a combo box, and when you do that the company code of that provider is autofilled with a dlookup from a table called [providers].

Sometimes, though, the order is for a NEW provider whose info is not in my table. In that case, the user has to input the name and code manually. How can I use this info to INSERT this as a new record in the [providers] table, so that the next time this provider appears his info is given in the combo box ?

I was told about: INSERT ....IF NOT EXISTS .. but i can't seem to write that in a VBA query. Meaning; I want to insert my (Me!providers) and (Me!code) into [providers] table. I tried the following SQL statement:

INSERT INTO providers (provider,code) VALUES ('"&Me!provider&"','"&Me!code&"') IF NOT EXISTS

but that didnt work. Can anyone please help me with the proper SQL?

score 2 · Answer 1 · edited Apr 19 '17 at 17:17

This SQL will add the entry in provider table if the provider name is not found in provider table

INSERT INTO Providers (Provider, Code)
    SELECT TOP 1 
        'New Provider Name' AS Provider, 
        'New Provider Code' AS Code 
    FROM 
        Provider 
    WHERE 
        NOT EXISTS (SELECT TOP 1 Provider, Code 
                    FROM Provider 
                    WHERE Provider = 'New Provider Name' 
                      AND Code = 'New Provider Code');

Substitute 'New Provider Name' and 'New Provider Code'

Omit TOP 1 clause in subquery if provider table has only one record or the table is empty.

You don't need the `AS Provider` or `AS Code`. – pianoJames Nov 12 '18 at 19:57 — pianoJames, Nov 12 '18 at 19:57

score 1 · Accepted Answer · answered Oct 24 '13 at 19:15

1

I would do something like this: check to see if the code exists in the Orders table and if it doesn't, then run your Insert Into SQL. You might have to play with this a bit, depending on if your Code field is a TEXT or an INT, but this should get you most of the way there.

Dim db as Database
Dim rec as Recordset
Dim sSQL as String

Set db = CurrentDB
Set rec = db.OpenRecordset("Select * from Orders WHERE Code = '" & Me.Code & "'")

'This refreshes the dataset so you can get an accurate record count
rec.MoveFirst
rec.MoveLast

'If your record count is 0, then the code isn't in the DB yet so you need to add it
If rec.RecordCount = 0 Then
  sSQL = "INSERT INTO providers (provider,code) VALUES ('"&Me!provider&"','"&Me!code&"')";
  DoCmd.RunSQL sSQL
EndIf

'Always set your connection variables to Nothing so the connection closes!
Set db = Nothing
Set rec = Nothing

answered Oct 24 '13 at 19:15

Johnny Bones

8,786
7
52
117

thank you that does work but i used dcount instead . it seemed a bit more smple to me – grupo palmira Oct 24 '13 at 20:21
I get a Run-time error '3021' (No current record) when the Select * returns 0 records. Is the rec.MoveFirst and rec.MoveLast necessary? – Zachary Ordo - GISP Jan 09 '19 at 17:04
@ZacharyOrdo-GISP - It kind of is, in order to get an accurate row count. Perhaps you need an OnError statement to trap that? – Johnny Bones Jan 09 '19 at 18:33
If you do `SELECT COUNT(*) as rowCount ...` instead of `SELECT * ...`, you'll always get one row back. If there are no records, then `rowCount` will be 0. Should be no errors on MoveFirst or MoveLast. – RDFozz Jan 10 '22 at 21:08

INSERT IF NO EXISTS in access

2 Answers2

Linked