Sequential numbers

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

Tony Williams

I have a database that holds records of documents. I have a table called
tblCommittees that is linked to a table tblDocuments by a field Docnbrtxt. I
have used Autonumber in both tables to create the table index. Access wont
let me create another Autonumber field. I want to allocate a sequential
reference number called CommDocNbrtxt in tblCommittee each time a record in
tblDocuments is created for that committee. The number will include a
sequential number and the year from a field DateCreatedtxt and will be 1.03
and 2.03 and 3.03 and so on for 2003 but at the start of 2004 the number
should revert back to 1.04 and 2.04 and so on.
I suspect that I will need to achieve this with VBA code but it is beyond my
level of knowledge. can anyone help?
TIA
Tony
 
Hi Tony,

Three basic steps in VBA will accomplish this task. First, retrieve the
last value (MAX) of the index field you are creating. Then convert the
decimal/string to an integer using the CInt() function. Finally, increment
the index append the 2 digit year value as the decimal portion and return
the value. An example using DAO would look like this:

--- Start Code ---
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblCommittee] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
..Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function
--- End Code ---

In order to make this code work, you will need to set the Microsoft DAO 3.x
Object Library reference in Tools -> References within the VBA window.
Please note that I did not test the code so some tweaking may be necessary.
Also, a consideration may be to use a decimal value in the field rather than
a text value as they are easier to index and manipulate through code (i.e.
2004.1, 2004.2, etc...).

Hope this helps,
- Glen
 
Thanks Glen although that looks somewhat daunting to a newbie like me you
have simplified the steps for me. I will try that out and perhaps I can get
back to you if I struggle. Incidentally which property would the code go in,
the Before Update of the number control??
Thanks again
Tony
Glen Appleton said:
Hi Tony,

Three basic steps in VBA will accomplish this task. First, retrieve the
last value (MAX) of the index field you are creating. Then convert the
decimal/string to an integer using the CInt() function. Finally, increment
the index append the 2 digit year value as the decimal portion and return
the value. An example using DAO would look like this:

--- Start Code ---
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblCommittee] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function
--- End Code ---

In order to make this code work, you will need to set the Microsoft DAO 3.x
Object Library reference in Tools -> References within the VBA window.
Please note that I did not test the code so some tweaking may be necessary.
Also, a consideration may be to use a decimal value in the field rather than
a text value as they are easier to index and manipulate through code (i.e.
2004.1, 2004.2, etc...).

Hope this helps,
- Glen

I have a database that holds records of documents. I have a table called
tblCommittees that is linked to a table tblDocuments by a field
Click to expand...
Docnbrtxt.
I
have used Autonumber in both tables to create the table index. Access wont
let me create another Autonumber field. I want to allocate a sequential
reference number called CommDocNbrtxt in tblCommittee each time a record in
tblDocuments is created for that committee. The number will include a
sequential number and the year from a field DateCreatedtxt and will be 1.03
and 2.03 and 3.03 and so on for 2003 but at the start of 2004 the number
should revert back to 1.04 and 2.04 and so on.
I suspect that I will need to achieve this with VBA code but it is
Click to expand...
beyond
my
level of knowledge. can anyone help?
TIA
Tony
Click to expand...
Click to expand...
 
Hi Tony,

The BeforeUpdate of the form should be used, not the control itself. The
control should be locked so the user can not change the value. Then in the
form's BeforeUpdate event, you should check if the field already has a value
and assign the value only if it's missing.

--- begin code ---
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub
--- end code ---

Hope this helps,
- Glen

Tony Williams said:
Thanks Glen although that looks somewhat daunting to a newbie like me you
have simplified the steps for me. I will try that out and perhaps I can get
back to you if I struggle. Incidentally which property would the code go in,
the Before Update of the number control??
Thanks again
Tony
Glen Appleton said:
Hi Tony,

Three basic steps in VBA will accomplish this task. First, retrieve the
last value (MAX) of the index field you are creating. Then convert the
decimal/string to an integer using the CInt() function. Finally, increment
the index append the 2 digit year value as the decimal portion and return
the value. An example using DAO would look like this:

--- Start Code ---
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblCommittee] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function
--- End Code ---

In order to make this code work, you will need to set the Microsoft DAO 3.x
Object Library reference in Tools -> References within the VBA window.
Please note that I did not test the code so some tweaking may be necessary.
Also, a consideration may be to use a decimal value in the field rather than
a text value as they are easier to index and manipulate through code (i.e.
2004.1, 2004.2, etc...).

