Re-setting numbering

[Etta]

I posted the following question to the wrong Access group. My apologies!

We have 35 controlled documents that are issued throughout the year,
generally in packages of 10 to 25. Each controlled document has its own
control number, which is set by the database based on date of issue. So, for
document "Walking" the first package of 10 issued in 2008 had numbers 08-0001
through 08-0010, and the numbers automatically update each time a package is
issued.

This worked fine for 2008, but now 2009 is approaching and I wonder if there
is a way to reset the numbering system so that on January 2, 2009 when the
document "Walking" is issued, the documents can be numbered 09-0001 through
09-0010.

Thanks, in advance, for your advice, and sorry again for posting this twice.

 

[John W. Vinson]

I posted the following question to the wrong Access group. My apologies!

We have 35 controlled documents that are issued throughout the year,
generally in packages of 10 to 25. Each controlled document has its own
control number, which is set by the database based on date of issue. So, for
document "Walking" the first package of 10 issued in 2008 had numbers 08-0001
through 08-0010, and the numbers automatically update each time a package is
issued.

This worked fine for 2008, but now 2009 is approaching and I wonder if there
is a way to reset the numbering system so that on January 2, 2009 when the
document "Walking" is issued, the documents can be numbered 09-0001 through
09-0010.

Thanks, in advance, for your advice, and sorry again for posting this twice.

How do the numbers "automatically update"? Is this an Autonumber field? What's
the actual structure (fieldnames and datatypes) of your table?

You'll need a bit of VBA code to create these numbers dynamically - and you
will NOT be able to use an Autonumber.

 

[Ken Sheridan]

You can look up the last number (if any) for the document type for the
current year and add 1. This would be done in the AfterUpdate event
procedure of a control bound to a column in which the document type is
recorded, so if this is called DocumentType and the column for the number is
called ControlNumber then the code would go like this:

Dim ctrl as Control
Dim strYear as String
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strYear = Format(VBA.Date,"yy")

