• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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.
  • 3
  • 3
1 Solution
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
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now