Auto update year in DMax 1

[bangsmic]

I have a field in which I am using =DMax("[fieldname]","tablename&quot+1 to automatically increment the # displayed in the field. (autonumber substitute) What I would really like the field to do is: 02-####. Where 02- equals the last two digits of the current year and #### equals the auto incrementing number. I know I can manually set the Format property to "02-" to accomplish this for this year, but how do get the program to update the year on New Years Day to "03-" and thereafter??

 

[sbohman]

Make sure the field datatype is string then use the datepart function to get the year, and the right function to get the last 2 digits.

dim strYear as string

strYear = Right(DatePart("yyyy",Now()),2) 

yourVariable = stryear & "-" & DMax("[fieldname]","tablename")+1

 

[bangsmic]

can't seem to get this working. What goes in in "Default" row of the field properties in place of =DMax("[fieldname]","tablename&quot+1?

 

[sbohman]

Try putting everything together in on one line like this:

=Right(DatePart("yyyy",Now()),2) & "-" & DMax("[fieldname]","tablename")+1

This will work as the default property for a text box on a form. It does not work as the default property of a field on a table.

 

[bangsmic]

I need the number 02-#### to be saved as part of the record in the table so I did the following:

created an unbound, non-visible text box with the above DatePart code in the Default row property.

created a bound, non-visible text box with the DMax code in the Default row property.

created a bound, visible text box with the following in the Default row property:

=[unboundtextbox]&[boundtextbox]

This pulls the numbers from the above two boxes and combines them in this one which is saved as part of the record. This seems to works fine.

 

[votegop]

Bangsmic:

Won't you have a problem in 2003 if you want your invoice#/record#/whatever# to re-set (for example, 2003-0001, 2003-0002, 2003-0003...)

What you will get at the turn of the year is something like 2002-0192, 2002-0193, 2003-0194, 2003-0195...

Is this what you were after?

 

[bangsmic]

You are correct. I was overruled by "others" and autonumber was was used over DMax. At this time, the number will continue on when the year changes over. I am curious however, how to reset the numer to 1 upon the year change. At a minimum, using DMax would have unable us to reset the number manually. ( 02-1653 ) to ( 03-0001)

BTW, the method being used (autonumber) is set up like the example from sbohman above.

 

[BillPower]

You would find that your program will run faster if you create a new table, add a field called i.e. InvoiceNo.

Run an update query to Increment InvoiceNo + 1 in the new table, using DLookup or DMax etc., on a single record makes more sense, then follow sbohman's advice above.

You will also be able to reset the number back to zero when the year changes or whenever you need to.

Regards

Bill Power

 

[votegop]

Ok, here's a shot at Bill's and Sbohman's ideas, taking it all the way to a reset in the invoice # when the year rolls over.

Given: Current form/recordset has a field called "INumber" (which should be the Invoice Number in this format: 2002-0195, 2002-0196...)

Create 2 new tables:
tblDateLastReset
Field: fldDate
the only record in that table: "01-01-2002"
tblInvoiceNumber
Field: fldNumber
the only record in that table: "0"

Private Sub INumber_Enter()
Dim rsta As Recordset
Dim rstb As Recordset
Dim DateNow
Dim strYear As String
Dim i As Long
Set rsta = CurrentDb.OpenRecordset("tblDateLastReset", dbOpenDynaset)
Set rstb = CurrentDb.OpenRecordset("tblInvoiceNumber", dbOpenDynaset)
DateNow = Date
strYear = Right(DatePart("yyyy", Now()), 4)
If IsNull(INumber.Value) Then
If DateNow > rsta![fldDate] + 365 Then
rsta![fldDate] = rsta![fldDate] + 365
rstb![fldNumber] = 0
Else
End If
Me![INumber] = strYear & "-" & DMax "[fldNumber]", "tblInvoiceNumber&quot + 1
rstb![fldNumber] = rstb![fldNumber] + 1
Else
End If
End Sub

This code does NOT work...but I think I'm close. It hangs on the 4th to last line (rstb!...) where error says:"Update or Cancel Update without AddNew or Edit"

Can someone jump in and help??!!

 

[votegop]

Oops. Sorry Bangsmic. I keep using my invoice # naming convention (2002-1234) instead of your 02-1234. The point is the same either way...

 

[bangsmic]

I think this line should read:

Me![INumber] = strYear & "-" & DMax "[fldNumber]","tblInvoiceNumber&quot + 1

Me![INumber] = strYear & "-" & DMax ("[fldNumber]","tblInvoiceNumber&quot + 1

 

[votegop]

Posting the following just for posterity:

The code now works. The only drawback is that you can get unwanted gaps in the numbering sequence. For example, if you create a new record and the code generates, say, Case Number 2003-04, you then delete that record and create a new one, the new one will be Case Number 2003-05. It does not recognize that 2003-04 was deleted. There are other ways to accomplish this (using DMAX) where the leading case number could be re-used if it is deleted.

The working code:

Private Sub Case_Number_Enter()
Dim rsta As Recordset
Dim rstb As Recordset
Dim DateNow
Dim strYear As String
Set rsta = CurrentDb.OpenRecordset("CECaseNumLastResetDate", dbOpenDynaset)
Set rstb = CurrentDb.OpenRecordset("CECaseNum", dbOpenDynaset)
rsta.Edit
rstb.Edit
DateNow = Date
strYear = Right(DatePart("yyyy", Now()), 4)
If IsNull(Case_Number.Value) Then
If DateNow > rsta![fldlastResetDate] + 365 Then
rsta![fldlastResetDate] = rsta![fldlastResetDate] + 365
rstb![fldCaseNum] = 0
rstb.Update
rsta.Update
Else
End If
Me![Case Number] = strYear & "-" & DMax("fldCaseNum", "CECaseNum&quot + 1
rstb.Edit
rstb![fldCaseNum] = rstb![fldCaseNum] + 1
rstb.Update
Else
End If
rsta.Close
rstb.Close
End Sub