For Arvin Meyer Autonumber code from July 2003

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the function). Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
 
You need to change how you retrieve intNumber from the recordset.

If Right(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Left(rs.Fields("CaseNum"),
Len(rs.Fields("CaseNum")) - 4)) + 1
Else
intNumber = 1
End If

However, it's not going to work. Since the number is a string, it won't sort
in the correct order, so you can't be guaranteed that you'll get the highest
number already used. You'll find that they'll sort 102003, 112003, 122003,
12003, 22003, 32003, 42003, 52003, ....

Much better is to store them as two separate fields, and just concatenate
them in a query.

--
Doug Steele, Microsoft Access MVP



Tony Williams said:
Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the function). Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
Click to expand...
 
Thanks Douglas, obviously I don't want that. I can get the date from a
control I have called DateCreatedtxt but I want to use the element of the
code for the sequential number. Is that possible? If so do I just strip out
any reference to the date?
Thanks
Tony
Douglas J. Steele said:
You need to change how you retrieve intNumber from the recordset.

If Right(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Left(rs.Fields("CaseNum"),
Len(rs.Fields("CaseNum")) - 4)) + 1
Else
intNumber = 1
End If

However, it's not going to work. Since the number is a string, it won't sort
in the correct order, so you can't be guaranteed that you'll get the highest
number already used. You'll find that they'll sort 102003, 112003, 122003,
12003, 22003, 32003, 42003, 52003, ....

Much better is to store them as two separate fields, and just concatenate
them in a query.

--
Doug Steele, Microsoft Access MVP



Tony Williams said:
Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the function). Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
Click to expand...
Click to expand...
 
Tony, That sounds rude.
What is obvious to you may not be so to someone who does not have all the
details.
Compare the two below...
Thanks Douglas, obviously I don't want that. I can get the date from ...
Thanks Douglas, I don't want that. I can get the date from ...
Click to expand...


I'd like to answer your question, but your original post is lost.
I'd suggest creating a separate table to hold the NextNumber, and can be
used to provide the next number for any table that you need.
The structure of the table could be
Domain - Text 50
Year - number
NextNumber integer
SampleData:
Table1, 2003, 1234
Table1, 2004, 45
Table2, 2003, 44
Table2, 2004, 1

The select statement returns Zero or one rows.
This can be a better solution because you do not have to retrieve the entire
recordset, movelast etc

If you need more assistance, post your original requirement again

HS



Tony Williams said:
Thanks Douglas, obviously I don't want that. I can get the date from a
control I have called DateCreatedtxt but I want to use the element of the
code for the sequential number. Is that possible? If so do I just strip out
any reference to the date?
Thanks
Tony
Douglas J. Steele said:
You need to change how you retrieve intNumber from the recordset.

If Right(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Left(rs.Fields("CaseNum"),
Len(rs.Fields("CaseNum")) - 4)) + 1
Else
intNumber = 1
End If

However, it's not going to work. Since the number is a string, it won't sort
in the correct order, so you can't be guaranteed that you'll get the highest
number already used. You'll find that they'll sort 102003, 112003, 122003,
12003, 22003, 32003, 42003, 52003, ....

Much better is to store them as two separate fields, and just concatenate
them in a query.

--
Doug Steele, Microsoft Access MVP



Tony Williams said:
Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the
Click to expand...
function).
Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
Click to expand...
Click to expand...
Click to expand...
 
Sorry HS the last thing I want to be when someone is helping me is to be
rude but I stand admonished, when you're struggling with a problem the words
just pour out without thinking.
What I am trying to do is populate a control called CommDocNbrtxt with a
number made up of a sequential number followed by the year eg 103,203,303
and so on and it changes with the year and reverts back to 1 so 104,204,304
and so on
Arvin Meyer post some code which is below but I can't get it to work
here is my previous post
"Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the function). Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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"

The point that Douglas raised about the sorting I hadn't realised so I was
thinking of an alternative solution.
Hope all that makes sense, it's driving me mad
Tony
HSalim said:
Tony, That sounds rude.
What is obvious to you may not be so to someone who does not have all the
details.
Compare the two below...
Thanks Douglas, obviously I don't want that. I can get the date from ...
Thanks Douglas, I don't want that. I can get the date from ...
Click to expand...


I'd like to answer your question, but your original post is lost.
I'd suggest creating a separate table to hold the NextNumber, and can be
used to provide the next number for any table that you need.
The structure of the table could be
Domain - Text 50
Year - number
NextNumber integer
SampleData:
Table1, 2003, 1234
Table1, 2004, 45
Table2, 2003, 44
Table2, 2004, 1

The select statement returns Zero or one rows.
This can be a better solution because you do not have to retrieve the entire
recordset, movelast etc

If you need more assistance, post your original requirement again

HS



