By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Help with runtime error due to incorrect VBA SQL statement syntax

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. Dim strCurrentMaterial As String
  4. Dim strSql As String
  5.  
  6. strCurrentMaterial = Me!sbfMaterials.Form!Material_Name 'the material name
  7.  
  8. strSql = "INSERT INTO tbl_Last_Visited ( Docket_ID, Log_Date, txtMaterial_Name ) " & vbCrLf & _
  9. "SELECT " & Nz(Me.[Docket_ID], "Null") & " AS Docket_ID, Now() AS Log_Date, " & strCurrentMaterial & " AS txtMaterial_Name;"
  10. DBEngine(0)(0).Execute strSql, dbFailOnError
  11.  
  12. End Sub

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

Hoping someone can help get me on track or offer a solution.
Dec 20 '11 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. "SELECT " & Nz(Me.[Docket_ID], "Null") & " AS Docket_ID, " & _
  2.         "Now() AS Log_Date, " & _
  3.         "'" & strCurrentMaterial & "' AS txtMaterial_Name;"

Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,421
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.
Dec 20 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. "SELECT " & Nz(Me.[Docket_ID], "Null") & " AS Docket_ID, " & _
  2.         "Now() AS Log_Date, " & _
  3.         "'" & strCurrentMaterial & "' AS txtMaterial_Name;"
Dec 21 '11 #3

P: 2
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.
Dec 21 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
Excellent news. Good for you :-)
Dec 21 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.