Re-ordering Autonumber Question

The Sub below in Code Snippet is expected to re-order the two groups of serial numbers in tblMain using the "AfterUpdate Event" of some frmMain's control.

I am quite sure that the Sub needs a minor correction in order to make it work as expected.

tblMain without the FDID modifications look like the one below:
tblMain
SID      TestType            FDID                                     BNo     LNo    CTypeID    STypeID
1          GN              XW-GN-TA-6612-23-CM-01       6612      23           TA             CM
2          GN              XW-GN-TA-6612-23-CM-02       6612      23           TA             CM
3          GN              XW-GN-TA-6612-23-CM-03       6612      23           TA             CM
4          PK               XW-PK-TA-1129-51-DM-01       1129       51           TA             DM
5          PK               XW-PK-TA-1129-51-DM-02       1129       51           TA             DM

User later modify a record in tblMain using frmMain form controls (AfterUpdate Event) and the result below is expected to be obtained.

For example, SID 2 before changes were made, field FDID was:

tblMain
SID      TestType          FDID                                        BNo      LNo    CTypeID     STypeID
2            GN             XW-GN-TA-6612-23-CM-02        6612     23         TA            CM

but after changes were made to SID 2, then field FDID now changed to:

tblMain
SID        TestType              FDID                                   BNo       LNo     CTypeID      STypeID
2               GN           XW-PK-TA-1129-51-DM-03        6612       23            TA             DM

Now after re-order, both FDID groups ............
XW-GN-TA-6612-23-CM- ..and.. XW-PK-TA-1129-51-DM-

......were both expected to re-order to:
tblMain
SID   TestType    FDID                                           BNo     LNo    CTypeID    STypeID
1         GN           XW-GN-TA-6612-23-CM-01      6612     23         TA           CM
2         PK            XW-PK-TA-1129-51-DM-01      1129      51        TA            DM '<== After reorder
3         GN           XW-GN-TA-6612-23-CM-02      6612     23        TA            CM '<== After reorder
4         PK            XW-PK-TA-1129-51-DM-02      1129      51        TA            DM '<== After reorder
5         PK            XW-PK-TA-1129-51-DM-03      1129      51        TA            DM '<== After reorder

Problem:
When the sub is called from the "AfterUpdate event" code of (cboTest, cboCType, cboBNo, txtLNo and cboSType) .......as shown below
        'Call renumbering function
        RenumberFDID sOldPrefix, sNewPrefix

...then group "XW-GN-TA-6612-23-CM-" was re-ordered but....
group "XW-PK-TA-1129-51-DM-" failed to re-order such that tbMain looked like this....
tblMain
SID   TestType    FDID                                           BNo     LNo    CTypeID    STypeID
1         GN           XW-GN-TA-6612-23-CM-01      6612     23         TA           CM
2         PK            XW-PK-TA-1129-51-DM-01      1129     51        TA            DM '<== This is Ok
3         GN           XW-GN-TA-6612-23-CM-03      6612     23        TA            CM '<== This is not Ok
4         PK            XW-PK-TA-1129-51-DM-02      1129      51        TA            DM '<== This is Ok
5         PK            XW-PK-TA-1129-51-DM-03      1129      51        TA            DM '<== This is Ok

Note that SID 3 (FDID) did not re-order as expected.....it remained as:
3         GN           XW-GN-TA-6612-23-CM-03      6612     23         TA           CM
.....instead of..

3         GN          XW-GN-TA-6612-23-CM-02        6612     23         TA           CM  '<== This is expected

I have attached a sample db for testing purposes at this link:
http://www.geocities.com/bombastikbill/Renumbering_db.zip

To Test:
 When frmMain opens ..
      navigate to SID 2 and change the following data in frmMain field controls as follows:
     Current value:                                            Changed Value
    FrmMain.cboTest         = "GN"                           "PK"
    frmMain.cboCType      = "TA"                             "TA"            '<=== Value here did not change
    frmMain.cboBNo          = ''6612"                         "1129"
    frmMain.cboLNo          =  "23"                             "51"
    frmMain.cboSType      = "CM"                             "DM"

The Sub with the problem is placed in Code Snippet.

Public Sub RenumberFDID(ByVal sOldPrefix As String, ByVal sNewPrefix As String)
 
    Dim rs As DAO.Recordset
    Dim iOldPrefix As Integer       'Count for renumbering original prefix
    Dim iNewPrefix As Integer     'Count for renumbering new prefix
    Dim sSql As String
    Dim sFormat As String
       
    DoCmd.Hourglass True
    
    'Initialise
    Set rs = Me.RecordsetClone
    iNewPrefix = 0
    iOldPrefix = 0
    sFormat = "00"
 
'CHANGE HERE - ADD sNewPrefix TO FILTER ONLY IF SPECIFIED 
    'Filter on records that begin with the old
    sSql = "SELECT * FROM tblMain WHERE FDID LIKE '" & sOldPrefix & "*'"
    
    'If new prefix specified (specified when changing record) then include that as well
    If sNewPrefix <> "" Then sSql = sSql & " OR FDID LIKE '" & sNewPrefix & "*'"
    
    Debug.Print "Initiating Search", sSql
    
    rs.Filter = sSql
    Set rs = CurrentDb.OpenRecordset(sSql)
    
    'Now loop through updating existing records
    Do While rs.EOF = False
 
        Debug.Print "Found ID=" & rs!SID, "FDID=" & rs!FDID
        
        rs.Edit
        
        If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Then
            iOldPrefix = iOldPrefix + 1
            Debug.Print "Updating Record to " & sOldPrefix & Format(iOldPrefix, sFormat)
            rs!FDID = sOldPrefix & Format(iOldPrefix, sFormat)
 
'CHANGE HERE - CHECK sNewPrefix HAS A VALUE 
        ElseIf sNewPrefix <> "" Then
            iNewPrefix = iNewPrefix + 1
            Debug.Print "Updating Record to " & sNewPrefix & Format(iNewPrefix, sFormat)
            rs!FDID = sNewPrefix & Format(iNewPrefix, sFormat)
        End If
 
        rs.Update
        
        rs.MoveNext
    Loop
 
    'Closedown
    rs.Close
    Set rs = Nothing
    
    Me.Refresh
 
    DoCmd.Hourglass False
End Sub

Select allOpen in new window