Update Query in VBA updates field to -1!?

[Riun]

Hi,

I'm trying to run an update query in vba and i'ts not working. It does the update but the [Limit Level 2] field is updated to "-1".

here's the query:

 
Dim stDocName As String
Set rst = New ADODB.Recordset
        stDocName = "SELECT * FROM [Limit Table] WHERE [ID]=" & txtID.Value & ""
        rst.Open stDocName, CurrentProject.Connection
 
        If txtID = "" Or IsNull(txtID) Then
        stDocName = MsgBox("Click on the Row that you want to Update")
    Else
        stDocName = "UPDATE [Limit Table] SET [Limit Level 1]='" & ComboLevel1.Value & "',[Limit Level 2]='" & ComboLevel2.Value & "' AND [Primary/Secondary]='" & ComboPS.Value & "',[Limit Type]='" & ComboType.Value & "',[Limit Amount]='" & txtAmount.Value & "',[Limit Currency]='" & ComboCurrency.Value & "',[Limit Approval Date]='" & txtDate.Value & "' WHERE [ID]=" & txtID
        DoCmd.RunSQL stDocName
        lstTEST.Requery
 
    End If 

[Limit Level 2] is a combobox with a dropdown that depends on what has been chosen in [Limit Level 1]. I have put the code for this in the 'gotfocus' property of ComboLevel2. I think this might have something to do with the problem, although the dropdown works perfectly.

any ideas?

regards,
Riun

[MMcCarthy]

Is txtID a text or a number field?

You haven't declared rst as a variable (i.e.)

Dim rst As ADODB.Recordset

In your update statement the syntax is wrong

"UPDATE [Limit Table] SET [Limit Level 1]='" & ComboLevel1.Value & "', [Limit Level 2]='" & ...."

it should be

"UPDATE [Limit Table] SET [Limit Level 1]='" & ComboLevel1.Value & "' AND [Limit Level 2]='" & ...."