By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

self generating number

100+
P: 243
i am using the following code to generate a number in a text box on a form when data is entered into the form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.   Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master"), 0) + 1, "0000")
  3. End Sub
what i would like to know is, is there any way that when the new year comes the number will restart at 0001. i do have a year field in my table as it is the first part of our unique ticket number system.. the number would display as
"09-0001" with "09-" being the default value for the year field that i will change every year. hope thats enough info to make sense... any help is greatly appreciated. thanks!
Feb 6 '09 #1
Share this Question
Share on Google+
14 Replies


Expert 100+
P: 1,287
You can use the DMax function's criteria to look for the maximum ticket number for the current year.

Expand|Select|Wrap|Line Numbers
  1. DMax("[ticketnum]", "master", "YearFieldName = '" _
  2. & Format(Date,"yy") & "-'")
Feb 6 '09 #2

100+
P: 243
ok very newb question here...but where would i place that code... and do i have to eliminate the default value in my year field on the table?
Feb 6 '09 #3

ADezii
Expert 5K+
P: 8,597
@didacticone
I just threw this together off the top-of-my-head in work, but it may actually, in fact, work:
  1. Create a Table named tblYear.
  2. This Table will consist of 1 Field only, named [Year] - {LONG}.
  3. Make this Table Hidden, so that it is not Visible to the average User.
  4. Execute the following code wherever appropriate.
    Expand|Select|Wrap|Line Numbers
    1. Dim lngStoredYear As Long
    2.  
    3. lngStoredYear = DLookup("[Year]", "tblYear")
    4.  
    5. If lngStoredYear = Year(Date) Then
    6.   'Year is the same, Increment by 1 and Format
    7.   Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master"), 0) + 1, "0000")
    8. Else    'different Year, Reset TICKETNUM to 0001, Update [Year] in tblYear by 1 Year
    9.   Me![TICKETNUM] = "0001"
    10.   CurrentDb.Execute "Update tblYear Set tblYear.[Year] = " & lngStoredYear + 1, dbFailOnError
    11. End If
  5. Any questions, feel free to ask.
Feb 6 '09 #4

Expert 100+
P: 1,287
Assuming the code you have works, just change
Expand|Select|Wrap|Line Numbers
  1. Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master"), 0) + 1, "0000")
to
Expand|Select|Wrap|Line Numbers
  1. Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master", _
  2.      "YearFieldName = '" & Format(Date,"yy") & "-'"), 0) + 1, "0000")
Feb 6 '09 #5

100+
P: 243
that code gives me a syntax error...
Feb 6 '09 #6

Expert 100+
P: 1,287
Which code and what error?
Feb 6 '09 #7

100+
P: 243
adezii... i tried your way and placed that code in the 'beforeinsert' event of the form and it returns run time error '94': invalid use of null. and it highlights the line:

lngStoredYear = DLookup("[Year]", "tblYear")

any ideas? and thanks to each of you for your help
Feb 6 '09 #8

100+
P: 243
chipr... i used the code you gave me:
Expand|Select|Wrap|Line Numbers
  1. Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master", 
  2. "YearFieldName = '" & Format(Date,"yy") & "-'"), 0) + 1, "0000")
after i paste it in vb it is all red.. and when i change the year field name to my year fields name it returns:

compile error:
expected:line number or label or statement or end of statement

thanks!
Feb 6 '09 #9

Expert 100+
P: 1,287
Is it really two lines? Because if it is, you need to continue it with _ &
along with putting in the correct name of your Year field.
Feb 6 '09 #10

100+
P: 243
ok it worked but, when i changed the year to "10-" in the table as my default value... the form doesnt reset to 1
Feb 6 '09 #11

Expert 100+
P: 1,287
Why would it reset to 1? It's looking up the highest ticket number for the current year in the table and adding 1 to it.
Feb 6 '09 #12

100+
P: 243
so its actually not using the value i input in the year field in my table at all then?
Feb 6 '09 #13

Expert 100+
P: 1,287
No, it's not using your default value for anything here.
Feb 6 '09 #14

100+
P: 243
oh ok thats where i was confused... so now when 2010 begins the number will restart at 0001? that is awesome.. thanks a ton for your help
Feb 6 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.