Tony Williams said:
Thanks Douglas, obviously I don't want that. I can get the date from a
control I have called DateCreatedtxt but I want to use the element of the
code for the sequential number. Is that possible? If so do I just strip out
any reference to the date?
Thanks
Tony
Douglas J. Steele said:
You need to change how you retrieve intNumber from the recordset.

If Right(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Left(rs.Fields("CaseNum"),
Len(rs.Fields("CaseNum")) - 4)) + 1
Else
intNumber = 1
End If

However, it's not going to work. Since the number is a string, it
Click to expand...
won't
sort
in the correct order, so you can't be guaranteed that you'll get the highest
number already used. You'll find that they'll sort 102003, 112003, 122003,
12003, 22003, 32003, 42003, 52003, ....

Much better is to store them as two separate fields, and just concatenate
them in a query.

--
Doug Steele, Microsoft Access MVP



Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table
names
to fit your data. You can put it in the form module or in a standard
module
(but don't name the standard module with the same name as the function).
Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
Click to expand...
Click to expand...
Click to expand...
 
The apology should be addressed to Doug. Nice of you to offer one.

There are a couple of approaches you can take - so tell me again:
Do you need an autonumbering for just one table? or many tables?
Do you need to be able to generate sequences for more than one year?
as in 2004,1... AND 2003,4506...

I'll build the code based on your response

HS
 
Thanks HS have apologised to Douglas
Yes just one table for a control called CommDocNbrtxt in the table called
tblDocuments. The sequence starts with 1 for each year and the format of the
number should be 103,203,303,403 and so on for 2003 and on 1.1.04 changes to
104,204,304 and so on. I would be happy if you think a 4 digit year would be
more desirable so 12003,22003 and so on
Any help would be appreciated
Tony
 
Doug sorry if that response sounded rude wasn't meant to be got so hung up
with my problem didn't realise what I'd said
Thanks
Tony
Douglas J. Steele said:
You need to change how you retrieve intNumber from the recordset.

If Right(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Left(rs.Fields("CaseNum"),
Len(rs.Fields("CaseNum")) - 4)) + 1
Else
intNumber = 1
End If

However, it's not going to work. Since the number is a string, it won't sort
in the correct order, so you can't be guaranteed that you'll get the highest
number already used. You'll find that they'll sort 102003, 112003, 122003,
12003, 22003, 32003, 42003, 52003, ....

Much better is to store them as two separate fields, and just concatenate
them in a query.

--
Doug Steele, Microsoft Access MVP



Tony Williams said:
Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the function). Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
Click to expand...
Click to expand...
 
Tony,
I like to build reusable functions so here it is. Yes, I have used a 4
digit year, but you could just as well use it with a two-digit year.
I have also padded it with 5 Zeros in front - this will help your sorting
See comments in function on usage examples

HS



'-----------------Begin Code -------------------------
Function NextNumber(ByVal Domain As String, ByVal Qualifier As Integer) As
String
'********************************************************************
' Name: NextNumber
' Purpose: General purpose function to Generate an incremental "number"
' based on the Table name and secondary qualification such as year
' Author: Habib Salim
' Date: October 2, 2003
' Dependency: Table: NextNumbers
' Columns: Domain Text(50), Qualifier Text(50), NextNum
Long Integer
' PrimaryKey: Domain, Qualifier
' Comment: Generates new number in the format Zero-Padded NextNumber &
Qualifier
' in the format of 000012003, 000022003, 000012004
' This function will seed the table if a number doesnot already
exist for
' given criteria of Domain and Qualifier
' Arguments are trimmed inside function to prevent
' seeding because of extraneous spaces
' Use this feature with caution or comment it out
' Usage: NextNumber("SomeTable", "2003") - returns 000012003
' NextNumber("SomeTable", cstr(year(date)))
'********************************************************************


On Error GoTo NextNumber_Err:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngNextNum As Long
Dim ssql As String

'REmove unwanted spaces
Domain = Trim(Domain)
Qualifier = Trim(Qualifier)


ssql = "Select Domain, Qualifier, NextNum from NextNumbers where Domain =
'"
ssql = ssql & Domain & "' AND Qualifier = " & Qualifier
Set db = CurrentDb
Set rst = db.OpenRecordset(ssql)

' capture nextnum, and write the next number for next use

If (rst.EOF And rst.BOF) Then
' new criteria - i.e. new domain and/or qualifier
lngNextNum = 1
With rst
.AddNew
!Domain = Domain
!Qualifier = Qualifier
!nextnum = lngNextNum + 1
.Update
End With

Else
' this criteria already exists,
rst.MoveFirst
rst.Edit
lngNextNum = rst("NextNum")
rst!nextnum = lngNextNum + 1
rst.Update
End If

NextNumber = Format(lngNextNum, "00000") & Qualifier


NextNumber_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

NextNumber_Err:
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 NextNumber_Exit
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem
Generating Number"
Resume NextNumber_Exit

