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