List Box Sort (1 Viewer)

[MattBaldry]

Hi All,

I have a code I use that I found on here, I was sure it was done by @isladogs , but I cannot find where anymore.

It is a function to move items up and down in a list box by changing a sequence field using buttons for up and down.

Dim varCurrentValue As Variant
    Dim intCurrentSort As Integer
    Dim strSQL As String
    
    varCurrentValue = lst.Column(lst.BoundColumn - 1)
    intCurrentSort = lst.Column(1, lst.ListIndex - lst.ColumnHeads)
    
    strSQL = "UPDATE [" & TableName & "] " _
           & "SET [" & SortField & "] = (2 * " & intCurrentSort & ") + " & Direction & " - [" & SortField & "] " _
           & "WHERE [" & SortField & "] BETWEEN " & intCurrentSort & " AND " & intCurrentSort + Direction
    CurrentDb.Execute strSQL, dbFailOnError
    lst.Requery
    lst.Value = varCurrentValue

It works perfectly, I use it in lots of places. What I wanted to know would be how to change it to have the sequence based on 2 fields.

At the moment the sequence is based on every record in the table. What I am hoping to achieve is changing the list to a sequence grouped on a second field.

GroupID	Sequence
1	1
1	2
1	3
2	1
2	2
2	3
2	4
3	1
3	2

I may have the wrong author of the original code, I just cannot seem to find the website I found it the first time round.

~Matt

 

[CJ_London]

not clear on the requirement - if a user moves say the bottom record to the top - do you want just that record moved? i.e. break the grouping. Or both records (GroupID=3) to preserve the grouping? If the latter, you will need a second sequence column to sort on, rather than groupID

 

[MattBaldry]

not clear on the requirement - if a user moves say the bottom record to the top - do you want just that record moved? i.e. break the grouping. Or both records (GroupID=3) to preserve the grouping? If the latter, you will need a second sequence column to sort on, rather than groupID

Hi CJ,

The way it works at the moment is that the item in position 1 cannot be moved up, and the item in the last place cannot be moved down.

When I add a new item, is uses a DMAX and adds +1 to the sequence. I then use the buttons to move it up and down in the group.

Works perfectly with everything in the same group. But I am looking to add the ability to move the sequence within the group only.

Not a problem to add a new record using the DMAX and an extra criteria, it is just working on the buttons that move it up and down the sequence to change it only within that group.

If I can find the original code author, they may already have something. If it is not isladogs, I apologise to the author that I have forgotten.

~Matt

 

[MajP]

You may be interested in this class. It turns any listbox into a sorter. Has many features including drag and drop.

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

 

[isladogs]

@MattBaldry
I do have code to move items up/down in a listbox using up/down button clicks.
However, I don't think I supplied the code in post #1.

A quick forum search gave me a link to this post where I did post example code:

MS Access: custom control built in another language

No, you can add a single form to any number of forms as a subform. You even add it multiple times to the same form. Possibly yes. I created ActiveX-Controls in .Net to be used in Access particularly to implement drag'n'drop and complex visualization. I even did a presentation on that...

www.access-programmers.co.uk

However, my code only moves items based on a single column and I've not seen code to do so based on two columns.
I'm not sure how well the idea of sorting only within a group would work in practice.

 

[MattBaldry]

You may be interested in this class. It turns any listbox into a sorter. Has many features including drag and drop.

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

Hi Maj,

I had looked at this one before, but it seems much more than I need to use for just moving items up and down. Very nice work, but far more than I need.

~Matt

 

[MattBaldry]

@isladogs , sorry, I thought it was yours.

I have just found the file I downloaded, I have attached it here.

I cannot find who or where I got it from though.

~Matt

 

[isladogs]

No problem.
That's definitely not my example. The person who I think may have provided that is no longer an AWF member.

In case it helps, I have different code based on the undocumented SortStringArray function which is part of the Wizhook library and which you may be able to adapt.

Sort Value Lists

This article demonstrates how you can easily sort value lists used as row sources for listboxes, combo boxes and table lookup fields including multi-valued fields (MVFs. An example app is provided. The undocumented Wizhook function SortStringArray is used for text strings in this example. Code...

isladogs.co.uk

 

[MajP]

I had looked at this one before, but it seems much more than I need to use for just moving items up and down.

Maybe, but it is far less work to use. It is two lines of code to instantiate and use.

Private Sub Form_Load()
  Set lstUpDwn = New ListMoveUpDown
  lstUpDwn.InitializeList Me.lstSort, Me.cmdup, Me.cmdDown, Me.cmdTop, Me.cmdBottom, Me.cmdDeselect
End Sub

 

[MattBaldry]

Maybe, but it is far less work to use. It is two lines of code to instantiate and use.

Private Sub Form_Load()
  Set lstUpDwn = New ListMoveUpDown
  lstUpDwn.InitializeList Me.lstSort, Me.cmdup, Me.cmdDown, Me.cmdTop, Me.cmdBottom, Me.cmdDeselect
End Sub

Thanks Maj,

I will have a deeper look. Do you think it could be modified to work the way I need for this list though?

I am trying something with the code I do have that I am hoping may work, we shall see.

~Matt

 

[MajP]

I will have a deeper look. Do you think it could be modified to work the way I need for this list though?

It does not need to get modified. It works with all lists.