End If

End Function
'-------------------------end code------------------------
 
Thank you very much Habib for such a quick response. as you probably guessed
I'm a newbie at this (although I'm 58, late starter, grey cells don't work
so fast) There are a couple of points I am unsure of. Do I create a table
called Nextnumbers and if so what field name do I create? Is it NextNum? Do
I put the function in the form module if so in which property BeforeUpdate?
and how do I link the procedure to my control CommDocNbrtxt?
I'm in the Uk, I've been at this since 9.am and it's now 7.30pm, I'm getting
tired I think
Thanks again Tony
 
Yes,
Please create a table
Table: NextNumbers
Columns (FieldNames): Domain Text(50), Qualifier Integer, NextNum long
Integer

You should place the code in a code module so that it can be called from any
form.
Now, how do you call the function so taht it works correctly?

That would depend on your form's design
Ideally, I would place the code behind a save button.
The idea is to generate the next number only when you are sure you are going
to use it

Regards
HS
 
Don't worry: it didn't strike me as rude. It seemed pretty obvious to me
that you wouldn't have wanted that under the circumstances!

--
Doug Steele, Microsoft Access MVP



Tony Williams said:
Doug sorry if that response sounded rude wasn't meant to be got so hung up
with my problem didn't realise what I'd said
Thanks
Tony
Douglas J. Steele said:
You need to change how you retrieve intNumber from the recordset.

If Right(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Left(rs.Fields("CaseNum"),
Len(rs.Fields("CaseNum")) - 4)) + 1
Else
intNumber = 1
End If

However, it's not going to work. Since the number is a string, it won't sort
in the correct order, so you can't be guaranteed that you'll get the highest
number already used. You'll find that they'll sort 102003, 112003, 122003,
12003, 22003, 32003, 42003, 52003, ....

Much better is to store them as two separate fields, and just concatenate
them in a query.

--
Doug Steele, Microsoft Access MVP



Tony Williams said:
Arvin I'm using your code you posted in July to create a sequential number
that changes with the year. It nearly does exactly what I want it to do.
The format at the moment is 20030001, 20030002, 20030003 etc I want
12003,22003,32003 etc
I've changed the line
DateNum = Year(Date) & Format(intNumber, "0000") to
Year(Date) & intNumber to get 20031, 20032 but if I change the line to
intNumber &Year(Date) it doesn't work.
What am I missing?
Hope you don't mind this being specifcally for you but I didn't know how
else to contact you.
Thanks in anticipation
Here is all the code you posted

Paste the following code into a module and change the field and table names
to fit your data. You can put it in the form module or in a standard module
(but don't name the standard module with the same name as the
Click to expand...
function).
Use
it in the DefaultValue (property sheet - data tab)of the text box like:

= DateNum()

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number in the table 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"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & 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


Tony Williams
Click to expand...
Click to expand...
Click to expand...
 
Thanks Habib, I'll try all of that and come back to you in a couple of days
if I may
Tony
 
Struggling, could I have got this out of a book instead of having to keep
bothering you???

So, I've created the table and the module. I've called the module
GetNextNumber because I've been told not to call a module by the same name
as a Function. Is that OK?
Now I appreciate what you said about the save button but I want the number
to appear in my control ComDocNbrtxt as the form opens on a new record and
is stored in that field in my table, tbldocuments. It has to appear as the
user needs to do something once they know the number. I've tried using an
unbound control and my CommDocNbrtxt control using the control source and
in the Before Update property as
=NextNumber
which is the name of the function but all I get in both is #Name?

I think I'm missing something basic here in my understanding of modules
Habib and there use in forms, something I haven't covered in much depth in
my studies yet.
 
Tony,
The function takes two arguments
- Domain, which you can think of as NumberType, and
- Qualifier - which you can use for year
so you would use
=NextNumber("CommDocNbrTxt", cint(year(date)))

You can name the module anything - GetNextNumber will work
but understand that a module can have many functions and can have any name.
Generally, I use a name like Utility. Sometimes I have more than one Module
to group
them logically - for example Utility, SQLFunctions and so on

HS
 
Tnoy,
were you able to get it to work?
HS
HSalim said:
Tony,
The function takes two arguments
- Domain, which you can think of as NumberType, and
- Qualifier - which you can use for year
so you would use
=NextNumber("CommDocNbrTxt", cint(year(date)))

You can name the module anything - GetNextNumber will work
but understand that a module can have many functions and can have any name.
Generally, I use a name like Utility. Sometimes I have more than one Module
to group
them logically - for example Utility, SQLFunctions and so on

HS


create
Click to expand...
 
Yes thanks Habib, sorry I didn't get back to you earlier but I only managed
to work on it late last night (UK time)
Thanks again for all your help where would us newbies be without guys like
you willing to spend time helping!!!
Tony
 
Back
Top