Hope this helps,
- Glen

I have a database that holds records of documents. I have a table called
tblCommittees that is linked to a table tblDocuments by a field
Click to expand...
Docnbrtxt.
I
have used Autonumber in both tables to create the table index. Access wont
let me create another Autonumber field. I want to allocate a sequential
reference number called CommDocNbrtxt in tblCommittee each time a
Click to expand...
record
in
tblDocuments is created for that committee. The number will include a
sequential number and the year from a field DateCreatedtxt and will be 1.03
and 2.03 and 3.03 and so on for 2003 but at the start of 2004 the number
should revert back to 1.04 and 2.04 and so on.
I suspect that I will need to achieve this with VBA code but it is
Click to expand...
beyond
my
level of knowledge. can anyone help?
TIA
Tony
Click to expand...
Click to expand...
Click to expand...
 
I really apologise for being so dum but do both sets of code go in the
Before Update?
In other words where does the function getDocIndex does that go in as a
module?
Tony
Glen Appleton said:
Hi Tony,

The BeforeUpdate of the form should be used, not the control itself. The
control should be locked so the user can not change the value. Then in the
form's BeforeUpdate event, you should check if the field already has a value
and assign the value only if it's missing.

--- begin code ---
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub
--- end code ---

Hope this helps,
- Glen

Thanks Glen although that looks somewhat daunting to a newbie like me you
have simplified the steps for me. I will try that out and perhaps I can get
back to you if I struggle. Incidentally which property would the code go in,
the Before Update of the number control??
Thanks again
Tony
Glen Appleton said:
Hi Tony,

Three basic steps in VBA will accomplish this task. First, retrieve the
last value (MAX) of the index field you are creating. Then convert the
decimal/string to an integer using the CInt() function. Finally, increment
the index append the 2 digit year value as the decimal portion and return
the value. An example using DAO would look like this:

--- Start Code ---
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblCommittee] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function
--- End Code ---

In order to make this code work, you will need to set the Microsoft
Click to expand...
DAO
3.x
Object Library reference in Tools -> References within the VBA window.
Please note that I did not test the code so some tweaking may be necessary.
Also, a consideration may be to use a decimal value in the field
Click to expand...
rather
than
a text value as they are easier to index and manipulate through code (i.e.
2004.1, 2004.2, etc...).

Hope this helps,
- Glen

I have a database that holds records of documents. I have a table called
tblCommittees that is linked to a table tblDocuments by a field Docnbrtxt.
I
have used Autonumber in both tables to create the table index.
Click to expand...
Access
wont
let me create another Autonumber field. I want to allocate a sequential
reference number called CommDocNbrtxt in tblCommittee each time a record
in
tblDocuments is created for that committee. The number will include a
sequential number and the year from a field DateCreatedtxt and will be
1.03
and 2.03 and 3.03 and so on for 2003 but at the start of 2004 the number
should revert back to 1.04 and 2.04 and so on.
I suspect that I will need to achieve this with VBA code but it is beyond
my
level of knowledge. can anyone help?
TIA
Tony
Click to expand...
Click to expand...
Click to expand...
 
Hi Tony,

The original function I posted can either go in a separate module or as a
separate function in the form module. Personally, as a general rule, if I
write a function that is specific to a particular form by referencing the
form's objects directly, I would put the code in the form's module.
Otherwise, the "generic" code would go in a separate module as is the case
with the GetDocIndex function.

The advantages to placing generic functions and subroutines in separate
modules are twofold. First, if the function is defined as a public function
or subroutine (default), then it can be accessed by another other object in
the project (re-usable code). Second, placing "like" functions in a module
helps to organize the code. For example, I would put all of my main data
I/O routines in a module called modData.

Hope this helps,
- Glen

Tony Williams said:
I really apologise for being so dum but do both sets of code go in the
Before Update?
In other words where does the function getDocIndex does that go in as a
module?
Tony
Glen Appleton said:
Hi Tony,

The BeforeUpdate of the form should be used, not the control itself. The
control should be locked so the user can not change the value. Then in the
form's BeforeUpdate event, you should check if the field already has a value
and assign the value only if it's missing.

--- begin code ---
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub
--- end code ---

Hope this helps,
- Glen

Thanks Glen although that looks somewhat daunting to a newbie like me you
have simplified the steps for me. I will try that out and perhaps I
Click to expand...
can
get
back to you if I struggle. Incidentally which property would the code
Click to expand...
go
in,
the Before Update of the number control??
Thanks again
Tony
Hi Tony,

