Find position in listbox 1

[zandsc1]

Hi all - thanks for the help in advance

I'm trying to find a way to determine the value of the top entry in a listbox. I have a listbox that shows 15 values but contains 60 values, so when a user scrolls down to view some of the lower entries, I need a way to determine the values in the top line.

Example: User opens the form and the listbox starts on line 1 and lists the first 15 entries. The user scrolls down to look at entries 12 through 27. At this point I need a way to determine that the top line is line 12 and make some updates accordingly.

Any suggestions?

 

[MajP]

I believe that this can only be done through the windows API and it is not that easy. Lebans show a few examples of this. Look at his custom listboxes. There is some good code to do this.

http://www.lebans.com/toc.htm

 

[alvechurchdata]

It might be possible to work with the source of the listbox instead. If that's in an array then you could keep track of the current selection and work out what else must be visible on screen. It's not going to be easy though.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MajP]

Geoff,
If I understand correctly what the OP is saying you can not use the current selection. Assume you have 100 rows and 10 are visible at a time. If item 1 is selected, but you are scrolled down so items 80-90 are visible. I believe the user wants to know that 80 is the "top index".

There is a very easy solution to this. Instead of using a native access listbox go to additional controls and choose an MSForms listbox. This control has a "topindex" property which returns the index of the top most visible row.

 

[zandsc1]

MajP -

Thanks, this sounds like *exactly* what I need. I'm having trouble finding the 'additional controls' in Access 2007 so that I can get to the 'MSForms Listbox', any tips?

Also - will I need any additional references to use this listbox and/or the .topindex property?

Thanks again

 

[MajP]

In the design tab look for a red "X". This is "additional active X controls". The correct name is
"Microsoft forms 2.0 Listbox"

There is no "rowsource" property so you will have to use your own method to fill your listbox.

You will need a reference to the "Microsoft forms 2.0 object library". I think this happens automatically when you add the control to your form.

 

[zandsc1]

Thanks MajP, you're a lifesaver.

Time to do a little research in working with a new type of listbox...

 

[MajP]

Here is some code to make it easy to load a listbox from a table, query, or sql string. This works with multi column listboxes or single column.

Public Sub loadListBox(lst As MSForms.ListBox, domain As String, ParamArray fieldNames() As Variant)
  Dim rs As DAO.Recordset
  Dim colCount As Integer
  Dim colCounter As Integer
  Set rs = CurrentDb.OpenRecordset(domain, dbOpenDynaset)
  colCount = UBound(fieldNames) + 1
  lst.ColumnCount = colCount
  Do While Not rs.EOF
      lst.AddItem
    For colCounter = 0 To colCount - 1
      lst.Column(colCounter, lst.ListCount - 1) = rs.fields(fieldNames(colCounter))
    Next colCounter
    rs.MoveNext
  Loop
End Sub

call this code like this

Private Sub Form_Load()
   Call loadListBox(Me.lstProducts.Object, "Products", "ProductName", "UnitPrice")
End Sub

you can pass in as many field names as you want and it will make a column for each field name.