Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
784 views
in Technique[技术] by (71.8m points)

forms - INSERT IF NO EXISTS in access

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...