We help IT Professionals succeed at work.

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?
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

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

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

Top Expert 2016

Commented:
scott your ")" after Textbox1  is off , should be before the comma
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Thanks cap!

My DLookup should look like this:

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

Author

Commented:
What if it is a numeric value?

Author

Commented:
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