Populate Listbox from RoloButtons

What do you mean by a Rolodex-type button? I have a Rolodex (don't use it much any more), and it is a circular arrangement of 3x5 file cards. It's hard to see how that interface would be implemented with buttons on an Access form. Is it some kind of 3rd-party ActiveX control?

It is a group of buttons in an option group with letters (A-B-C...) that search for the closest match when clicked. The code comes from a database on 'VBA For Dummies'. but it populates textboxes. I wish to populate a list box using the option group buttons.

This is about a search.
If you like, you can download the mdb file at:
http://media.wiley.com/product_ancillary/16/0764

Or you can click the link from:

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764574116,descCd-DOWNLOAD.html

The filename is 'RoloButtons'

I see how it works -- so how do you want the list populated after a button is clicked?

The example goes to the first record whose last name starts with the selected letter.

This project uses linked tables from sql server, and the view I am using in a name column made of the firstname & lastname. The name will start with the selected letter.  The record source and orderby properties are easy to set. I don't know how to make it loop through the recordset and put ALL of the matching data in the list box. As it is, the code bookmarks the first record found.

What does this have to do with the Rolodex example? That was for selecting the first match to a letter of the alphabet. If you want to put all the records in a recordset (or a field or calculated expression for each record) into a listbox's row source, this can be done in a variety of ways. The simplest would be to create a query that selects the appropriate data, and use that query as the row source. Alternatively, you could create a filtered query in VBA code, and assign the SQL to the row source (see below for code). Or you could iterate through a recordset and use AddItem to add the items to the listbox's Value List (but they will not be ordered alphabetically, unless the recordset itself is so ordered).
I am not entirely sure what you are trying to achieve here.  If, for example, you want to select a letter using the Rolodex buttons, and then fill the listbox with all the items starting with that letter, then I would recommend using the option group AfterUpdate event procedure I posted earlier, with code to create a filtered recordset running off the selected option, then assign the recordset to the listbox's row source.

[Text filter combo box]
 
Private Sub cboTechnician_AfterUpdate()
'Created by Helen Feddema 2-Feb-2005
'Last modified 2-Feb-2005
 
On Error GoTo ErrorHandler
 
   pstrTechnicianFilter = "[Technician] = " & Chr$(39) & _
      Nz(Me![cboTechnician].Value) & Chr$(39)
   Debug.Print "Technician filter: " & pstrTechnicianFilter
 
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
 
End Sub
 
[Date filter combo box]
 
Private Sub cboWorkDate_AfterUpdate()
'Created by Helen Feddema 2-Feb-2005
'Last modified 2-Feb-2005
 
On Error GoTo ErrorHandler
 
  pstrWorkDateFilter = "[WorkDate] = " & Chr$(35) & Nz(Me![cboWorkDate].Value) _
      & Chr$(35)
   Debug.Print "Work date filter: " & pstrWorkDateFilter
 
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
 
End Sub
 
[Numeric filter combo box]
 
Private Sub cboCode_AfterUpdate()
'Created by Helen Feddema 2-Feb-2005
'Last modified 2-Feb-2005
 
On Error GoTo ErrorHandler
 
   pstrCodeFilter = "[ProjectCode] = " & Nz(Me![cboCode].Value)
   Debug.Print "ProjectCode filter: " & pstrCodeFilter
   
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
 
End Sub
 
[Code to concatenate filters]
 
Private Sub cmdApplyFilter_Click()
'Created by Helen Feddema 2-Feb-2005
'Last modified 2-Feb-2005
 
On Error GoTo ErrorHandler
 
   Dim lngCount As Long
   
   'Concatenate filters
   pstrFilter = IIf(pstrCodeFilter <> "", pstrCodeFilter & " And ", "") _
      & IIf(pstrCompanyNameFilter <> "", pstrCompanyNameFilter & " And ", "") _
      & IIf(pstrCustomerNameFilter <> "", pstrCustomerNameFilter & " And ", "") _
      & IIf(pstrLocationFilter <> "", pstrLocationFilter & " And ", "") _
      & IIf(pstrWorkDateFilter <> "", pstrWorkDateFilter & " And ", "") _
      & IIf(pstrTechnicianFilter <> "", pstrTechnicianFilter & " And ", "") _
      & IIf(pstrProjectStartFilter <> "", pstrProjectStartFilter, "")
   Debug.Print "Filter: " & pstrFilter
   If Right(pstrFilter, 5) = " And " Then
      pstrFilter = Left(pstrFilter, Len(pstrFilter) - 5)
      'Debug.Print "Filter: " & pstrFilter
   End If
   Me![txtFilter].Value = pstrFilter
   
   strQuery = "qryFilteredWorkSchedule"
   Set dbs = CurrentDb
   strSQL = "SELECT * FROM qryWorkSchedule WHERE " & pstrFilter & ";"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      MsgBox "No records found; canceling filter"
      GoTo ErrorHandlerExit
   Else
      Me![subSchedule].Form.RecordSource = strQuery
   End If
   
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
 
End Sub

Select allOpen in new window

In other words, say they select "L", then you would filter by a field (or calculated expression) having Left(strName, 1) = "L"