Generate Sequential Numbers in Table (2003 SP2)

Topic

New Reply

Hello all,

It’s Friday!

I need a way in a table field to advance a number by 1, based on conditions in other fields in the same table. See my attachment example table in Excel..

We are tracking comments made by reviewers and need to assign a number (CmtNo) to each comment based on the following fields:
1. OPLAN
2. MilestoneLU (limited to values A through D)

For example, Given the OPLAN = IF2211, and the MilestoneLU = A, then the table should determine that a comment number ((CmtNo) associated with IF2211 and A already exists, so that the new number would be the maximum in that condition + 1. If none exists, then assign the number 1.

I am thinking that an AutoNumber would not work, since that number in years to come could get pretty big and too unwiedly for what we want to do with that number.

In forms and Reports, we want to be able to concatenate the fields OPLAN, MilestoneLU, and CmtNo to display something like IF2211.C.211

Any ideas how I can do this so that when an engineer enters into a form, values in the fields OPLAN and MilestoneLU I get the “auto-magically”-created-next comment number? Note: In my example, the values in CommentCode-txt were manually entered.

Thanks so much, and
Cheers,
Rich

Reply | Quote

Replies

You could put code in the Before Update event of the form used to enter data:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngNum As Long
If Me.NewRecord Then
lngNum = Nz(DMax("CmtNo", "tblSomething", "OPLAN=" & Chr(34) & _
Me.OPLAN & Chr(34) & " AND MileStoneLU=" & Chr(34) & _
Me.MileStoneLU & Chr(34)), 0) + 1
Me.CmtNo = lngNum
End If
End Sub

Reply | Quote

Hi Hans,

This looks very promising. My user hasn’t yet tried to implement your suggestion in the database development, but is intending to incorporate your code.

Thanks so much,
Cheers,
Rich

Reply | Quote