Solved

Microsoft Access VBA - Renumbering Function

Posted on 2014-02-28
1
715 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

792 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