Assigning sequential #'s to record entries in related tables

[BPeters]

Using MS Access 2003, I have two related tables based on ID fields in both.
For the secondary table, I've created a field called "Counter" were I want
each entry sequentially numbered (i.e. 1, 2, etc) by ID. For example, if the
ID for the secondary table is 1 and there are 4 entries tied to this ID, the
"Counter" field would be numbered 1, 2, 3, 4 and restart back at 1 when the
next ID field is encountered. I'm not that advanced a programmer. Below is
what I've come up with which works for the overall table. I can't figure out
how to get it to start renumbering after a new ID # is encountered. Any help
is appreciated.

Public Function AssignSequenceNumber()
On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer
Dim sSQL As String

sSQL = "SELECT tblParcelIDs.intID, tblParcelIDs.intParcelCount FROM
tblLogBook LEFT JOIN tblParcelIDs ON tblLogBook.idsID = tblParcelIDs.intID
ORDER BY tblParcelIDs.intID"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("intParcelCount") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function

Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

 

[Marshall Barton]

Using MS Access 2003, I have two related tables based on ID fields in both.
For the secondary table, I've created a field called "Counter" were I want
each entry sequentially numbered (i.e. 1, 2, etc) by ID. For example, if the
ID for the secondary table is 1 and there are 4 entries tied to this ID, the
"Counter" field would be numbered 1, 2, 3, 4 and restart back at 1 when the
next ID field is encountered. I'm not that advanced a programmer. Below is
what I've come up with which works for the overall table. I can't figure out
how to get it to start renumbering after a new ID # is encountered. Any help
is appreciated.

Public Function AssignSequenceNumber()
On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer
Dim sSQL As String

Dim lngRecID As Long

sSQL = "SELECT tblParcelIDs.intID, tblParcelIDs.intParcelCount FROM
tblLogBook LEFT JOIN tblParcelIDs ON tblLogBook.idsID = tblParcelIDs.intID
ORDER BY tblParcelIDs.intID"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF

If rs!intID <> lngRecID Then
lngCounter = 0
lngRecID = rs!intID
End Uf

rs.Edit
lngCounter = lngCounter + 1
rs.Fields("intParcelCount") = lngCounter
rs.Update
rs.MoveNext
Loop

[snip]

I don't understand why the record set Joins two tables. It
looks like all you want is table tblParcelIDs

 

[BPeters]

Yea, your right. I only need the tblParcelIDs table.

It works great! I really appreciate your help.

Using MS Access 2003, I have two related tables based on ID fields in both.
For the secondary table, I've created a field called "Counter" were I want
each entry sequentially numbered (i.e. 1, 2, etc) by ID. For example, if the
ID for the secondary table is 1 and there are 4 entries tied to this ID, the
"Counter" field would be numbered 1, 2, 3, 4 and restart back at 1 when the
next ID field is encountered. I'm not that advanced a programmer. Below is
what I've come up with which works for the overall table. I can't figure out
how to get it to start renumbering after a new ID # is encountered. Any help
is appreciated.

Public Function AssignSequenceNumber()
On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer
Dim sSQL As String

Dim lngRecID As Long

sSQL = "SELECT tblParcelIDs.intID, tblParcelIDs.intParcelCount FROM
tblLogBook LEFT JOIN tblParcelIDs ON tblLogBook.idsID = tblParcelIDs.intID
ORDER BY tblParcelIDs.intID"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF

If rs!intID <> lngRecID Then
lngCounter = 0
lngRecID = rs!intID
End Uf

rs.Edit
lngCounter = lngCounter + 1
rs.Fields("intParcelCount") = lngCounter
rs.Update
rs.MoveNext
Loop

[snip]

I don't understand why the record set Joins two tables. It
looks like all you want is table tblParcelIDs