Three basic steps in VBA will accomplish this task. First, retrieve the
last value (MAX) of the index field you are creating. Then convert the
decimal/string to an integer using the CInt() function. Finally,
increment
the index append the 2 digit year value as the decimal portion and return
the value. An example using DAO would look like this:

--- Start Code ---
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblCommittee] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function
--- End Code ---

In order to make this code work, you will need to set the Microsoft DAO
3.x
Object Library reference in Tools -> References within the VBA window.
Please note that I did not test the code so some tweaking may be
necessary.
Also, a consideration may be to use a decimal value in the field rather
than
a text value as they are easier to index and manipulate through code (i.e.
2004.1, 2004.2, etc...).

Hope this helps,
- Glen

message
I have a database that holds records of documents. I have a table called
tblCommittees that is linked to a table tblDocuments by a field
Docnbrtxt.
I
have used Autonumber in both tables to create the table index. Access
wont
let me create another Autonumber field. I want to allocate a sequential
reference number called CommDocNbrtxt in tblCommittee each time a record
in
tblDocuments is created for that committee. The number will
Click to expand...
Click to expand...
include
a
Click to expand...
will
be
Click to expand...
Click to expand...
 
Hi Glen I'm struggling!
Here is the whole code for my form
Option Compare Database
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocGroupLists] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then
strLastIdx = .Fields("LastDocIdx").Value
.Close
'End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx
End If
End With
End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub

Ive changed the table tblCommittees to tblDocGroupLists and put the control
CommDocNbrtxt on my form but when I open the form and add a record nothing
happens, the control is empty. When I ran Compile I had to change the
position of End With and added an End If.
Can you help any further??
Thanks, I'm a little out of my depth here even after reading up on Functions
and modules. All this code is in the Form module
Glen Appleton said:
Hi Tony,

The original function I posted can either go in a separate module or as a
separate function in the form module. Personally, as a general rule, if I
write a function that is specific to a particular form by referencing the
form's objects directly, I would put the code in the form's module.
Otherwise, the "generic" code would go in a separate module as is the case
with the GetDocIndex function.

The advantages to placing generic functions and subroutines in separate
modules are twofold. First, if the function is defined as a public function
or subroutine (default), then it can be accessed by another other object in
the project (re-usable code). Second, placing "like" functions in a module
helps to organize the code. For example, I would put all of my main data
I/O routines in a module called modData.

Hope this helps,
- Glen

I really apologise for being so dum but do both sets of code go in the
Before Update?
In other words where does the function getDocIndex does that go in as a
module?
Tony
in
the me
you
Click to expand...
code
go
in,
the Before Update of the number control??
Thanks again
Tony
Hi Tony,

Three basic steps in VBA will accomplish this task. First,
Click to expand...
retrieve
the
last value (MAX) of the index field you are creating. Then
Click to expand...
convert
the
decimal/string to an integer using the CInt() function. Finally,
increment
the index append the 2 digit year value as the decimal portion and
return
the value. An example using DAO would look like this:

--- Start Code ---
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblCommittee] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function
--- End Code ---

In order to make this code work, you will need to set the
Click to expand...
Microsoft
DAO
3.x
Object Library reference in Tools -> References within the VBA window.
Please note that I did not test the code so some tweaking may be
necessary.
Also, a consideration may be to use a decimal value in the field rather
than
a text value as they are easier to index and manipulate through code
(i.e.
2004.1, 2004.2, etc...).

Hope this helps,
- Glen

message
I have a database that holds records of documents. I have a table
called
tblCommittees that is linked to a table tblDocuments by a field
Docnbrtxt.
I
have used Autonumber in both tables to create the table index. Access
wont
let me create another Autonumber field. I want to allocate a
sequential
reference number called CommDocNbrtxt in tblCommittee each time a
record
in
tblDocuments is created for that committee. The number will
Click to expand...
include
a
sequential number and the year from a field DateCreatedtxt and
Click to expand...
will
be
1.03
and 2.03 and 3.03 and so on for 2003 but at the start of 2004 the
number
should revert back to 1.04 and 2.04 and so on.
I suspect that I will need to achieve this with VBA code but it is
beyond
my
level of knowledge. can anyone help?
TIA
Tony
Click to expand...
Click to expand...
Click to expand...
 
Back
Top