strCriteria = "DocumentType = """ & ctrl & """ " & _
"And Left(ControlNumber,2) = """ & strYear & """"

If Not IsNull(ctrl) Then
Me.ControlNumber = strYear & "-" _
Format(Nz(DMax("Right(ControlNumber,4)", _
"YourTable",strCriteria),0)+1,"0000")
Else
Me.ControlNumber = Null
End If

This should work fine in a single-user environment, but a conflict could
arise in a multi-user environment if two or more users are adding a record
for the same document type simultaneously as they'd all get the same number.
Provided you have a unique index on the DocumentType and ControlNumber
columns (in combination, not individually) the first user saving the record
would be successful, but subsequent users would experience an error, so there
would be no possibility of duplicate numbers for the same document type.

There are ways of preventing two users getting the same number and you'll
find one described in my reply in the following thread, which deals with a
similar scenario:


http://www.microsoft.com/office/com...rted&mid=da6995be-f180-4b1d-ac56-8693f4db7008


Ken Sheridan
Stafford, England

 

[John via AccessMonster.com]

Sorry, this is my second post.

We have the same problem, I have had a peer to peer network, in which the
shared mdb resides in desktop pc, two laptops accessing the shared SharedData.
mdb, okay, we used the primary key whici is the autonumber, but...we have the
record number which is unique, my record number starts TSND00001, TSND00002...
AND SO ON, when the two users entering the product code, it happens that they
are on the same unique number......Please help me regarding this, is there
any chance to correct this scenario, we just starting only this year to
transfer our data form excel sheets, we are very new in vb code......thanks!

==================================================================
We are NGO (Non-Government Organization) and solicitation comes from variety
of good people / oraganization, we are non-profit organization dealing on
relief goods for less fortunate people, everyday, one company is being added
to our database NGGO and we want to create UNIQUE PRIMARY KEY BASED ON THE
FOLLOWING:

Fields1(Counter) Fields2(strNGGO) NGONumber

000001 NGGO NGGO000001
000001 NGGO NGGO000002
000001 NGGO NGGO000003
000001 NGGO NGGO000004
000001 NGGO NGGO000005

and so on, and so forth......

two desktop pc's accessing the database in shared/split format. everytime,
the two user creating making/creating new NGONumber they are making
duplicates and we are facing problems on this.

BY THE WAY, I'VE CREATED A COMMAND BUTTON FOR NEW NUMBER

Is there any chance you could help us regarding the above problem?

You can look up the last number (if any) for the document type for the
current year and add 1. This would be done in the AfterUpdate event
procedure of a control bound to a column in which the document type is
recorded, so if this is called DocumentType and the column for the number is
called ControlNumber then the code would go like this:

Dim ctrl as Control
Dim strYear as String
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strYear = Format(VBA.Date,"yy")

strCriteria = "DocumentType = """ & ctrl & """ " & _
"And Left(ControlNumber,2) = """ & strYear & """"

If Not IsNull(ctrl) Then
Me.ControlNumber = strYear & "-" _
Format(Nz(DMax("Right(ControlNumber,4)", _
"YourTable",strCriteria),0)+1,"0000")
Else
Me.ControlNumber = Null
End If

This should work fine in a single-user environment, but a conflict could
arise in a multi-user environment if two or more users are adding a record
for the same document type simultaneously as they'd all get the same number.
Provided you have a unique index on the DocumentType and ControlNumber
columns (in combination, not individually) the first user saving the record
would be successful, but subsequent users would experience an error, so there
would be no possibility of duplicate numbers for the same document type.

There are ways of preventing two users getting the same number and you'll
find one described in my reply in the following thread, which deals with a
similar scenario:

http://www.microsoft.com/office/com...rted&mid=da6995be-f180-4b1d-ac56-8693f4db7008


Ken Sheridan
Stafford, England

I posted the following question to the wrong Access group. My apologies!

[quoted text clipped - 11 lines]

Thanks, in advance, for your advice, and sorry again for posting this twice.

 

[jhuncabas via AccessMonster.com]

Sorry, this is my second post.

We have the same problem, I have had a peer to peer network, in which the
shared mdb resides in desktop pc, two laptops accessing the shared SharedData.

mdb, okay, we used the primary key whici is the autonumber, but...we have the
record number which is unique, my record number starts TSND00001, TSND00002...

AND SO ON, when the two users entering the product code, it happens that they
are on the same unique number......Please help me regarding this, is there
any chance to correct this scenario, we just starting only this year to
transfer our data form excel sheets, we are very new in vb code......thanks!

==================================================================
We are NGO (Non-Government Organization) and solicitation comes from variety
of good people / oraganization, we are non-profit organization dealing on
relief goods for less fortunate people, everyday, one company is being added
to our database NGGO and we want to create UNIQUE PRIMARY KEY BASED ON THE
FOLLOWING:

Fields1(Counter) Fields2(strNGGO) NGONumber

000001 NGGO NGGO000001
000001 NGGO NGGO000002
000001 NGGO NGGO000003
000001 NGGO NGGO000004
000001 NGGO NGGO000005

and so on, and so forth......

two desktop pc's accessing the database in shared/split format. everytime,
the two user creating making/creating new NGONumber they are making
duplicates and we are facing problems on this.

BY THE WAY, I'VE CREATED A COMMAND BUTTON FOR NEW NUMBER

Is there any chance you could help us regarding the above problem?

You can look up the last number (if any) for the document type for the
current year and add 1. This would be done in the AfterUpdate event
procedure of a control bound to a column in which the document type is
recorded, so if this is called DocumentType and the column for the number is
called ControlNumber then the code would go like this:

Dim ctrl as Control
Dim strYear as String
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strYear = Format(VBA.Date,"yy")

strCriteria = "DocumentType = """ & ctrl & """ " & _
"And Left(ControlNumber,2) = """ & strYear & """"

If Not IsNull(ctrl) Then
Me.ControlNumber = strYear & "-" _
Format(Nz(DMax("Right(ControlNumber,4)", _
"YourTable",strCriteria),0)+1,"0000")
Else
Me.ControlNumber = Null
End If

This should work fine in a single-user environment, but a conflict could
arise in a multi-user environment if two or more users are adding a record
for the same document type simultaneously as they'd all get the same number.
Provided you have a unique index on the DocumentType and ControlNumber
columns (in combination, not individually) the first user saving the record
would be successful, but subsequent users would experience an error, so there
would be no possibility of duplicate numbers for the same document type.

There are ways of preventing two users getting the same number and you'll
find one described in my reply in the following thread, which deals with a
similar scenario:

http://www.microsoft.com/office/com...rted&mid=da6995be-f180-4b1d-ac56-8693f4db7008


Ken Sheridan
Stafford, England

I posted the following question to the wrong Access group. My apologies!

[quoted text clipped - 11 lines]

Thanks, in advance, for your advice, and sorry again for posting this twice.

 

[Ken Sheridan]

If the numeric part of your NGONumber values, i.e. the last six characters
are unique and sequential, as appears to be the case, then the method used in
the file at the following link, which I cited in my original reply, will give
you the numeric element:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


In your case it appears that you want to add four letters before this to
give the full NGONumber. It looks from your post as though the first four
characters are always NGGO; is that right? If so then all you need to do is
to copy the basCounterfunctions module into your front end file, and create
the Counter.mdb file with its tblCounter table with a long integer number
column NextNumber in the same shared folder as your current back end file.

In your data entry form the code for its BeforeInsert event procedure would
then be a slight adaptation of that in the demo file, i.e.

On Error GoTo Err_Handler

Dim strCounterDb As String, lngID As Long

strCounterDb = ConnectPath() & "Counter.mdb"

'attempt to get next number
If Not OpenCounterDb(strCounterDb) Then
MsgBox "Unable to get NGO number at present.", vbInformation, "Error"
End If

Me!NGONumber = "NGGO" & Format(GetNextNumber(),"000000")

' close external counter database if open
CloseCounterDb

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

The demo file also includes a means for resetting the next number to be used
when a record is added, which you may or may not want to include. If you do,
then add a cmdReset button and txtStartNumber text box to your form as in the
demo, and use the same code for the former's Click event procedure as in the
demo, changing "NameID" and "Names" to "NGONumber" and the name of your
table.

Ken Sheridan
Stafford, England

Sorry, this is my second post.

We have the same problem, I have had a peer to peer network, in which the
shared mdb resides in desktop pc, two laptops accessing the shared SharedData.

mdb, okay, we used the primary key whici is the autonumber, but...we have the
record number which is unique, my record number starts TSND00001, TSND00002...

AND SO ON, when the two users entering the product code, it happens that they
are on the same unique number......Please help me regarding this, is there
any chance to correct this scenario, we just starting only this year to
transfer our data form excel sheets, we are very new in vb code......thanks!

==================================================================
We are NGO (Non-Government Organization) and solicitation comes from variety
of good people / oraganization, we are non-profit organization dealing on
relief goods for less fortunate people, everyday, one company is being added
to our database NGGO and we want to create UNIQUE PRIMARY KEY BASED ON THE
FOLLOWING:

Fields1(Counter) Fields2(strNGGO) NGONumber

000001 NGGO NGGO000001
000001 NGGO NGGO000002
000001 NGGO NGGO000003
000001 NGGO NGGO000004
000001 NGGO NGGO000005

and so on, and so forth......

two desktop pc's accessing the database in shared/split format. everytime,
the two user creating making/creating new NGONumber they are making
duplicates and we are facing problems on this.

BY THE WAY, I'VE CREATED A COMMAND BUTTON FOR NEW NUMBER

Is there any chance you could help us regarding the above problem?

You can look up the last number (if any) for the document type for the
current year and add 1. This would be done in the AfterUpdate event
procedure of a control bound to a column in which the document type is
recorded, so if this is called DocumentType and the column for the number is
called ControlNumber then the code would go like this:

Dim ctrl as Control
Dim strYear as String
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strYear = Format(VBA.Date,"yy")

strCriteria = "DocumentType = """ & ctrl & """ " & _
"And Left(ControlNumber,2) = """ & strYear & """"

If Not IsNull(ctrl) Then
Me.ControlNumber = strYear & "-" _
Format(Nz(DMax("Right(ControlNumber,4)", _
"YourTable",strCriteria),0)+1,"0000")
Else
Me.ControlNumber = Null
End If

This should work fine in a single-user environment, but a conflict could
arise in a multi-user environment if two or more users are adding a record
for the same document type simultaneously as they'd all get the same number.
Provided you have a unique index on the DocumentType and ControlNumber
columns (in combination, not individually) the first user saving the record
would be successful, but subsequent users would experience an error, so there
would be no possibility of duplicate numbers for the same document type.

There are ways of preventing two users getting the same number and you'll
find one described in my reply in the following thread, which deals with a
similar scenario:

http://www.microsoft.com/office/com...rted&mid=da6995be-f180-4b1d-ac56-8693f4db7008


Ken Sheridan
Stafford, England

I posted the following question to the wrong Access group. My apologies!

[quoted text clipped - 11 lines]

Thanks, in advance, for your advice, and sorry again for posting this twice.