Errror when updating table using VBA

[mafhobb]

So I have this code in an afterupdate event in an unbound text box to update the value of a table if the textbox is updated:

Private Sub txtCustRepID_AfterUpdate()

'Go to Calls table and find original value for CustRepID
    'Fin the Call ID first
        Dim CallIDVar As Long
        Dim ContactIDVar As Long
        Dim CustRepIDOr As String
        CallIDVar = Forms![Contacts]![Call Listing Subform].Form![CallID]
        
    'find CustRepID Original based on CallID using a recordset on the Calls table, matching it to CallID
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Calls", dbOpenDynaset)
        rs.FindFirst "[CallID]=" & CallIDVar
    'Assign the value to the CustRepIDOr variable
        CustRepIDOr = rs!CustRepID
    'reset recordset
        Set db = Nothing
        Set rs = Nothing

    Dim sName As String
    Dim CustRepIDNew As String
        
    'get the new value
        CustRepIDNew = Me.txtCustRepID.Value
        
    'Are you sure you want to change it?
        If MsgBox("Are you sure you want to change this number?", vbQuestion + vbYesNo, "Change?") = vbNo Then
            'If entering the new value is cancelled
            Me.txtCustRepID.Value = CustRepIDOr
            Exit Sub
        Else
            'Who is making the changes
            sName = Nz(DLookup("EngineerID", "Tbl-Users", "PKEnginnerID = " & StrLoginName & ""), "")
            
            'Accept change and add new value to table
            CurrentDb.Execute _
            "UPDATE Calls " & _
            "SET CustRepID = " & CustRepIDNew & " " & _
            "WHERE CallID = " & CallIDVar, dbFailOnError
            
            'Add text to box below
            txtNewDetails = txtNewDetails & " The number has been changed."
        End If

End Sub

This code works well when entering numbers in the text box but it returns error 3061; "Too few parameters. Expected 1" when along with the numbers there is a letter in the textbox.

The error happens in this part of the code:

            'Accept change and add new value to table
            CurrentDb.Execute _
            "UPDATE Calls " & _
            "SET CustRepID = " & CustRepIDNew & " " & _
            "WHERE CallID = " & CallIDVar, dbFailOnError

The underlying table has text as type of data for this field.

What am I doing wrong?

mafhobb

 

[pr2-eugin]

If it is Text type then use single quotes around the field like.

"SET someTextField = '" & someVariable & "' WHERE someCondition"

 

[mafhobb]

Yeap. This fixed it:

            "SET CustRepID = '" & CustRepIDNew & "' " & _

Thank you!

mafhobb