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
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