Sequential Number

[GAVO.]

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

[Jim Allensworth]

On Thu, 1 Apr 2004 16:40:47 +0000 (UTC), "GAVO."
<ga******@hotmail.com> wrote:

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

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

[John Winterbottom]

"GAVO." <ga******@hotmail.com> 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.

[Jim Allensworth]

On Thu, 01 Apr 2004 22:42:27 GMT, "Jim Allensworth"
<ji****@Notdatacentricsolutions.com> wrote:

On Thu, 1 Apr 2004 16:40:47 +0000 (UTC), "GAVO."
<ga******@hotmail.com> wrote:

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

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.

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

[Wayne Gillespie]

On Thu, 01 Apr 2004 22:42:27 GMT, "Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote:

On Thu, 1 Apr 2004 16:40:47 +0000 (UTC), "GAVO."
<ga******@hotmail.com> wrote:

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

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

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

[GAVO.]

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" <as******@hotmail.com> wrote in message
news:c4*************@ID-185006.news.uni-berlin.de...

"GAVO." <ga******@hotmail.com> 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.

[John Winterbottom]

"GAVO." <ga******@hotmail.com> 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

[Michael Tueller]

On Fri, 2 Apr 2004 15:29:28 +0200, John Winterbottom wrote
(in message <c4*************@ID-185006.news.uni-berlin.de>):

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