asked on

Access VBA Match a Value in Table1

I need to open a form
Input a number into TextBox1 and have it Lookup that value in Table1 Field ‘CompanyID”, if value matchs append record into Table2 Else not match append into Table3.

How would you write this in Access VBA?

Scott McDaniel (EE MVE )

You can use DLookup to determine if your record exists. Assuming CompanyID is a STRING value:

Dim sComanyID As String

sCompanyID = Nz(Dlookup("CompanyID", "Table1", "CompanyID=" & Me.Textbox1), "")

If sCompayID <> "" Then
'/ we found he value
Currentdb.Execute "INSERT INTO Table2(Col1, Col2, Col3) VALUES(Val1, Val2, Val3)"
Else
'/ we didn't find the value
Currentdb.Execute "INSERT INTO Table3(Col1, Col2, Col3) VALUES(Val1, Val2, Val3)"

End If

Scott McDaniel (EE MVE )

Sorry, the DLookup should look like this if CompanyID is a STRING value:

sCompanyID = Nz(Dlookup("CompanyID", "Table1", "CompanyID='" & Me.Textbox1) & "'", "")

Rey Obrero (Capricorn1)

scott your ")" after Textbox1 is off , should be before the comma

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

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

Start Free Trial

ca1358

ASKER

What if it is a numeric value?

ca1358

ASKER

Thank you

My final
Dim sCompanyID 'As String

'string 'sCompanyID = Nz(DLookup("CompanyID", "Table1", "CompanyID='" & Me.TextBox1 & "'"), "")
'numeric
sCompanyID = Nz(DLookup("CompanyID", "Table1", "CompanyID=" & Me.TextBox1), "")

If sCompanyID <> "" Then
'/ we found he value
CurrentDb.Execute "INSERT INTO Table2(CompanyID ) VALUES(" & Me.TextBox1.Value & ");"

Else
'/ we didn't find the value
CurrentDb.Execute "INSERT INTO Table3(CompanyID ) VALUES(" & Me.TextBox1.Value & ");"

End If