Maintaining Custom Sort Order - INDEXED UNIQUE

[Albert2]

There's already a thread for Maintaining Custom Sort Order but just for one table by @Mike Krailo

What I'm looking for is also the capability to change the order of a field by the user. The attached vba replace the chapter number but when I change the property Indexed to unique values it doesn't work and it detect it as duplicated.

To clarify what vba does:
· Letters are chapters
· Numbers position
· A=1,B=2.C=3,D=4,E=5, then user modifies the A=3 and the rest of titles change to B=1,C=2,A=3,D=4 y E=5

Please find the attached file. It works changing the form ChapterTable, field ChapterOrder.

Thanks!

 

[Albert2]

Thanks @Pat Hartman.
Those are nice solutions when you can use duplicates on your reordered field.
Meanwhile, the easiest solution will be to eliminate the table "chapter" and integrate the field "chapter" inside the table "titles" and apply the chapter update query just to that title. I'll adapt the Vba to change all the same chapters numbers that already exist.

 

[Albert2]

Yes, this is the reason it doesn't work, I'm applying the vba "AfterUpdate" and when I enter the repeated number to renumber the whole sequence without duplicates Access detects the error.

I have made several tries to avoid it: copy order, disconnect and reactivate the unique index and copying a temporal table, but it doesn't work.

There's must be a way to activate the VBA before entering the new number... maybe creating another field with the same data but, it looks inefficient.

 

[Mike Krailo]

That's strange, if you use the form setup as in the provide links, there is no problem with the code adjusting the order of the items while having a double key (ID & Seq) both have the key icon next them.

For example, here is a table I added the Seq field to so I could adjust the order of the menu buttons. Works like a champ.

 

[Albert2]

HI @Mike Krailo, I must check your examples carefully, it could save me time.
Hi @Pat Hartman, It looks the issue could come from the "non duplicate" option independently of the key. Maybe there's something I miss.

PD: I was trying to copy the resorted values to a none indexed field (with duplicates) to an indexed field (without duplicates) in the same table.

 

[Albert2]

Hi @Pat Hartman, I've tried I have modified all the Item# to unique, changing all the values counting 10.
· In your Access you can not introduce a number that is allready in the table, but when you apply the vba to renumber it also give an error. I should have more time to look into. Keeping and replacing the other numbers is basic for me. Thanks anyway.

Mainly what I do:
1: Verify if the new value allready exist: existingCount = DCount("*", "Chapters", "ChapterOrderIntro = " & newOrder & " AND ChapterID <> " & ChapterID)
2: In case it exist reorganize values, replacing the current number and reordering the rest: strSQL = "UPDATE Chapters SET ChapterOrderIntro = ChapterOrderIntro + 1 WHERE ChapterOrderIntro >= " & newOrder
db.Execute strSQL

 

[Albert2]

Thanks for your clarification @Pat Hartman I'll do my best!

 

[gemma-the-husky]

I've done this by having a dummy field available in the table

I then have a form offering a choice of sort factors on the records, and the user selects the factors he wants to use. The process then populates the dummy field with a sort string based on the sort factors, and then you sort the table based on that.

So you can sort by date, customer or alternatively you can select customer, date without needing to modify the query, just re-evaluating the sort string.

You do need code to evaluate the sort string based on the sort factors, and iterate all the records to calculate the sort string. It was worth doing that in the application I was developing.

Maybe this is similar to @Pat Hartman suggestions.

 

[MajP]

Those are nice solutions when you can use duplicates on your reordered field.

In this demo I have uniquely indexed the sort order field, so not a limitation. Sorting is very easy. You can drag and drop if you are not scrolling. You can double click and then drop. You can use the buttons.
See button "Write Order to Table" to persist the sort order to the table.

 

[Albert2]

Very graphic and clear! I did several tries trying to execute the vba at the same time and I had abbandoned the project for a while. Thanks!

 

[Thales750]

In this demo I have uniquely indexed the sort order field, so not a limitation. Sorting is very easy. You can drag and drop if you are not scrolling. You can double click and then drop. You can use the buttons.
See button "Write Order to Table" to persist the sort order to the table.

Mine works similar to yours. I use a separate Universal Sort Table. This allows the query to contain the Computer Name, or UserID depending on how you manage users/computers.
This works for any table because I use the controversial dual foreign Key Universal Link.

 

[Thales750]

The Sort Table
Link field allows any table in the system with a table number to be linked to Sorts
usLinkTableID All Tables have a number it's a field in the table set at record creation
usDescriptionID a logic field to determine the sort type
usTaskOwnerID is usually the same as usUserID these are set by log in
usNetworkID is the Computer Name used for systems without a User Login

The UserID is better than the NetworkID especially if Users move around in the system. Their Sorts follow them

 

[Albert2]

In this demo I have uniquely indexed the sort order field, so not a limitation. Sorting is very easy. You can drag and drop if you are not scrolling. You can double click and then drop. You can use the buttons.
See button "Write Order to Table" to persist the sort order to the table.



View attachment 110212

Hi MajP,

I've been carefully checking your answer, and it works perfectly for a non-related table. However, when you link the ordered value to another table, it works for the main table, but the results are not updated in the secondary table. I've tried updating values to -1 instead of null, but it doesn't work. Thanks!

 

[MajP]

Your tables and relationships do not seem correct as Pat points out and like Pat said cascade updates only work for the Primary Key to Foreign Key when referential integrity exists. Does not make sense here.
In plain words what does T-Scene-Shooting represent and how is it used. Also your names are problematic. NO special characters in any names (#,-) except for underscores. Can cause a lot of problems especially the #. Normally the letters ID are only used in a PK or FK. But you have IDsceneOrder and IDShootOrder which do not seem like PKs, FKs.
Is there a difference between the Scene Order and the Shot Order? Are they two seperate things?