Using VBA Variables in a SQL statement

[saltydawg617]

I am using a barcode reader to capture data that is encoded in an ID card and enter it into a table. An input box opens so that you can scan the id. The ReadIDCard() then breaks the data down into different sections to corrispond with LastName, FirsName, and etc. Then I want the data to be inserted into a table Check_In.

The code works fine until it gets to the SQL statement. I receive a input box titled "Input parameter value" and I have to type in the value. My question is how do I pass the value from a VBA variable into the SQL statement without having to type it in.

 
Public Function ReadIDCard()
 
DIM txtIN As String
DIM txtSSN As String
DIM txtFirstName As String
DIM txtLastName As String
 
txtIN = InputBox("Scan ID") 'Scan ID Card Popup Box
txtSSN = Mid$(txtIN, 2, 6) 'SSN
txtFirstName = Trim(Mid$(txtIN, 16, 20)) 'First Name
txtLastName = Trim(Mid$(txtIN, 36, 26)) 'Last Name
 
DoCmd.RunSQL "INSERT INTO Check_In ([SSN], [LastName], [FirstName]) Values (txtSSN, txtLastName, txtFirstName)"

[pbaldy]

They have to be concatenated into the string. Text and date values need delimiters. Along the lines of

"...Values ('" & txtSSN & "',..."

Decent tutorial here:

http://www.baldyweb.com/BuildSQL.htm

[saltydawg617]

Thanks for the help. It was all in the spacing.

[pbaldy]

Happy to help, and welcome to the site.