Auto generatered number

  • Thread starter Thread starter MyMel
  • Start date Start date
M

MyMel

Note: I posted this question on a old post and I'm not sure if it went out to
the community or is just attached to the old post. So if it went out to the
community please forgiven me for doubling posting a question.

Hi,

I am trying to use this code that has been so graciously provided but due to
lack of experience I am having THE MOST frustrating time getting it to work
consistently for me. Would someone please give me a hand.

I have taken the code and placed it in a module and I call the DateNum
function using a button with click event procedure.

Private Sub CreateCntlNum_Click()
DateNum
End Sub

I want the unique id created(09-0001) and a message box to popup stating
that this number has been created. Sometimes the number is created sometime
its not but the only way I can tell it is to look on the actual
table(Request_Log) it's not showing on the form field (Control_Number). No
error message is occuring. Also it keeps creating the same number(09-0001).
Any and all assistance is greatly appreciated. Thank you.

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 [Control_Number] from [Request_Log] order
by [Control_Number];")

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

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

With rs
.AddNew
!Control_Number = 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
 
This post has been responded to via another posts the link below. Thank you
Arvin.

Under post "Help with auto numbers in Access"

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

MyMel said:
Note: I posted this question on a old post and I'm not sure if it went out to
the community or is just attached to the old post. So if it went out to the
community please forgiven me for doubling posting a question.

Hi,

I am trying to use this code that has been so graciously provided but due to
lack of experience I am having THE MOST frustrating time getting it to work
consistently for me. Would someone please give me a hand.

I have taken the code and placed it in a module and I call the DateNum
function using a button with click event procedure.

Private Sub CreateCntlNum_Click()
DateNum
End Sub

I want the unique id created(09-0001) and a message box to popup stating
that this number has been created. Sometimes the number is created sometime
its not but the only way I can tell it is to look on the actual
table(Request_Log) it's not showing on the form field (Control_Number). No
error message is occuring. Also it keeps creating the same number(09-0001).
Any and all assistance is greatly appreciated. Thank you.

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 [Control_Number] from [Request_Log] order
by [Control_Number];")

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

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

With rs
.AddNew
!Control_Number = 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
Click to expand...
 
Back
Top