I have a Routine - "RenumberFDID" that is reusable by simply passing in two prefixes
such that it will renumber both based on the form's recordset.
However, I noted that the "RenumberFDID" is not working as expected.
For example, when user make changes to the FDID in tblMain, the routine is expected to renumber the "group series
in tblMain. This by my experience is not working.
If I have these series of numbers in 2 groups in tblMain.
Note: "SID" is an Autonumber it does not change.
"FDID" is a text type and it changes after re-ordering for reporting purposes.
If I change:
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
Note that re-numbering occurs in both groups
I have enclosed a sample db here as visual aid.
I have also placed the the "RenumberFDID" function in the code snippet for assistance.
To test my sample simply do the following when frmMain opens.
When SID#1 opens, make changes to the controls as follows:
a). Select "Parks Dept" from the combo
b). Select "Ply" from the next combo
c). Select "201" from the next combo
d). Type in "111" and..
e). Select "Dome" in that last combo
This should put in the scenerio described above and at the point the "RenumberFDID" function is expected to renumber the last two digits as shown above. This is why I posted this question because the routine is not working.
Public Sub RenumberFDID(ByVal sOldPrefix As String, ByVal sNewPrefix As String, ByVal sTag 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
'Track no change
' If sOldPrefix & sTag = sNewPrefix & sTag Then Exit Sub
Set rs = Me.RecordsetClone
iNewPrefix = 0
iOldPrefix = 0
sFormat = "00"
'Filter on records that begin with the old and new letter
sSql = "FDID LIKE '" & sNewPrefix & sTag & "*' OR FDID LIKE '" & sOldPrefix & sTag & "*'"
Debug.Print "Initiating Search", sSql
rs.Filter = sSql
'Now loop through updating existing records
Do While rs.EOF = False
Debug.Print "Found ID=" & rs!SID, "FDID=" & rs!FDID
If Left$(rs!FDID, 2) = sOldPrefix & sTag Or Left$(rs!FDID, 2) = sNewPrefix & sTag Then
If Left$(rs!FDID, 2) = sOldPrefix & sTag Then
iOldPrefix = iOldPrefix + 1
Debug.Print "Updating Record to " & sOldPrefix & sTag & Format(iOldPrefix, sFormat)
rs!FDID = sOldPrefix & sTag & Format(iOldPrefix, sFormat)
iNewPrefix = iNewPrefix + 1
Debug.Print "Updating Record to " & sNewPrefix & sTag & Format(iNewPrefix, sFormat)
rs!FDID = sNewPrefix & sTag & Format(iNewPrefix, sFormat)
Set rs = Nothing