[AccessD] Assign Document Numbers

So you must have a table somewhere which has these document numbers in it,
Yes? Let's Call it DocumentList_tbl for this discussion, and let's assume
the document number field in it is called DocNum, and that the document
number you want to check ("DC-2101" etc) is in a variable called strPrefix

So, in your form's berforeupdate event (I'd suggest) you need to do
something like this...

strNewDoc = Nz(Dmax("DocNum","DocumentList_tbl ","Left(DocNum,7)='" &
strPrefix & "'"),strPrefix)
If strNewDoc = strPrefix Then
   ' did not find the document in the table
    strNewDoc = strNewDoc & "-0001"
Else
   strNewDoc = strNewDoc & "-" & Format(Eval(Right(strNewDoc,4) ) +
1,"0000")
End If

The important thing is not to assign this document number until the last
possible moment, i.e. just before you save the record so that two users will
not wind up assigning the same number because of a delay in saving the
record.

Lambert

> -----Original Message-----
> From:	Hollis,Virginia [SMTP:HollisVJ at pgdp.usec.com]
> Sent:	Wednesday, July 09, 2003 11:52 AM
> To:	'accessD at databaseadvisors.com'
> Subject:	[AccessD] Assign Document Numbers
> 
> I've created a database for use by a group of individuals (perhaps 20
> users) in which the user will open a form and place a request for a
> document number. I need a document number to be automatically assigned at
> that time.
>  
> An example of document numbers and their scheme are as follows: 
>  
> DC-2101-0001
> DC-2204-0001
> DC-2301-0001
> LA-2605-0001
> LA-2605-0002
> LA-2605-0003
> NR-2605-0001
>  
> It's the last set of numbers (0001, 0002, 0003) that I wish to have
> assigned according to which first two sets (DC) and (2101) are input by
> the user. Like if a user inputs "DC" and "2101"....I'd like the system to
> automatically assign the next available number which would be "0002".
> Therefore the document number assigned would be "DC-2101-0002".
>  
> Also, the bad thing for me is that we have already been using this
> database (just one individual) and manually assigning these numbers. I
> don't know how it would work out if we suddenly put the code in. Would it
> acknowledge previous entries and assign the correct number?
>  
> If someone can help me with this, I'd be eternally grateful.
>  
> Virginia << File: ATT7929573.txt >>