We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Public Sub DeleteRenumber() Dim rs As DAO.Recordset Dim cbo As String Dim i As Integer Dim bLoop As Boolean Dim sPrefix As String Dim sSql As String On Error GoTo Err_cmdDelete_Click bLoop = True sPrefix = "XW-" & rs!TestType & "-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-" 'Find next number from one about to be deleted i = Val(Mid$(Me.FDID, 2)) + 1 'Now update existing records Set rs = Me.RecordsetClone 'Find first next highest number sSql = "FDID = '" & sPrefix & Format(i, "00") & "'" Debug.Print "Looking for", sSql rs.FindFirst sSql Do While bLoop 'If no records found, end loop If rs.NoMatch = True Then bLoop = False Else 'Decrement number Debug.Print "Updating ID", rs!SID rs.Edit rs!FDID = sPrefix & Format(i - 1, "00") rs.Update 'Find next highest i = i + 1 sSql = "FDID = '" & sPrefix & Format(i, "00") & "'" Debug.Print "Finding next", sSql rs.FindNext "FDID = '" & sPrefix & Format(i, "00") & "'" End If Loop 'Closedown rs.Close Set rs = Nothing Me.Requery Exit_cmdDelete_Click: Exit Sub Err_cmdDelete_Click: MsgBox Err.Description Resume Exit_cmdDelete_Click End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.