self generating number

[didacticone]

i am using the following code to generate a number in a text box on a form when data is entered into the form:

Private Sub Form_BeforeInsert(Cancel As Integer)
  Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master"), 0) + 1, "0000")
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!

[ChipR]

You can use the DMax function's criteria to look for the maximum ticket number for the current year.

DMax("[ticketnum]", "master", "YearFieldName = '" _
& Format(Date,"yy") & "-'")

[didacticone]

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?

[ADezii]

@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.
   

   Dim lngStoredYear As Long
    
   lngStoredYear = DLookup("[Year]", "tblYear")
    
   If lngStoredYear = Year(Date) Then
     'Year is the same, Increment by 1 and Format
     Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master"), 0) + 1, "0000")
   Else    'different Year, Reset TICKETNUM to 0001, Update [Year] in tblYear by 1 Year
     Me![TICKETNUM] = "0001"
     CurrentDb.Execute "Update tblYear Set tblYear.[Year] = " & lngStoredYear + 1, dbFailOnError
   End If

5. Any questions, feel free to ask.

[ChipR]

Assuming the code you have works, just change

Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master"), 0) + 1, "0000")

to

Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master", _
     "YearFieldName = '" & Format(Date,"yy") & "-'"), 0) + 1, "0000")

[didacticone]

that code gives me a syntax error...

[ChipR]

Which code and what error?

[didacticone]

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

[didacticone]

chipr... i used the code you gave me:

Me![TICKETNUM] = Format(Nz(DMax("[ticketnum]", "master", 
"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!

[ChipR]

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.

[didacticone]

ok it worked but, when i changed the year to "10-" in the table as my default value... the form doesnt reset to 1

[ChipR]

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.

[didacticone]

so its actually not using the value i input in the year field in my table at all then?

[ChipR]

No, it's not using your default value for anything here.

[didacticone]

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