Syntax Error (Missing Operator) In Qry Expression 'Service Fee"

[d9pierce1]

Hi all,
I am getting an error in my code (See Below)


I am sure its in the " & CboDescription.Column(1) & " and I cannot figure out what syntax is needed.
Its a combo box that I am trying to get the column 1 text from to post in the Description of my table which is short text

Private Sub CmdPostToLedger_Click()
    Dim strSQL As String
    Dim db As DAO.Database
    Set db = CurrentDb
    


Select Case Me.CboCorrectionType


Case 2 'Debit


        strSQL = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, TransMethodID, Debit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & CboDescription.Column(1) & ", " & CboTransMethod & ", " & TransAmount & ")"
Case Else 'Credit
        strSQL = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & CboDescription.Column(1) & ", " & CboTransMethod & ", " & TransAmount & ")"
        End Select
        db.Execute strSQL, dbFailOnError


Set db = Nothing
    Me.RecordLock = True
    Call Form_Current
    
    End Sub

[Micron]

Pretty sure you cannot directly get a column value in sql by referencing it - not even if you include the form name. However, you can if you wrap the reference in Eval function but not sure I recall the syntax correctly. Try

Eval(frmFormName.CboDescription.Column(1))
and if not, perhaps

Eval(Forms!frmFormName.CboDescription.Column(1))

[Minty]

Assuming it's text you need to escape it?

" & CboToAccount & ", " & CboDescription.Column(1) & ", 

" & CboToAccount & ", '" & CboDescription.Column(1) & "',

@Micron you definitely can when you are simply creating a SQL string - it's evaluated in the concatenation.

[d9pierce1]

Please disregard as I found error... Needed ' and ' put on it.
Sorry all,
Thanks
Dave

[Micron]

it's evaluated in the concatenation.

Yes, course. My bad - had not finished one cup yet.

[Minty]

Yes, course. My bad - had not finished one cup yet.

It's a bit later here, might be on my first shortly!

[Welshgasman]

When are you going to start using Debug.Print?