How do I? (Access Tabe Sequencing/VBA/Array question)

  • Thread starter Thread starter parantaja
  • Start date Start date
P

parantaja

Here's what I'm trying to do. I have a table where data in column A is
repeated and column B records sequencing such that if the first unique item
in column A has 10 entries, column B has a corresponding 1...10, and the
sequence starts over for the next unique item in column A.

To clarify:

Column A Column B
Item 1 1
Item 1 2
. . .
Item 1 10
Item B 1
Item B 2
(etc.)

My thought is that the table colube be loaded into an array in vba and I
could populate the sequencing column using loops and counters. But, I don't
know enough about VBA to even get started.

Any suggestions?
 
Pehaps not the most elegant approach, but this will give you the desired
outcome

Private Sub cmdUpdateSeq_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intI As Integer

Set db = CurrentDb()

strSQL = "SELECT columnA, columnB " & _
"FROM table1 " & _
"ORDER BY columnA;"

Set rs = db.OpenRecordset(strSQL)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
intI = 1
strColACurrent = !ColumnA
strColANext = !ColumnA
Do While strColACurrent = strColANext And Not .EOF
.Edit
!ColumnB = intI
.Update
intI = intI + 1
.MoveNext
If Not .EOF Then
strColANext = !ColumnA
End If
Loop
Loop
End If
End With

End Sub
 
parantaja said:
Here's what I'm trying to do. I have a table where data in column A is
repeated and column B records sequencing such that if the first unique item
in column A has 10 entries, column B has a corresponding 1...10, and the
sequence starts over for the next unique item in column A.

To clarify:

Column A Column B
Item 1 1
Item 1 2
. . .
Item 1 10
Item B 1
Item B 2
(etc.)

My thought is that the table colube be loaded into an array in vba and I
could populate the sequencing column using loops and counters. But, I don't
know enough about VBA to even get started.
Click to expand...

Using a VBA array would be a roundabout, and slow way to do
this.

If your table already has the sequence column correctly
filled in, then the problem is how to generate the next
sequence number for a new record. In this case you can use
the data entry form's BeforeUpdate event to get the maximum
sequence number, add 1 to it and put that into the
corresponding field:

If Me.NewRecord Then
Me.colB = Nz(DMax("colB", "thetable", _
"colA=" & Me.ColA),0)+1
End If

If you are asking how to put acceptable sequence numbers in
the entire colB for the first time, then I suggest using a
recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngSeqNo As Long
Dim strColA As String

strSQL = "SELECT colA,colB FROM thetable " _
& "ORDER BY colA, colC"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
If rs!colA <> strColA Then
lngSeqNo = 0
strColA = rs!colA
End If
lngSeqNo = lngSeqNo +1
rs.Edit
rs!colB = lngSeqNo
rs.Update
rs.MoveNext
Loop

Note that the colC above is important to determine the order
of the sequence numbers for an item. If it takes more than
one field to determine the orider, then add those columns to
the ORDER BY clause. If you don't care what order an item's
records are sequenced, then you only need to specify colA.
 
Back
Top