VBA insert error

[TheCandyman]

I'm trying to do a insert in a table, if i get the output from the code and run that sql line in a query it updates the table. If it's in the vba code it doens't work, so i'm not sure what i can do to make it work. All values are numbers. The problem is in the Year, if i take that out it works. Year is a lookup value from another table, values are 2007, it's ID is 2082530338.

        Set adoCmd3 = New ADODB.Command
        Set adoRst3 = New ADODB.Recordset
        Set adoConn3 = CurrentProject.Connection
        strSQL3 = "Insert Into tblDues (AzDAAID, Year, Yearly_Dues, MethodPaidID) "
        strSQL3 = strSQL3 & "Values(" & adoRst2.Collect("AzDAAID") & ""
        strSQL3 = strSQL3 & "," & Me.cboToYear.Value & ""
        strSQL3 = strSQL3 & "," & Me.cboToDues.Value & ""
        strSQL3 = strSQL3 & ",4)"
        Set adoCmd3 = New ADODB.Command
        With adoCmd3
            .ActiveConnection = adoConn3
            .CommandText = strSQL3
            .CommandType = adCmdText
            .Execute , , adExecuteNoRecords
        End With
        Set adoCmd3 = Nothing
        adoRst2.MoveNext

The SQL statement that runs fine if i paste it into a query but wont run in the code

Insert Into tblDues (AzDAAID, Year, Yearly_Dues, MethodPaidID) Values(445,2082530338,40,4)

Error

run-time error '-2147217900 (80040e14)':
Syntax error in INSERT INTO statement.

 

[PHV]

Have you tried this ?
strSQL3 = "Insert Into tblDues (AzDAAID, [!][[/!]Year[!]][/!], Yearly_Dues, MethodPaidID) "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheCandyman]

It doesn't error, but the year is blank. If i enter the 2007 or the 2082530338 it still leaves the year blank. Thoughts?