Problem with Insert SQL

[webopdoug]

I was using the following syntax in a Private Function when Access complained "Invalid SQL Statement":

Dim strSQL As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "tblCommissionsPaid"
rs.Open strSQL, conn, , adLockOptimistic
rs.AddNew <-- Error at this point
rs!ContractNumber = Me.ContractNumber
rs!RepID = Me.RepID
rs!CommTypeID = 2
rs!CommAmt = Me.PurchAmt * 0.15
rs!CommDate = Me.ContractDate
rs.Update
rs.Close
conn.Close

I tried an alternate Syntax with no luck
strSQL = "INSERT INTO tblCommissionsPaid (ContractNumber, RepID, CommAmt, CommDate, CommTypeID) " & _
"VALUES '" & Me.ContractNumber & "', ' & Me.RepID & ', ' & Me.PurchAmt * 0.15 & '," & _
"' #" & Me.ContractDate & "#', '2'"
DoCmd.RunSQL strSQL

I have used the rs.AddNew before with success (same syntax with a different table). Can you tell me what I'm missing here? Or is there a better way?

 

[webopdoug]

I was able to get this to work

strSQL = "INSERT INTO tblCommissionsPaid ([ContractNumber], [RepID], [CommAmt], [CommDate], [CommTypeID]) " & _
"VALUES ('" & Me.ContractNumber & "', " & Me.RepID & ", " & Me.PurchAmt * 0.15 & ", " & _
" #" & Me.ContractDate & "#, 2)"

I'm still not sure if this is the best way to do this.

 

[JoeAtWork]

Yup, you need brackets around the parameters of the VALUES clause.

And yes, it's better to use an INSERT statement than having the overhead of opening a recordset (especially since you were opening up the entire table - if you do use recordsets to add records, at least make sure your SQL statement doesn't retrieve records you don't need).