Logical Problems

Okay, what I have is a number and when I transfer that number to someone else, then I keep the original in the main table, but update it with the new owner info, and then I stick a letter on the end of the old number like A if that were the first transfer of that number and then I store it in a transfer table.  Well, I thought that the easiest way to do this would be to make an sql statement saying check the table for all transferred numbers like the original.  then do a recordcount on the recordset and if 1 record in the transfer table showed up then I would know that when I stored this number again it would have to have a B on the end of it. ...

here is what I have so far, but it doesn't work because everytime I run it I end up with the number and an A attached when after I run the first one the next one I run should have a B after it:

    DoCmd.SetWarnings False      'Turns the warnings off.
   
 If Me.cboCode = "T" Then
 
        Dim sql As String
        Dim rst As Recordset
        Dim varTransferredNumber As Variant
   
            sql = "Select * FROM tblAdjustmentsTransfers Where ([Trans #] Like " & Chr$(34) & txtNumber & Chr$(34) & ");"
            Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If rst.RecordCount = 0 Then
    varTransferredNumber = Me.txtNumber & "A"
ElseIf rst.RecordCount = 1 Then
    varTransferredNumber = Me.txtNumber & "B"
ElseIf rst.RecordCount = 2 Then
    varTransferredNumber = Me.txtNumber & "C"
ElseIf rst.RecordCount = 3 Then
    varTransferredNumber = Me.txtNumber & "D"
ElseIf rst.RecordCount = 4 Then
    varTransferredNumber = Me.txtNumber & "E"
End If

        rst.Close
        Set rst = Nothing


Set rst = CurrentDb.OpenRecordset("tblAdjustmentsTransfers", dbOpenDynaset)
    rst.AddNew
    rst![Trans #] = varTransferredNumber
    rst.Update
    rst.Close
Set rst = Nothing

DoCmd.SetWarnings True  'Turns the warnings back on.

MsgBox "This Transfer will be given the Number of: " & varTransferredNumber
'DoCmd.Close

Else
   
    MsgBox "Hey this must be an adjustment..."
End If