Help ADO Recordset Update, Code check

[tigersden]

Hi,
I am tryong to loop through a table check a value in
the "Trade" cpolumn & depending on this set a new value in "Trade_Code" which is currently empty.
When I tried this code it deleted the value in the "Trade" column & set every trade code to 1 the first value it found.
How can I set the new value but leave the existing value in "Trade" alone.
Thanks in advance.

CODE:

Private Sub cmdTRCode_Click()
Dim rst As ADODB.Recordset
Dim strSQL As String

Set conDatabase = CurrentProject.Connection
strSQL = "SELECT * FROM tblNewDataRange"

Set rst = New Recordset
rst.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

With rst
Do While Not .EOF
Select Case rst("Trade")
Case Is = C
rst("Trade_Code") = 1
Case Is = CH
rst("Trade_Code") = 1
Case Is = E
rst("Trade_Code") = 2
Case Is = M
rst("Trade_Code") = 3
Case Is = D
rst("Trade_Code") = 4
Case Else
rst("Trade_Code") = 99
End Select
' rst("Trade") = rst("Trade")
.Update
.MoveNext
Loop
End With

rst.Close
conDatabase.Close
Set rst = Nothing
Set conDatabase = Nothing

End Sub

 

[Remou]

Hi
Did you mean
Case Is = "C"

 

[tigersden]

Thanks Remou how stupid of me.
Have tried it again but it is putting the same value in Trade_Code for all records.

 

[Remou]

I tried your code with a mockup and it seemed to work ok, in that I got different Trade_codes and the Trade field was left alone. Is it worth trying against a temp table to see if it could be something else? I added:
Dim conDatabase As ADODB.Connection
but even without, the code ran as expected.

PS What is the value you get for trade_code?

 

[tigersden]

Hi Remou
Thanks for your help I had typo, as I had been trying all diffent things after posting.
It does work but I get an error at the end a file sharing lock count something about editing a registry value.

Thanks for your help
Tim