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
"lgray" wrote:
> 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
Similar Threads
