Display order vs. system order

[Ricter]

Db tracks data from a set of documents. Each document has a unique number,
but this is not necessarily in numerical order. However, the order in which
the documents are received must be maintained, both in the hardcopy stack,
and in the displayed recordset.

So far, I have been replying on an autonumber to display the records in the
correct order. However, I noted that I missed a document and now I need to
insert it. I figure I need to create a field for "display order", to
differentiate from the autonumber system order.

Creating the field I can do, but what query would I use to fill in this
field for the current database? How do I set up this field to auto increment
for a new record, and still be user editable for instances where I want to
change it to x.5 (to insert)? How do I change the form (on load?) to sort by
this new display order?

 

[Roger Carlson]

First of all, get the notion of "system order" out of your head. There IS no
intrinsic order in Access tables. Depending on circumstances, the records
can be retrieved in any order.

As for display order, you are correct, Autonumber is totally unsuited for
this purpose. However, in a form, you can make your own quasi-autonumber
that is self incrementing by using the DMax function. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"AutonumberProblem.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ken Sheridan]

To insert the values of the current autonumber column into the DisplayNumber
column you'd run an update query like this:

UPDATE YourTable
SET DisplayNumber = DocumentNumber;

Where DocumentNumber is the current autonumber column. Rather than using
#.5 when you insert a row between two existing ones you could use the old
programming trick for line numbers and increment the numbers by 10, which
means you can use a long integer number data type. The update query would
then be:

UPDATE YourTable
SET DisplayNumber = DocumentNumber*10;

For auto-incrementing the DisplayNumber value this is easily done in a
single user environment by putting something like this in the BeforeInsert
event procedure of your data input form:

Me.DisplayNumber = Nz(DMax("DisplayNumber", "YourTable"),0)+1

or +10 if you are incrementing by 10. In a multi-user environment on a
network conflicts might arise if two users are adding records simultaneously.
You should index the DisplayNumber column uniquely to prevent this. You can
then handle the data error which would occur if a conflict arises, or you
could use a more elaborate number generation technique to prevent the error
occurring.

Ken Sheridan
Stafford, England

 

[Ricter]

Thanks Ken.

To insert the values of the current autonumber column into the DisplayNumber
column you'd run an update query like this:

UPDATE YourTable
SET DisplayNumber = DocumentNumber;

Where DocumentNumber is the current autonumber column. Rather than using
#.5 when you insert a row between two existing ones you could use the old
programming trick for line numbers and increment the numbers by 10, which
means you can use a long integer number data type. The update query would
then be:

UPDATE YourTable
SET DisplayNumber = DocumentNumber*10;

For auto-incrementing the DisplayNumber value this is easily done in a
single user environment by putting something like this in the BeforeInsert
event procedure of your data input form:

Me.DisplayNumber = Nz(DMax("DisplayNumber", "YourTable"),0)+1

or +10 if you are incrementing by 10. In a multi-user environment on a
network conflicts might arise if two users are adding records simultaneously.
You should index the DisplayNumber column uniquely to prevent this. You can
then handle the data error which would occur if a conflict arises, or you
could use a more elaborate number generation technique to prevent the error
occurring.

Ken Sheridan
Stafford, England