Solved

Microsoft Access VBA - Renumbering Function

Posted on 2014-02-28
1
699 Views
Last Modified: 2014-02-28
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
0
Comment
Question by:Ei0914
1 Comment
 

Accepted Solution

by:
Ei0914 earned 0 total points
ID: 39895940
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now