Auto generate date with number

[magster06]

Hello all,

I am back with yet another question, lol.

I have a case number that I want to auto generate with the date + number. I also want this case number to be incremented by one when ever a new case is started.

Me.txtCaseNumber = Year(Date) & "-"

this is what I have so far to generate the year: 2013-(I want a number here that should be autogenerated)

I tried Dmax, but it does not seem to work.

 

[pr2-eugin]

You need to create a new column (say autoFieldName) in the table, it should have a default value as 0, (if there are already 10 records in the table, then fill them with 1,2,3..10). In the Form Current Property..

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.txtCaseNumber = Year(Date) & "-" & Nz(DMax("[autoFieldName]", "tableName"), 0) + 1
    End If
End Sub

Hope this helps..

 

[Solo712]

Hello all,

I am back with yet another question, lol.

I have a case number that I want to auto generate with the date + number. I also want this case number to be incremented by one when ever a new case is started.

Me.txtCaseNumber = Year(Date) & "-"

this is what I have so far to generate the year: 2013-(I want a number here that should be autogenerated)

I tried Dmax, but it does not seem to work.

It does work with Dmax, magster. You have to remember though that if you want to generate a unique string (as opposed to a number), you need to zero-fill the variant numeric portion of the string, so that the routine can correctly generate the successors to record number 10, 100, etc, i.e. those that change the length of the string. Try placing this in the Current event. Substitute the name of your own table:

Dim x As Integer, ss As String
If Me.NewRecord Then
  ss = Nz(DMax("txtCaseNumber", "MyTable"), "")
   If ss = "" Then
     Me.txtCaseNumber = Year(Date) & "-001"
   Else
     x = InStr(ss, "-") + 1
     ss = Mid(ss, x, Len(ss))
     x = CInt(ss) + 1
     Me.txtCaseNumber = Year(Date) & "-" & Trim(Right(Str(10 ^ 3 + x), 3))
   End If
End If

The routine will handle 999 records for the year. If you need thousands, change the 10 ^ 3 to 10 ^4 (+ allow 4 spaces for the string) in the last statement and the first record appendix to "-0001".


Best,
Jiri

 

[magster06]

Thanks for the quick response guys!

I have not had a chance to implement the suggestions, but I sure they will work (as they usually do).

 

[magster06]

How would I modify the code to reset to zero at the beginning of each year?

 

[Solo712]

How would I modify the code to reset to zero at the beginning of each year?

Dim x As Long, ss As String
If Me.NewRecord Then
  ss = Nz(DMax("txtCaseNumber", "DateAutoGen"), "")
  If ss = "" Then
    x = 0
  Else
    x = CLng(Left(ss, 4))
  End If
  If x <> Year(Date) Then
    Me.txtCaseNumber = Year(Date) & "-001"
  Else
    x = InStr(ss, "-") + 1
    ss = Mid(ss, x, Len(ss))
    x = CLng(ss) + 1
    Me.txtCaseNumber = Year(Date) & "-" & Trim(Right(Str(10 ^ 3 + x), 3))
  End If
End If

If you really want to start with zero, change the starting counter value to "-000".

Best,
Jiri

 

[magster06]

Thank you Jiri!!

 

[Solo712]

Thank you Jiri!!

You're welcome.

 

[brispark]

Dim x As Long, ss As String
If Me.NewRecord Then
  ss = Nz(DMax("txtCaseNumber", "DateAutoGen"), "")
  If ss = "" Then
    x = 0
  Else
    x = CLng(Left(ss, 4))
  End If
  If x <> Year(Date) Then
    Me.txtCaseNumber = Year(Date) & "-001"
  Else
    x = InStr(ss, "-") + 1
    ss = Mid(ss, x, Len(ss))
    x = CLng(ss) + 1
    Me.txtCaseNumber = Year(Date) & "-" & Trim(Right(Str(10 ^ 3 + x), 3))
  End If
End If

If you really want to start with zero, change the starting counter value to "-000".

Best,
Jiri

Hi, I've tried this but get a compile error on Me.txtCaseNumber = Year(Date) & "-001"

any ideas??