Help with runtime error due to incorrect VBA SQL statement syntax

[moonlighting]

Hoping an expert can help me fix an error that I'm getting while trying to execute an SQL statement in Access Visual Basic.

Please note I have limited programming knowledge.

Here's the background of my problem:

I'm trying to create a list of the last ten records visited by the database user. As a record is visited, I want to capture in another table its [Docket_ID], a field that is located on a subform [Material Name], and the date and time the record is visited.

My ultimate goal is to create a drop down or continuous form which lists the last 10 records and the user will be able to click on one in the list and be taken back to that record. But I need to get this functionality to work to add the records to the last visited table.

I started with Alan Browne's solution to a similar problem here and tried to adapt it: http://objectmix.com/ado-dao-rdo-rds...d-records.html

This solution only captures two items and I need to capture three.

Here is my code:

Private Sub Form_Current()

Dim strCurrentMaterial As String
Dim strSql As String

strCurrentMaterial = Me!sbfMaterials.Form!Material_Name 'the material name

strSql = "INSERT INTO tbl_Last_Visited ( Docket_ID, Log_Date, txtMaterial_Name ) " & vbCrLf & _
"SELECT " & Nz(Me.[Docket_ID], "Null") & " AS Docket_ID, Now() AS Log_Date, " & strCurrentMaterial & " AS txtMaterial_Name;"
DBEngine(0)(0).Execute strSql, dbFailOnError

End Sub

I am getting a Run-time error '3075' Syntax error (missing operator) in query expression 'the name of the strCurrentMater ial varible appears here'

Hoping someone can help get me on track or offer a solution.

[Rabbit]

If that's a string (as the name of the variable would seem to imply) then in the SQL string, it needs to be surrounded by quotes.

[NeoPa]

It looks like a string Literal. You do indeed need to use quotes (') in that case. See Quotes (') and Double-Quotes (") - Where and When to use them for more on that.

Line #9 might then be :

"SELECT " & Nz(Me.[Docket_ID], "Null") & " AS Docket_ID, " & _
        "Now() AS Log_Date, " & _
        "'" & strCurrentMaterial & "' AS txtMaterial_Name;"

[moonlighting]

Rabbit and NeoPa, thank you!
The quotes/double quotes reference will come in handy.

Now it works exactly like I wanted, and I can build the form to display the last 10 records.

[NeoPa]

Excellent news. Good for you :-)