Automatic generated report no

[Scott]

I need to have a field to generate a non-duplicate report number. The
number format is as below:-

The first part consists of three digits for each year. It could be manual
or automatic changed according to the calendar.

The second part consists of four digits for the report number of the year
and should generate automatically when a record is added.

The third part consists of a single digit for the part of the report of the
report number and should generate automatically.

The last two letters for the initials of the writer.

Can someone share the experience in generate such report no for reference.

Thanks,

Scott

 

[Arvin Meyer [MVP]]

Here's some code I use in a similar situation. You need to also concatenate
the initials at the end:

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003, Revised February 25, 2006
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 03-0001, 03-0002, etc.
' Seed the first number if other than 0000
'********************************************************************
On Error GoTo Error_Handler

Dim intNumber As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by
[CaseNum];")

If Not rs.EOF Then
rs.MoveLast
If Left(rs.Fields("CaseNum"), 2) = CStr(Right(year(Date), 2)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 4)) + 1
Else
intNumber = 1
End If
End If

DateNum = Right(year(Date), 2) & "-" & Format(intNumber, "0000")

With rs
.AddNew
!CaseNum = DateNum
.Update
End With

Exit_Here:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler: 'If someone is editing this record trap the error
Dim intRetry As Integer
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Err.Number, vbOKOnly, "Another user editing this number"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem
Generating Number"
Resume Exit_Here
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Scott]

Arvin,

Thanks for your code. I have created the table, field and module but unsure
how to use initial the code to generate the number format. Could you please
give me further instructions.

Thanks,

Scott

Here's some code I use in a similar situation. You need to also
concatenate
the initials at the end:

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003, Revised February 25, 2006
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 03-0001, 03-0002, etc.
' Seed the first number if other than 0000
'********************************************************************
On Error GoTo Error_Handler

Dim intNumber As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by
[CaseNum];")

If Not rs.EOF Then
rs.MoveLast
If Left(rs.Fields("CaseNum"), 2) = CStr(Right(year(Date), 2)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 4)) + 1
Else
intNumber = 1
End If
End If

DateNum = Right(year(Date), 2) & "-" & Format(intNumber, "0000")

With rs
.AddNew
!CaseNum = DateNum
.Update
End With

Exit_Here:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler: 'If someone is editing this record trap the error
Dim intRetry As Integer
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Err.Number, vbOKOnly, "Another user editing this number"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem
Generating Number"
Resume Exit_Here
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I need to have a field to generate a non-duplicate report number. The
number format is as below:-

The first part consists of three digits for each year. It could be
manual
or automatic changed according to the calendar.

The second part consists of four digits for the report number of the year
and should generate automatically when a record is added.

The third part consists of a single digit for the part of the report of the
report number and should generate automatically.

The last two letters for the initials of the writer.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Arvin Meyer [MVP]]

You can set the default value property to:

=DateNum()

There are other ways, but that's the easiest.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Arvin,

Thanks for your code. I have created the table, field and module but unsure
how to use initial the code to generate the number format. Could you please
give me further instructions.

Thanks,

Scott

Here's some code I use in a similar situation. You need to also
concatenate
the initials at the end:

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003, Revised February 25, 2006
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 03-0001, 03-0002, etc.
' Seed the first number if other than 0000
'********************************************************************
On Error GoTo Error_Handler

Dim intNumber As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by
[CaseNum];")

If Not rs.EOF Then
rs.MoveLast
If Left(rs.Fields("CaseNum"), 2) = CStr(Right(year(Date), 2)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 4)) + 1
Else
intNumber = 1
End If
End If

DateNum = Right(year(Date), 2) & "-" & Format(intNumber, "0000")

With rs
.AddNew
!CaseNum = DateNum
.Update
End With

Exit_Here:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler: 'If someone is editing this record trap the error
Dim intRetry As Integer
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Err.Number, vbOKOnly, "Another user editing this number"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem
Generating Number"
Resume Exit_Here
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I need to have a field to generate a non-duplicate report number. The
number format is as below:-

The first part consists of three digits for each year. It could be
manual
or automatic changed according to the calendar.

The second part consists of four digits for the report number of the year
and should generate automatically when a record is added.

The third part consists of a single digit for the part of the report of the
report number and should generate automatically.

The last two letters for the initials of the writer.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott