Preserving a sort order

[ryetee]

I'm amending a system that takes an order template that is displayed in a certain order.

The user can change the order and also add/delete records.
This creates entries in the order table.
The order from the form needs to be preserved for other forms/reports etc

I thought this would be easy by simply having a field that contains the row number of the original input form but can not find a way to do this.

Apparently ROW-NUMBER is not available any more
I've tried setting a control to form.currentrecord but this results in all records = 1.

Any help appreciated.

 

[The_Doc_Man]

You are correct that a property called RowNumber is not part of Access. I don't recall it being a part of Access in perhaps the last 20 years. But ROWNUMBER might be a property of something else.

First question: Is this database "pure" Access or is it an Access front end and something else on the back end? If so, it is possible that ROWNUMBER comes from the back-end.

Second question: If the order record has a prime key, why don't you store that for later use?

Third (somewhat rhetorical) question: If you need to preserve something like the number from an original input form, does that system allow a critical historical element to be deleted? If so, do you have the option to fix something that has a broken design?

 

[CJ_London]

providing you have an autonumber (generally reliable for this requirement) or a timestamp field (more reliable for manual input), you can just order by that. It won't give you a rownumber, but it will preserve the order which is what you are requiring.

Otherwise in the form current event use the dcount function - something like

if me.newrecord then me.rownum=dcount("*","orderlineTable","orderid=" & me.orderid)+1

The only problem is if you subsequently delete a row, you will end up with 2 records with the same row number

alternative is to use the dmax function, but then if you delete a row you'll end up with a gap

 

[ryetee]

You are correct that a property called RowNumber is not part of Access. I don't recall it being a part of Access in perhaps the last 20 years. But ROWNUMBER might be a property of something else.

First question: Is this database "pure" Access or is it an Access front end and something else on the back end? If so, it is possible that ROWNUMBER comes from the back-end.

Second question: If the order record has a prime key, why don't you store that for later use?

Third (somewhat rhetorical) question: If you need to preserve something like the number from an original input form, does that system allow a critical historical element to be deleted? If so, do you have the option to fix something that has a broken design?

The system runs alone on a PC - it has a front end for the tables etc and links to the backend data. I don't know if that makes it pure.

There is a primary key but I can't use it.
Let me clarify a bit more.
An order is copied from an order template. This actually holds a field that the user originally fills in with the order that he wants to see the items within the order displayed (i'll call this USRERORDER for now). This works. When he copies the template he may wish to change the order of the items, add new ones and indeed delete old ones. Once he has finished he's left with a number of rows. Once he commits to this he can not change anything in the order. All he requires is that this order is preserved for displays on future forms. If I can store the row number then I can do this.

Incidentally the place where this is does is actually a sub form. I have found some code that appears to change the USERORDER when I view it on the sub form but data on the table itself remains unchanged - this could be because a an append query is being used.

For your 3rd question - see my point why I want to use the row position. The user can only change order on the 1st form.

 

[ryetee]

providing you have an autonumber (generally reliable for this requirement) or a timestamp field (more reliable for manual input), you can just order by that. It won't give you a rownumber, but it will preserve the order which is what you are requiring.

Otherwise in the form current event use the dcount function - something like

if me.newrecord then me.rownum=dcount("*","orderlineTable","orderid=" & me.orderid)+1

The only problem is if you subsequently delete a row, you will end up with 2 records with the same row number

alternative is to use the dmax function, but then if you delete a row you'll end up with a gap

Apparently autonumber is not reliable and that's a property of the table isn't it? The user wants to be able to change the field when he first copies a template over - see reply to the above.
Ditto for time stamp I think.

I think I'm almost there as I've found some code to do this BUT i can see the required data on the form but it doesn't copy this to the table (again see above).