Auto Increment recordset

[lgray]

I think this is a simple one for the MVPs. But not for the novice still
grasping at VBA....

Below is a sample of the data I am trying to increment
CompanyID Record Detail Auto Increment Value
312 Detail 1 ?
312 Detail 2 ?
312 Detail 3 ?
318 Detail 1 ?
318 Detail 2 ?
319 Detail 1 ?
319 Detail 2 ?
319 Detail 3 ?
319 Detail 4 ?
321 Detail 1 ?

What is the recordset Code that I need to increment the "Increment" field as
follows
CompanyID Record Detail Auto Increment Value
312 Detail a 1
312 Detail c 2
312 Detail as 3
318 Detail 3181 1
318 Detail 3182 2
319 Detail 1a 1
319 Detail 2b 2
319 Detail 3c 3
319 Detail 4d 4
321 Detail 1 1


Thank you as always,
Linda

 

[dymondjack]

Hi Linda.

You'll need a variable to record the last CompanyID and a variable to record
the last Increment. And the recordset variables, of course. I haven't
tested the code below, it may need to have a bug or two worked out, but this
should get you going.


Dim rs As DAO.Recordset
Dim sSQL As String
Dim lLastRecord As Long
Dim iIncrement As Integer

sSQL = "your recordset"
Set rs = Currendb.OpenRecordset(sSQL)

'Verify Records
If rs.Recordcount > 0 Then

'Initialize trackers
lLastRecord = 0
iIncrement = 0

rs.MoveFirst

'Loop the recordset
While Not rs.EOF

'Check the last record to the current record
If rs.Fields("CompanyID") <> lLastRecord Then
'Reset your Increment
iIncrement = 0
End If

'Update your Increment field
With rs
.Edit
.Fields("Auto Increment Value") = iIncrement
.Update
End With

'Set your LastRecord for the next record to run
lLastRecord = rs.Fields("CompanyID")

rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing



--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[dymondjack]

I forgot to increment iIncrement

'Reset your Increment
iIncrement = 0
End If

iIncrement = iIncrement + 1

'Update your Increment field
With rs
.Edit

iIncrement = iIncrement + 1
Put this line where I've shown it, otherwise you'll just end up with 0's

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[lgray]

Thank you again, Great Gatekeepers of the Code. As usual, you save the
Novices hide.

 

[Tony Toews [MVP]]

Thank you again, Great Gatekeepers of the Code. As usual, you save the
Novices hide.

You're welcome. But note that there are lots of people besides MVPs
who do a good job of answering questions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/