Generate order number in VBA? reset counter at begining of month? 1

[CompAnalyst]

Ok... so I am trying to figure out the code to create an intelligent "order number" which would be in the format of "MMYY-0001" which would reset the "0001" back to one at the start of the next month. The way i was going to go about doing this was to basically use the today function and cull out a string for the MMYY part but i can't figure out how to make a counter within the code (and also have the counter reset on a new month). I would asume this would be some sort of record count, but i need HEEEEELP thanks any and all who can help.
Rachel

 

[cmmrfrds]

Why not just use a date? For example yymmddhhmmss

 

[flyover789]

assuming you have a table tbl_orders with a field ordernbr, and a form bound to that table, where you enter your orders, and on the form is a textbox with the ordernbr and a button to add a new record ... the following code in the on_click event of the mentioned button accomplishes your task:

Private Sub ctl_newrec_Click()
Dim syymm As String
syymm = Format(Now(), "yy") & Format(Now(), "mm")
Dim snbr As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Application.CurrentDb
Set rs = db.OpenRecordset("select max(right(t.ordernbr,4)) from tbl_orders t " & _
"where t.ordernbr like '" & syymm & "*';")
rs.MoveLast

DoCmd.GoToRecord , , acNewRec

If IsNull(rs.Fields(0).Value) Then
snbr = "0001"
Else:
snbr = Format(rs.Fields(0).Value + 1, "0000")
End If

Me.txt_ordernbr = syymm & "-" & snbr
Me.Refresh

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[TheAceMan1]

How are ya CompAnalyst . . . . .

Have you considered a sort on this field?

It surely will not be sequential top to bottom.

See Ya! . . . . . .

 

[CompAnalyst]

Thank you FlyOver i will try that out.

I am trying to figure this out as a favor to someone else

TheAceMan, CompAnalyst refers to Compensation Analyst. No need to be rude

 

[scriverb]

I recently posted a response to a similar thread. See the response:

thread702-827975

Try using something like this as the Control Source for your Order Number field control:

=IIF( Format(Month(Date()),"00") & Format(Year(Date()),"00") = Mid$( DMax("[Order Number]","[yourtablename]"),1,4),Mid$(DMax("[Order Number]","[yourtablename]")),1,4) & "-" & Format(CInt(Mid$(DMax("[Order Number]","[yourtablename]"),6,4))+1,"0000"), Format(Month(Date()),"00") & Format(Year(Date()),"00") & "-0001")

This is pseudo code but I think I have it right. If you have any errors just post back and I will create a table and test it out here.





Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[CompAnalyst]

Thank you both for supplying the code for me i have passed it on and i will let u know if i need additional guidance. Thank u again!
Rachel

 

[cgarts]

I'd wonder about glomming a date and a sequence number together - I just don't see any benefit that a simpler sceme can't provide and provide better.

If you have a date field in your data, which I'd urge, then why have unneeded redundancy in your key? In general, any redundancy whatsoever is kinda a red flag for me. It at least gets me looking hard.

If someone might ever be tempted to change the data - like in your instance - what if someone discovers that a transaction actually took place at 12:00:01 the next day or whatever, and decides to start fiddling with the primary key - that's bad.

If you are working with a large or complex Db and you have good reason - then generate your own primary keys, but an awful lot of the time, an autonumber or the like - totally hidden and protected from the user will make data honest and will make searching and sorting and finding and maintenance easy.

Any data you wish to be able to work with, like what month an order came from, or day or year, or how many orders hapened in a month, or in what sequence, all are readily available from a table including:

OrderID Unique Number / Autonumber or equivalent
OrderDate Date and Time

I would say identifiers are best if they are:
- Persistent and unchanging
- Non-recyclable
- Unique
- Meaningless
- Hidden from the users
- Simple

You can easily show what month an order came from, and how many records are dated between the beginning of the month and the moment of your current order. On this one why reinvent the wheel?

Best,

C

 

[scriverb]

CompAnalyst: I have modified my code from my previous posting. This does require you to create an AutoNumber field called RecNumber. This number is only used to seek out the last record entered. Put this code in the AfterInsert event procedure of the Form with the updates to the table and field names.

Me.OrderNumber = IIf(Format(Month(Date), "00") & Mid$(Format(Year(Date), "00"), 3, 2) = Mid$(DLookup("[OrderNumber]", "[tblOrders]", "[RecNumber] = " & DMax("[RecNumber]", "[tblOrders]")), 1, 4), Mid$(DLookup("[OrderNumber]", "[tblOrders]", "[RecNumber] = " & DMax("[RecNumber]", "[tblOrders]")), 1, 4) & "-" & Format(CInt(Mid$(DLookup("[OrderNumber]", "[tblOrders]", "[RecNumber] = " & DMax("[RecNumber]", "[tblOrders]")), 6, 3)) + 1, "000"), Format(Month(Date), "00") & Mid$(Format(Year(Date), "00"), 3, 2) & "-001")
DoCmd.RunCommand acCmdSaveRecord

This does not completely solve your problem though. If you allow your users to delete records then you will have gaps.

This is just one way of doing this as is the function posted by flyover789. Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[CompAnalyst]

thank you Bob! I passed on the information... again i really appreciate the assistance
Rachel