Auto Generate Numbers

[Guest]

I am creating a d/b that will manage my authorizations for customer returns.

On the form is the Customer Information and item Information that is being
returned.

I then need to click a button that would auto generate a RA (return
authorization) number that the customer would use on his shipping box.

Ideally what I would like is a way that the RA number generated is done in a
sequence (Text Char) that outputs DDMMYY-#, e.g customer 1 calls in for a RA
on December 1 and is given number 01122005-1, the next customer calls for an
RA on the same date is given numer 01122005-2..etc.

On Dec 2 2005 the same logic would apply but the output number would be
02122005-1...etc

Any suggestions?

 

[Guest]

Create an RANumber table which contains two columns:
RADate - date/time
RASeq - integer
To generate an RA number, look in the table for the current day.
If it is found, increment RASeq and update the table
If it is not found, change the RADate to todays date and set RASeq to 1.

Dorian

 

[Guest]

I would not create an additional table as mscertified suggested. You don't
really need it. You can use the table where the RA number are housed. A
better approach would be to put put the following code in the Click event of
your button. I will make up table, field, and control name, you will have to
substitute your own real names. One thing I would suggest is that you change
your numbering scheme from 01122005 to 20051201. This will make sorting work
a lot better; otherwise, if you sort by RA number, you would see it like:
01012005
01012006
01022005
01022005
See what I mean.
Also, you will have the same problem with the incremental number if you
don't specify the number of digits and format it accordingly. It would sort
as
1
10
11
12
13
14
15
16
17
18
19
100
....
2
20
2

So I will use a four digit format. You can determine how many digits you
need depending on the return volume. Make it 2 digits more than you think
you will ever need.

Dim strRADate As String
Dim strRANumber As String
Dim varRALast as Variant
'My way
strRADate = Format(date,"yyyymmdd")
'Or your way
strRADate = Format(date,"ddmmyyyy")

varRALast = DMax("[RA_NUMBER]", "tblReturns", "Left([RA_NUMBER], 8) = '" & _
strRADate & "'")
If IsNull(varRaLAst) Then
strRANumber = strRADate & "-0001"
Else
strRANumber = Left(varRALast, 9) & Format(Clng(Right(varRALast, 4) + 1, _
"0000")
End If

Me.txtRANumber = strRANumber.

One other thing to consider if you are in a multiuser environment. If two
users click for a new number, they could both get the same number and when
they try to update the table, the second one to update will get an error.
You may need to do some error handling to increment the number and try again.
Another approach is to update the table as soon as you have the number. The
issue here, however, is if the user does not complete the RA, then you have
an unused number hanging out there.