Rank with VBA

Rank with VBA

With the Google presentation and a video below, I demonstrate how to change the rank of an item in a table. This is particularly useful if you have a lookup table and you want to offer your user the ability to move an item up or down the list. To add this feature you need to add a new field which stores a value you can change. Sort the list ascending so that the preferred option selected by the user comes to the top. See the presentation and video together for further information.

I developed this code for the Nifty Switchboard Builder. The page data is controlled by an index which prevents duplicate values. This causes a problem when you want to move one of the switchboard page rows up or down. Let’s say you want to move record number 5 to record number 4, you can’t because you can’t change 5 to 4 because this will trigger the duplication prevention provided by the unique index. The solution is to move it to a row number not within the set of row numbers. You could invent an arbitrary figure saying 1000, however you must be sure that this 1000 will not be used by some other process.

Rank with VBA

Rank with VBA

Video 1 (7:06)

This 1st video follows along from the presentation below. The presentation goes over the basic idea, then the video goes into details about how to construct the queries required to effect the ranking. In this video I show the next step, which is to create the queries which move people up and down in a list with VBA… In the second video of (not published yet) I will show how to convert the queries into SQL string and run them with VBA.

Video 1 (7:06)

Nifty Access YouTube Thumb Nail
Play Video

Presentation as PDF Here:-

… …

More Useful Stuff HERE:-

Leave a comment

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.