If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Re-setting numbering
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. |
#2
|
|||
|
|||
Re-setting numbering
On Fri, 12 Dec 2008 17:14:00 -0800, Etta
wrote: 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Re-setting numbering
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/comm...6-8693f4db7008 Ken Sheridan Stafford, England "Etta" wrote: 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. |
#4
|
|||
|
|||
Re-setting numbering
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? Ken Sheridan wrote: 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/comm...6-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. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Re-setting numbering
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? Ken Sheridan wrote: 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/comm...6-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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200812/1 |
#6
|
|||
|
|||
Re-setting numbering
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/...g=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_He 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 "jhuncabas via AccessMonster.com" wrote: 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? Ken Sheridan wrote: 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/comm...6-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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200812/1 |
Thread Tools | |
Display Modes | |
|
|