Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generate Sequential Numbers in Table (2003 SP2)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Generate Sequential Numbers in Table (2003 SP2)

    You could put code in the Before Update event of the form used to enter data:
    <code>
    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</code>

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generate Sequential Numbers in Table (2003 SP2

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •