424,509 Members | 1,686 Online
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

# Sequential Number

 P: n/a Hello every one I have a database with a form called "frmOrders" on that for I need to create a sequential number for each city apart from the original autonumber. So the table "tblorders" would look something like this: OrderID (Autonumber) SeqNo City 1 1 London 2 1 Madrid 3 2 London 4 2 Madrid 5 1 Barcelona 6 3 London 7 4 London At the moment I'm using a DMax formula on the default value on the "txtSeqNo" field on the orders form, but my problem is; when two orders are entered simultaneously they are given exactly the same sequential number. I have found a vb code on the web that creates a sequential number from a table and is network friendly, but I don't know how to fix the code to create the sequential number according to the City. Set myset = CurrentDb.OpenRecordset("tblSeqNo") myset.LockEdits = True myset.Edit myset!txtSeqNo = myset!SeqNo + 1 SeqID = myset!SeqID myset.Update myset.Close I would appreciate if anyone gives and an idea on how to do this Nov 12 '05 #1
7 Replies

 P: n/a On Thu, 1 Apr 2004 16:40:47 +0000 (UTC), "GAVO." wrote: Hello every oneI have a database with a form called "frmOrders" on that for I need tocreate a sequential number for each city apart from the original autonumber.So the table "tblorders" would look something like this:OrderID (Autonumber) SeqNo City1 1 London2 1 Madrid3 2 London4 2 Madrid5 1 Barcelona6 3 London7 4 LondonAt the moment I'm using a DMax formula on the default value on the "txtSeqNo" field on the orders form, but my problem is; when two orders areentered simultaneously they are given exactly the same sequential number.I have found a vb code on the web that creates a sequential number from atable and is network friendly, but I don't know how to fix the code tocreate the sequential number according to the City.Set myset = CurrentDb.OpenRecordset("tblSeqNo")myset.LockEdits = Truemyset.Editmyset!txtSeqNo = myset!SeqNo + 1SeqID = myset!SeqIDmyset.Updatemyset.CloseI would appreciate if anyone gives and an idea on how to do this The DMax is fine, it is the timing of it that is the problem. You are setting it when the record is created. Instead set it at the time the record is saved. Use the Form's BeforeUpdate event and check for the NewRecord property. Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.SeqNo = DMax("SeqNo", "tblOrders") + 1 End If End Sub This way the sequenced number happens at the very last instance. The chance of duplication is very, very small. There is really no need for the extra table with locking and unlocking. Also, you would probably want the sequence field indexed without duplicates. - Jim Nov 12 '05 #2

 P: n/a "GAVO." wrote in message news:c4**********@titan.btinternet.com... Hello every one I have a database with a form called "frmOrders" on that for I need to create a sequential number for each city apart from the original autonumber. So the table "tblorders" would look something like this: OrderID (Autonumber) SeqNo City 1 1 London 2 1 Madrid 3 2 London 4 2 Madrid 5 1 Barcelona 6 3 London 7 4 London If an order is deleted do you care that there will be gaps in the sequence? If you do then you should not store SeqNo in the table and instead consider generating it in a query when required. Otherwise go with Jim's suggestion. Nov 12 '05 #3

 P: n/a On Thu, 01 Apr 2004 22:42:27 GMT, "Jim Allensworth" wrote: On Thu, 1 Apr 2004 16:40:47 +0000 (UTC), "GAVO." wrote:Hello every oneI have a database with a form called "frmOrders" on that for I need tocreate a sequential number for each city apart from the original autonumber.So the table "tblorders" would look something like this:OrderID (Autonumber) SeqNo City1 1 London2 1 Madrid3 2 London4 2 Madrid5 1 Barcelona6 3 London7 4 LondonAt the moment I'm using a DMax formula on the default value on the "txtSeqNo" field on the orders form, but my problem is; when two orders areentered simultaneously they are given exactly the same sequential number.I have found a vb code on the web that creates a sequential number from atable and is network friendly, but I don't know how to fix the code tocreate the sequential number according to the City.Set myset = CurrentDb.OpenRecordset("tblSeqNo")myset.LockEdits = Truemyset.Editmyset!txtSeqNo = myset!SeqNo + 1SeqID = myset!SeqIDmyset.Updatemyset.CloseI would appreciate if anyone gives and an idea on how to do thisThe DMax is fine, it is the timing of it that is the problem. You aresetting it when the record is created. Instead set it at the time therecord is saved. Use the Form's BeforeUpdate event and check for theNewRecord property.Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.SeqNo = DMax("SeqNo", "tblOrders") + 1 End IfEnd SubThis way the sequenced number happens at the very last instance. Thechance of duplication is very, very small. There is really no need forthe extra table with locking and unlocking.Also, you would probably want the sequence field indexed withoutduplicates. I just noticed that you are using City as part of the criteria. So you might do it like this... Me.SeqNo = Nz(DMax("SeqNo", "tblOrders", _ "City='" & Me.City & "'"), 0) + 1 Single quotes around the City text value. - Jim Nov 12 '05 #4

 P: n/a On Thu, 01 Apr 2004 22:42:27 GMT, "Jim Allensworth" wrote: On Thu, 1 Apr 2004 16:40:47 +0000 (UTC), "GAVO." wrote:Hello every oneI have a database with a form called "frmOrders" on that for I need tocreate a sequential number for each city apart from the original autonumber.So the table "tblorders" would look something like this:OrderID (Autonumber) SeqNo City1 1 London2 1 Madrid3 2 London4 2 Madrid5 1 Barcelona6 3 London7 4 LondonAt the moment I'm using a DMax formula on the default value on the "txtSeqNo" field on the orders form, but my problem is; when two orders areentered simultaneously they are given exactly the same sequential number.I have found a vb code on the web that creates a sequential number from atable and is network friendly, but I don't know how to fix the code tocreate the sequential number according to the City.Set myset = CurrentDb.OpenRecordset("tblSeqNo")myset.LockEdits = Truemyset.Editmyset!txtSeqNo = myset!SeqNo + 1SeqID = myset!SeqIDmyset.Updatemyset.CloseI would appreciate if anyone gives and an idea on how to do thisThe DMax is fine, it is the timing of it that is the problem. You aresetting it when the record is created. Instead set it at the time therecord is saved. Use the Form's BeforeUpdate event and check for theNewRecord property.Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.SeqNo = DMax("SeqNo", "tblOrders") + 1 End IfEnd SubThis way the sequenced number happens at the very last instance. Thechance of duplication is very, very small. There is really no need forthe extra table with locking and unlocking.Also, you would probably want the sequence field indexed withoutduplicates.- Jim Just a small addition to Jim's answer. If the database is to start life empty (no data) then you need to wrap the DMax in a Nz function otherwise it will fail when you attampt to add the first record to the table. In an empty table DMax("SeqNo", "tblOrders") will return Null. Null + 1 = Null. whereas Nz(DMax("SeqNo", "tblOrders") ,0) will return 0. 0 + 1 = 1 If the underlying table already has data it is a non issue. Wayne Gillespie Gosford NSW Australia Nov 12 '05 #5

 P: n/a It is really important that this sequential number has no duplicates or gaps, that is one of the reasons why i did not want to use the original Autonumber, as the user is asked to weather to save or not the record when closing. I apresiate Jims suggestion about changing the timing of the formula i already have iin place and does what i want, but im looking for something that i do not have to worry about, that is why i included that vb code, which creates a sequential number, but what i dont know is how to use the "City" field as a criteria. Thanks. "John Winterbottom" wrote in message news:c4*************@ID-185006.news.uni-berlin.de... "GAVO." wrote in message news:c4**********@titan.btinternet.com... Hello every one I have a database with a form called "frmOrders" on that for I need to create a sequential number for each city apart from the original autonumber. So the table "tblorders" would look something like this: OrderID (Autonumber) SeqNo City 1 1 London 2 1 Madrid 3 2 London 4 2 Madrid 5 1 Barcelona 6 3 London 7 4 London If an order is deleted do you care that there will be gaps in the sequence? If you do then you should not store SeqNo in the table and instead consider generating it in a query when required. Otherwise go with Jim's suggestion. Nov 12 '05 #6

 P: n/a "GAVO." wrote in message news:c4**********@sparta.btinternet.com... It is really important that this sequential number has no duplicates or gaps, that is one of the reasons why i did not want to use the original Autonumber, as the user is asked to weather to save or not the record when closing. Regardless of the number generation mechanism, if an order is deleted then your sequence is going to have gaps. I suggest generating the seq nbr through code like: select o1.OrderID, o1.City, ( select count(*) from orders as o2 where o2.City = o1.City and o2.OrderID <= o1.OrderID ) As SeqNo from orders as o1 Nov 12 '05 #7

 P: n/a On Fri, 2 Apr 2004 15:29:28 +0200, John Winterbottom wrote (in message ): Regardless of the number generation mechanism, if an order is deleted then your sequence is going to have gaps. I recently had to implement something similar (SeqNo for Topics, SeqNo is not primary but of course candidate key). Since there won't be more than about 20 topics per Recordset in use I wrapped a SQL-Delete Statement and a renumbering loop in a transaction. I should add that the SeqNo is not editable for Users and changed/ created only by functions. And this db has only max. 3 Users. Michael Nov 12 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.