x
Solved

# Microsoft Access VBA - Renumbering Function

Posted on 2014-02-28
Medium Priority
779 Views
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
Question by:Eileen Murphy
1 Comment

Accepted Solution

Eileen Murphy 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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month11 days, 3 hours left to enroll

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

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