Microsoft Access VBA - Renumbering Function

Hi Experts! I need to develop a function to do the following:

Quote     Line #
12345        1
12345        2
12345        4 <------- change to 3
12345        4 <------- change to 3 (can have duplicate line #'s)
12345        5 <------- change to 4
12345        6 <------- change to 5

I started working on the code but am hitting a wall... I attached a doc with my code thus far.
Sample.docx
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Eileen MurphyConnect With a Mentor Independent Application DeveloperAuthor Commented:
I got it to work - here's the function:

Private Sub cmdRenumber_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String, strCriteria As String, intQ As Long, intQHold As Long
    Dim i As Integer, chki As Integer
   
    strSQL = "SELECT [Quote-Detail].* FROM [Quote-Detail] ORDER BY [Quote-Detail].Company, [Quote-Detail].Quote, [Quote-Detail].Line;"
   
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    i = 1
    rst.MoveFirst
    Do Until rs
        intQ = rst!Quote
        chki = rst!Line
        strCriteria = "Quote = " & intQ & " and Line = " & chki
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then
            Do Until rst.NoMatch
                rst.Edit
                    rst![LineNew] = i
                rst.Update
                rst.FindNext strCriteria
            Loop
            i = i + 1
        End If
    rst.MoveNext
    If rst.EOF Then GoTo ContinueHere
    intQHold = rst!Quote
    If intQHold <> intQ Then
        i = 1
    End If
    Loop
   
ContinueHere:
    rst.Close
   
    Set rst = Nothing
    Set db = Nothing
   
    MsgBox "Done!"

End Sub
0
All Courses

From novice to tech pro — start learning today.