Weird auto number generating requirement

[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!

 

[Klatuu]

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

 

[tina]

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

 

[AJ]

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"

 

[Klatuu]

That is just a made up name. It should be the name of the table where you
store your RMA numbers.

 

[AJ]

Thanks! I figured that out, finally. It works like a charm!