a sortable listbox

[belovedcej]

Okay - to my knowledge there is no way to do this - but I want a listbox that the user could sort by clicking on the column headers. Something like the datagrid object in VB.net.

Is there an equivalant object available for Access or is it possible create one with just VBA?

 

[Zion7]

there is the DataXObject, which has a listbox for Access.
You can do a lot of things, plus sort by column header.

...there was a post/faq in this fora about it,
by zameerabdulla, he had a sample DB with it.

sorry, I can't recall how to find it?

but yes, DataXObject, has a lot more versitalty.

If you insert a DataXObject, onto your form,
it will automatically create a reference to it, in your
VBA library. Using the Object browser, under libraries
select MSComctlLib, then you will see all the properties & methods
of the ActiveX ListBox control

 

[MajP]

I will have to give the DataXObject a try, but you can roll your own pretty easily if you want. Above the list box put cmd buttons right over your columns the same width as the column. In the command button put the name of the field in the tag property. Something like
CmdBtnLastName
Caption = Last Name
Tag = strLastName

Option Compare Database
Option Explicit
Public rsList As DAO.Recordset

Private Sub Form_Open(Cancel As Integer)
  Set rsList = Me.List8.Recordset.Clone
End Sub

Private Sub CmdBtnLastName_Click()
  'Static sortCount As Integer
  Dim strSort As String
  strSort = ActiveControl.Tag
  'If Not (sortCount / 2 = sortCount \ 2) Then
  '    strSort = strSort & " DESC"
  'End If
  Call SortList(strSort)
  'sortCount = sortCount + 1
End Sub

Private Sub CmdBtnFirstName_Click()
  Call SortList(ActiveControl.Tag)
End Sub


Public Sub SortList(theSortString As String)
  Dim rsTemp As DAO.Recordset
  Dim strSort As String
  Set rsTemp = Me.List8.Recordset
  strSort = theSortString
  Set rsTemp = rsList.OpenRecordset
  rsTemp.Sort = strSort
  Set rsTemp = rsTemp.OpenRecordset
  Set List8.Recordset = rsTemp
  List8.Requery
End Sub

If you uncomment the CmdLastName it will alternate between ascending and descending.

 

[MajP]

Here is another idea. In this one use the column headers and the mouse down event to figure out which column you are clicking in.

Private Sub List8_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Dim aColumnWidths() As String
  Dim intCounter As Integer
  Dim intColumn As Integer
  Dim lngTotalWidth As Long
  Dim rs As DAO.Recordset
  aColumnWidths = split(List8.ColumnWidths, ";")
  If Y < 300 Then 'The height of the top row in twips
  For intCounter = 0 To UBound(aColumnWidths)
    lngTotalWidth = lngTotalWidth + CLng(aColumnWidths(intCounter))
    If X < lngTotalWidth Then
      intColumn = intCounter
      Exit For
    End If
  Next intCounter
  If intColumn = 0 Then
    intColumn = UBound(aColumnWidths)
  End If
  
  Set rs = List8.Recordset.Clone
  Call SortList(rs.Fields(intColumn).Name)
  End If
End Sub

This was set up with the first field of zero width. You have to play around to figure out the height of the header row.

 

[belovedcej]

Awesome ideas all!! I am unfamiliar with the ActiveX objects, and so will need to spend some time learning about them to use them. The VBA (particularly the first one) also look like they would work well. I'll spend some time playing with these ideas and let you know if they work. So grateful for the thoughts!

 

[MajP]

Give me your thoughts on the code I posted. I would like to build a class module to encapsulate the above functionality, then I will post as a FAQ. I recently posted a class module FAQ for building a Combobox that filters the list as you type. You may want to look at that. There is another example as well that does not use a class module. You may get some additional ideas.

 

[mp9]

As an aside, there is a solution of sorts for this that doesn't use any ActiveX objects at

http://www22.brinkster.com/accessory/forms/004.shtml

- it's purely native Access form controls and VBA.

Hope this helps.

 

[MajP]

Looks a lot like my second code, but I would say mine is a far more portable, but maybe less flexible. I do not require any reference to field names since I use the recordset. No need for any case statements.