Problems populating form via lookup

[ScottK1396]

I have been trying to use DAO.recordset to more quickly populate form fields in a new project. It seems as though I have it set up properly, however it keeps throwing the same error.

I have a lookup form that I want to take a scanned serial number and tell what the scanned item is from our asset data source. I also will be adapting to create a snapshot inventory tool, independent of production.
I am using the Serial number (alpha string) from a text box to perform a lookup against a linked ODBC SQL database.

    'Populate form fields from serial scan
 
     Dim SerialScan As String
     SerialScan = Me!txtScannedText
        Dim rstC As DAO.Recordset
        Set rstC = CurrentDb.OpenRecordset("ASTM_devices_all_state", dbOpenSnapshot)
[B][COLOR=red]! [/COLOR][/B][COLOR=red][B]>[/B][/COLOR]     [COLOR=red]rstC.FindFirst "[Serial] =  " & SerialScan[/COLOR]
 
        If rstC.NoMatch Then
            txtASTMTag.Text = "NOT FOUND"
            txtASTMMfrModel.Text = "--"
            txtASTMModelnum.Text = "--"
            txtASTMLocation.Text = "--"
            txtASTMAssignment.Text = "--"
            txtASTMUsername.Text = "--"
        Else
            txtASTMMfrModel.Text = rstC!mfr_modelname
            txtASTMModelnum.Text = rstC!Model
            txtASTMLocation.Text = rstC!Location
            txtASTMAssignment.Text = rstC!Assignment
            txtASTMUsername.Text = rstC!Name
        End If
        Set rstC = Nothing

However, Access throws the following error on the highlighted line:
Run-time error '3070':
The Microsoft Access Database engine does not recognize 'J2K4K6' [the value of Me!ScannedText] as a valid field name or expression.:banghead:

I fell back to DLookup, using a previous project as template for parameters

Dim SerialScan As String
SerialScan = Me!txtScannedText
txtASTMMfrModel.Text = DLookup("[mfr_modelname]", "[ASTM_devices_all_state]", "[ASTM_devices_all_state].[Serial] = [SerialScan]")

The error thrown here is :
Run-time error'2471':
The expression you entered as a query parameter produces this error: 'SerialScan':banghead:

The only differences is that I used using a numerical list index in previous effort, and a string here. Also using a R/O SQL table instead of a SharePoint R/W table.
I read up the primer from Utter Access (thanks pr2-eugin), I think I followed convention, but I probably got all of the quotes and brackets hosed up.

 

[JHB]

You have to put string criteria between single quotes

rstC.FindFirst "[Serial]=[B][COLOR=Red]'[/COLOR][/B]" &  SerialScan [B][COLOR=Red]& "'"[/COLOR][/B]

Here is more mixed up!

DLookup("[mfr_modelname]", "[ASTM_devices_all_state]", "[ASTM_devices_all_state].[Serial] =[B][COLOR=Red]'" &[/COLOR][/B] [SerialScan] [B][COLOR=Red]& "'"[/COLOR][/B])

 

[ScottK1396]

Thank you. I need to pin this to my wall; it's bad enough I fatfinger everything. This is going to be worse than bracketing until I get more proficient.

Thanks for the experienced set of eyes.

 

[namliam]

Just FYI:
For Text:
"[ASTM_devices_all_state].[Serial] ='" & SerialScan & "'")
If your text can have ' in it you can also use
"[ASTM_devices_all_state].[Serial] =""" & SerialScan & """")

For dates:
"[ASTM_devices_all_state].[Serial] =#" & SerialScan & "#")
The Serialscan MUST be in US date format (MM/DD/YYYY)

For numbers:
"[ASTM_devices_all_state].[Serial] =" & SerialScan & "")
Or a little shorter:
"[ASTM_devices_all_state].[Serial] =" & SerialScan)

Also I would strongly advice you to not (over)use the []

... More over... just to really give you something "real" instead of your FindFirst construct which is terribly inefficient... try this:

        Set rstC = CurrentDb.OpenRecordset("Select * from ASTM_devices_all_state where [Serial] =  " & SerialScan, dbOpenSnapshot)

Which should run considerably faster than the findfirst, not to mention its less typing, thus less fat-finger-able