Access 2010: Hierarchy movement with 2 columns

We have a table of processes that the user should be able to move up/down to form the order in which they are executed when they click an Execute button.
The problem is that we also need to give the user the ability to include or exclude items from this list.

I was thinking of have a two column type of combo box, one with the processes, one with a checkbox. But I dont know how to give the user the ability to re-order the items by selecting a process and "moving" it up/down on the list.

For example a list like:

ProcessA     YES/NO
ProcessB     YES/NO
ProcessC     YES/NO
ProcessD     YES/NO

where YES/NO is a checkbox.

The user should be able to move say ProcessC on top of ProcessA and perhaps move ProcessD below ProcessA.  

I'm open to any controls or ideas that you might have. The list will have the ability to grow - say up to 50 items or so. These are held in a table that we can append to and it would of course automatically appear as another new process.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
You need to add a SortOrder or Seq column to the table and two buttons (Up and Down) on your form.  User selects a record, checks the checkbox as desired, then uses the up and down buttons to execute a subroutine.  The following is air code, but I think I have it right.

I usually hide txt_SortOrder and use the SortOrder field to sort the recordset in the query for the form or the recordsource for a listbox.

Private sub cmd_Up_Click

    Call subReorder(-1)

End Sub

Private Sub cmd_Down_Click

    Call subReorder(1)

End Sub

Private Sub reorder(Direction as integer)

    Dim strSQL as string

    strSQL = "UPDATE yourTable " _
                & "SET [SortOrder] = (2 * " & me.txt_SortOrder & ") + " & Direction & " - [SortOrder] " _
                & "WHERE [SortOrder] BETWEEN " & me.txt_SortOrder _
                                                  & " AND " & me.txt_SortOrder + Direction
    currentdb.execute strsql, dbfailonerror


End Sub

Open in new window

This SQL results in a query that looks like the following, assuming you are on the record ranked #4 in the sort order, and you push Up

UPDATE yourTable
SET [SortOrder] = 7 - [SortOrder]
WHERE SortOrder Between 4 and 3

or, if you push down

UPDATE yourTable
SET [SortOrder] = 9 - [SortOrder]
WHERE [SortOrder] BETWEEN 4 and 5

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GNOVAKAuthor Commented:
Excellent ! went beyond by not only giving aan example but explaining it as well.
Dale FyeCommented:
Glad to help.

You also commented about potentially adding records to your list of processes.  When you do that, make sure that you give it a [SortOrder] equal to:

NZ(DMAX("[SortOrder]", "yourTable"), 0) + 1
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

GNOVAKAuthor Commented:
Hopefully you will see this -
It works great. How do I have the highlight follow the movement?
If I have the second item selected and move it up, the highlight stays on the new second item.
I have tried this and it seems right but the highlight doesnt move:

Me.lstHier.Selected(Me.lstHier.ItemsSelected(0) + Direction) = True
Dale FyeCommented:
As long as the bound column of your list is the PK (not the sort order), the selection should stay with the item you just moved.
GNOVAKAuthor Commented:
thank you (AGAIN!) worked like a champ!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.