By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

Appending auto increment numbers on a given root

P: n/a
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

Jul 23 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Jul 23 '07 #2

P: n/a
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

Jul 23 '07 #3

P: n/a
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
Jul 23 '07 #4

P: n/a
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"
<jensschillingBitteLoesc...@fissership.dewrote:
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

Jul 23 '07 #5

P: n/a
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
Jul 23 '07 #6

P: n/a
On 23 Jul., 14:54, "Jens Schilling"
<jensschillingBitteLoesc...@fissership.dewrote:
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
Jul 23 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.