Appending auto increment numbers on a given root

[Thorben Grosser]

Hello Newsgroup,

at the moment I am coding an archive database for a bank. Every folder
has an own number, following the sheme
x-x-xxx-yyyy
whereas x-x-xxx describes the archive, the storage location and the
shelf, yyyy should be a consecutive number.
If I am using an normal consecutive number, this would mean that it is
- independant from the x-x-xxxx-root - consecutive. I'd prefer to have
for every x-x-xxx- a new-starting consecution, meaning that for every
x-x-xxx, yyyy takes the values from 0000-9999,

is there a way to do so?

greetings
Thorben Grosser

[Jens Schilling]

Hi,

If I am using an normal consecutive number, this would mean that it is
- independant from the x-x-xxxx-root - consecutive. I'd prefer to have
for every x-x-xxx- a new-starting consecution, meaning that for every
x-x-xxx, yyyy takes the values from 0000-9999,
is there a way to do so?

You can try the following :

Create a table - I've named it "tblRoots" - and insert 2 fields

Field: "Root" as text and primary key
Field: "CurNumber" as long integer

Copy the code below in a standard modul.
In the direct window you can test it:
?GetRootNumber("MyTest")

Regards
Jens

Public Function GetRootNumber(MyRoot As String) As Long

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lgNumber As Long
Dim strCondition As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblRoots", dbOpenDynaset)

strCondition = "Root = '" & MyRoot & "'"
rs.FindFirst strCondition
If rs.NoMatch Then
lgNumber = 1
rs.AddNew
rs!Root = MyRoot
rs!CurNumber = 1
rs.Update
Else
lgNumber = rs!CurNumber
lgNumber = lgNumber + 1
rs.Edit
rs!CurNumber = lgNumber
rs.Update
End If

GetRootNumber = lgNumber

rs.Close: Set rs = Nothing
db.Close: Set db = Nothing

End Function

[Thorben Grosser]

Hey Jens,

thanks for your information. I tried to introduce the module into my
Access-File. Still, it reports the detection of an ambigious name
which makes no sense to me as there seems to be no double used name.
Is there any way to receive a more accurate error description or do I
have to look for the needle?

thanks
Thorben Grosser

[Jens Schilling]

Hi,

thanks for your information. I tried to introduce the module into my
Access-File. Still, it reports the detection of an ambigious name
which makes no sense to me as there seems to be no double used name.
Is there any way to receive a more accurate error description or do I
have to look for the needle?

Check your References - the refenrence to Microsoft DAO 3.x must be set

Regards
Jens

[Thorben Grosser]

Well, yes. RTFM :) works great, thanks a lot. Still, there is one
small problem:
No matter if I set the number of decimal places to 4 wether I set the
input scheme to 9999 it only returns values like 1, 2, 3...instead of
0001 0002 0003.
Any ideas?

Thanks
Thorben Grosser

On 23 Jul., 10:33, "Jens Schilling"
<[e-mail]:

Hi,

thanks for your information. I tried to introduce the module into my
Access-File. Still, it reports the detection of an ambigious name
which makes no sense to me as there seems to be no double used name.
Is there any way to receive a more accurate error description or do I
have to look for the needle?

Check your References - the refenrence to Microsoft DAO 3.x must be set

Regards
Jens

[Jens Schilling]

Hi,

No matter if I set the number of decimal places to 4 wether I set the
input scheme to 9999 it only returns values like 1, 2, 3...instead of
0001 0002 0003.
Any ideas?

As the return value of the function is a long integer value - and numbers
know nothing about leading zeros - you could change the return value to a
string.

So change the first line to :

Public Function GetRootNumber(MyRoot As String) As String

And use the format-method to for the return value

GetRootNumber = Format(lgNumber, "0000")

Regards
Jens

[Thorben Grosser]

On 23 Jul., 14:54, "Jens Schilling"
<[e-mail]:

Hi,

No matter if I set the number of decimal places to 4 wether I set the
input scheme to 9999 it only returns values like 1, 2, 3...instead of
0001 0002 0003.
Any ideas?

As the return value of the function is a long integer value - and numbers
know nothing about leading zeros - you could change the return value to a
string.

So change the first line to :

Public Function GetRootNumber(MyRoot As String) As String

And use the format-method to for the return value

GetRootNumber = Format(lgNumber, "0000")

thanks a lot, works great

Thorben