asked on

INSERT INTO - If CoName NOT IN

Experts,

I am importing from excel and sometimes I need to add a CoName to the db as that company has not been added yet. I have to manually add that CoName to tblCompanies and its starting to be a real pain. I think what I am after is an INSERT INTO tblCompanies.CoName when the excel file company_name is NOT IN tblCompanies. I do not know how to first check if the company name is contained in tblCompanies.CoName first and then automatically add that excel file company_name into tblCompanies.CoName

I hope that makes sense. Let me know if I have missed something.

thank you

John Tsioumpris

Private Function InsertIfNot(companyName as String)
Dim rst as DAO.Recordset
set rst=currentDB.openRecordset("tblcompanies")
With rst
.Findfirst("CoName = '" & companyName & "'"
If .NoMatch then
.AddNew
.Fields("CoName")=companyName
.Update
End if
End with

Open in new window

Because the new code formatter is inserting extra line breaks this line is

.Findfirst("CoName = '" & companyName & "'"

pdvsa

ASKER

whoa that is awesome. Let me try that out. thanks!

pdvsa

ASKER

John, I think I need to reference the table that is imported into access. Its called XLImport and this table is the exact copy of the data that is imported (appended) from excel. I am not sure but I think XLImport would need to be referenced somewhere? (like XLImport.Company_Name).. thank you

ASKER CERTIFIED SOLUTION

Anders Ebro (Microsoft MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Hi Anders, is DISTINCT necessary? I have another query like this but it doesnt use DISTINCT and I get the same results.

pdvsa

ASKER

I guess I could execute that append query instead of developing more complicated code.

Like this: CurrentDb.Execute "qryAppendCoNotIN", dbFailOnError

Anders Ebro (Microsoft MVP)

If you don't use distinct, it might try to insert the same company name multiple times, if multiple records come from the same new company.