forms - 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?

Answer 1

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.