Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

Syntax Error in VBA SQL

I am trying to run an SQL statement in MS Access VBA.  The values in the immediate window for strSQl is:

INSERT INTO tblMAPUniqueID_Junction_SessionID(UniqueJournalID,EventID) VALUES(A011473893_1_614,287984)

but I am getting an error when I try to execute: "too few pararmeters: Expected 1.  Is my statement written incorrectly?

Function MultiSelectMAP()

Dim ItemIndex As Variant
Dim strSessionID As String
Dim strMAPTransactions As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSessionID = Me.lstEligibleSessions

For Each ItemIndex In Me.lstDeptDetails.ItemsSelected
    strMAPTransactions = Me.lstDeptDetails.ItemData(ItemIndex)
    MsgBox Me.lstDeptDetails.ItemData(ItemIndex)

        strSQL = "Select * from tblMAPUniqueID_Junction_SessionID"
       
       
        Set rs = db.OpenRecordset(strSQL)
       
            strSQL = "INSERT INTO tblMAPUniqueID_Junction_SessionID(UniqueJournalID,EventID) VALUES(" & strMAPTransactions & "," & strSessionID & ")"


            db.Execute (strSQL)
           
Next

End Function
0
marku24
Asked:
marku24
  • 4
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Looks like that first value is a Text value, so:

   strSQL = "INSERT INTO tblMAPUniqueID_Junction_SessionID(UniqueJournalID,EventID) VALUES('" & strMAPTransactions & "'," & strSessionID & ")"

Note I inserted single quotes ( ' ), which I highlighted in Bold.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, what's the point of opening the Recordset in the loop? I realize you may have pared down the code to post it here, but as it stands, there doesn't seem to be any reason to open that recordset.
0
 
marku24Author Commented:
Thank you so much.  The quotes always confuse me.  I thought I had to open the recordset to read it.  Tnanks
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The quotes always confuse me.  

They can be confusing when you first are learning SQL. Essentially, any Text data must be enclosed in single or double quotes. I use single quotes since I do as much work in SQL Server as I do in Access, and it was just easier to do the same in all.

I thought I had to open the recordset to read it.

A Recordset does have to be open to "read" it, but in the code above you're not reading it. To read a value from a recordset, you'd use this syntac:

msgbox rst("FieldName")

Of course, a Recordset often contains more than one record, so that would just give you the value from the current record.

In your case, you're not inserting into a recordset, but rather into a Table.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Very common mistake - not enclosing Text values in single or double quotes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now