Weird auto number generating requirement

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

Hi, I've asked this before, and I know its not the best thing to do,
but unfortunately, I have to be able to generate an auto number
sequence using the following format:

I have a form with a blank textbox [RMA Number]. I also have a button
with which I want to generate a unique, sequential number in the
textbox.

The tricky part is that the format of the number is a value that must
follow this format:

05K025

Where "05" is the current two digit year, "K" is the current month
represented by a letter, eg: January = A, Feb = B..etc. and "025" is a
number that increases sequentially with each new record.

What's more, I need that "025" to reset to 001 when a new month starts,
eg:

05L01 = first RMA issued during the month of december, 2005.

I'm thinking I need to implement code like this for the month:

Function DateDiffM(BegDate, EndDate)
Const JANUARY = A
Const DECEMBER = L
Dim NumWeeks As String

But I'm not sure what to do with it all. It's got me stumped. Anbody
have any revelations?

Thanks!
 
This is not really an Autonumber field, but here is how you do what you want:

Dim strFind as String
Dim varLastRMA as Variant
Dim strNextRMA as String

strFind = Format(Year(Date),"yy") & _
Choose(Month(Date()),"A","B","C","D","E","F","G","H","I","J","K","L")
varLastRMA = DMax("[RMA_NUMBER]", "tblRMA", _
"Left([RMA_NUMBER], 3) = '" & strFind & "'")
If IsNull(varLastRMA) Then
strNextRMA = strFind & "001"
Else
strNextRMA = strFind & Format(Clng(Right(varLastRMA,3)) + 1, "000")
End If
 
well, if you're asking specifically about code to return the correct letter
for the current month, try

Dim bytMonth As Byte, strMonth As String

bytMonth = Month(Date)
strMonth = Choose(bytMonth,"A", "B", "C", "D", _
"E", "F", "G", "H", "I", "J", "K", "L")

hth
 
I can follow everything except the "tblRMA",_ Is that a reference to
the database? What should be there as right now it says it cant' find
the input field "tblRMA"
 
That is just a made up name. It should be the name of the table where you
store your RMA numbers.
 
Back
Top