Maintaining Custom Sort Order - INDEXED UNIQUE (1 Viewer)

[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!

 

[Pat Hartman]

This is the link that includes Mike's sample. The sample also includes a link to a class module solution created by @MajP

Maintaining a Custom Sorted List

I have various projects that require custom sorting on either alpha numeric lists of items or just custom groupings. This of course uses a helper column with a list of numbers that can force any sort order I want. Maintaining those lists has been a pain so I finally broke down and made a form...

www.access-programmers.co.uk

Here is a third option. Look at the option used for the subform.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[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.

 

[Pat Hartman]

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

Duplicates are not allowed in the sequence number field. I'm not sure what you are talking about. The duplicates are prevented by the use of a multi-column unique index (must be created using the indexes dialog). The sequence number is unique WITHIN the parent ID. Is that what is causing the confusion?

 

[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.

 

[Pat Hartman]

Yes, this is the reason it doesn't work,

Did you build the necessary index using the indexes dialog? That is the ONLY way to do it. Adding a unique index to only the ItemNumber field would not allow you to repeat the number within table. Using the two field index, changes the scope to within the parent record.
On the first empty line of the dialog enter a name for the index. Then choose the first field and set the unique property to yes.
Move to the next empty line. Skip over the index name field and choose the second column name. So, rows with empty Index Names belong to the most recent Index name. You can have up to 10 columns to create a unique multi-column index. Two or three is pretty normal.

 

[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.

 

[Pat Hartman]

Try my example. The example doesn't include the unique index but it should, I will fix it and update the sample but in the mean time, you can add it yourself. By default, the sample increments by 10 when a new item is added. That leaves room for the user to move items around or to insert a new item between other items. So to "move" 180 to be the third item, change 180 to 25. Renumbering changes the set to number by 10's again.

 

[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

 

[Pat Hartman]

I've tried I have modified all the Item# to unique

I told you specifically that you CANNOT do that if item# repeats within a parent record.
I also pointed out why DCount() is a dangerous method. DMax() will prevent assigning a duplicate sequence number whereas DCount() will not.

It is really important that you understand BOTH of these concepts.

The code you are using (unless you switched to my method) requires you to allow duplicate numbers to exist by virtue of the way it works. So, unless you tell me that you have switched to my method and posted a new sample, I think someone else is going to have to solve your problem.

 

[Albert2]

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

 

[Pat Hartman]

You're welcome. Good luck.

 

[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