Sequence numbering of documents

[atledreier]

Hello!

I have a problem with my numbering.

I am making a database for document control, and need some pointers.

The system is as follows (and this is set in stone);

All document numbers start with a project code, then originator code,
recipient code, type code, and sequence number
An example of a doc number: FG-AIL-BP-A-0001.
So far it's easy. The problem is that the sequence numbers need to
count each individual combination of projects, recipients and
originators, as well as document types. So we can have a table of
documents looking like this;

PMS-AIL-BP-A-0001
PMS-AIL-BP-MC-0001
PMS-AIL-BP-A-0002
PMS-BP-ABB-VOR-0001

See my problem? What would be a good way to organize this?

So far I have a table with lookups to companies, project codes and
document types, but the sequence numbering has me stumped.

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form where you enter this data
to create this key. (That's the last possible moment before the record is
saved, reducing the chance that 2 users entering records will be given the
same value at the same time.)

The example below checks that there's data in all 4 fields, and builds the
prefix string from them. It then looks up the table for the highest value
used for that prefix. This will only work properly if the remaining
characters are exactly 4 digits (no more, no less, and only 0-9.)

It then take the numeric value of the trailing characters, and makes sure
it's less than 9999. If so, it adds 1, formats with leading zeros, appends
it to the prefix string created above, and assigns the result.

You will need to substitute your own field names. Be sure to put square
brackets around any name that contains a space, e.g. if your field is
[Project Code].

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strPrefix As String
Dim varResult As Variant
Dim lngNum as Long

If Me.NewRecord Then
If IsNull(Me.ProjectCode) Or IsNull(Me.OriginatorCode) Or _
IsNull(Me.RecipientCode) Or IsNull(Me.TypeCode)) Then
Cancel = True
MsgBox "You forgot something!"
Else

strPrefix = Me.ProjectCode & "-" & Me.OriginatorCode & "-" & _
Me.RecipientCode & "-" & Me.TypeCode & "-"
varResult = DMax("DocNumber", "Table1", _
"DocNumber Like """ & strPrefix & "*""")
If Len(varResult) > Len(strPrefix) Then
lngNum = Val(Mid(varResult, Len(strPrefix) + 1)) + 1
Else
lngNum = 1
End If

If lngNum > 9999 Then
Cancel=true
MsgBox "Oops: " & lngNum & " is too high to use."
Else
Me.DocNumber = strPrefix & Format(lngNum, "0000")
End If
End If
End If
End Sub

Note that Access does have a problem matching fields that contain dashes:
http://support.microsoft.com/kb/271661

 

[atledreier]

Thanks alot, I'll try that approach.

Use the BeforeUpdate event procedure of the form where you enter this data
to create this key. (That's the last possible moment before the record is
saved, reducing the chance that 2 users entering records will be given the
same value at the same time.)

The example below checks that there's data in all 4 fields, and builds the
prefix string from them. It then looks up the table for the highest value
used for that prefix. This will only work properly if the remaining
characters are exactly 4 digits (no more, no less, and only 0-9.)

It then take the numeric value of the trailing characters, and makes sure
it's less than 9999. If so, it adds 1, formats with leading zeros, appends
it to the prefix string created above, and assigns the result.

You will need to substitute your own field names. Be sure to put square
brackets around any name that contains a space, e.g. if your field is
[Project Code].

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strPrefix As String
    Dim varResult As Variant
    Dim lngNum as Long

    If Me.NewRecord Then
        If IsNull(Me.ProjectCode) Or IsNull(Me.OriginatorCode) Or_
            IsNull(Me.RecipientCode) Or IsNull(Me.TypeCode)) Then
            Cancel = True
            MsgBox "You forgot something!"
        Else

            strPrefix = Me.ProjectCode & "-" & Me.OriginatorCode & "-" & _
                Me.RecipientCode & "-" & Me.TypeCode & "-"
            varResult = DMax("DocNumber", "Table1", _
                "DocNumber Like """ & strPrefix & "*""")
            If Len(varResult) > Len(strPrefix) Then
                lngNum = Val(Mid(varResult, Len(strPrefix) + 1)) + 1
            Else
                lngNum = 1
            End If

            If lngNum > 9999 Then
                Cancel=true
                MsgBox "Oops: " & lngNum & " is too high to use."
            Else
                Me.DocNumber = strPrefix & Format(lngNum, "0000")
            End If
        End If
    End If
End Sub

Note that Access does have a problem matching fields that contain dashes:
   http://support.microsoft.com/kb/271661

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a problem with my numbering.

I am making a database for document control, and need some pointers.

The system is as follows (and this is set in stone);

All document numbers start with a project code, then originator code,
recipient code, type code, and sequence number
An example of a doc number: FG-AIL-BP-A-0001.
So far it's easy. The problem is that the sequence numbers need to
count each individual combination of projects, recipients and
originators, as well as document types. So we can have a table of
documents looking like this;

See my problem? What would be a good way to organize this?

So far I have a table with lookups to companies, project codes and
document types, but the sequence numbering has me stumped.