Populate Listbox from RoloButtons

wdarnellg
wdarnellg used Ask the Experts™
on
I would like to populate a list box in an Access form using rolodex type buttons. They work fine for an individual record, but I want to display multiple matches to the search criteria if they exist. I tried in the code here to see if I could get the same record in the listbox as is in the bookmark. It doesn't work. Can someone help get me on the right track?
'This procedure executed whenever you click on a button
'in the RoloButtons option group.
Private Sub RoloButtons_AfterUpdate()
 
    
    Dim FldToSearch, LookFor As String
    'Change FldToSearch below to the name of the field in your
    'table or query that you want the Rolodex buttons to operate on.
    FldToSearch = "Junior"
    'Make a temporary recordset for searching.
    Dim TempRecSet As Object
    Set TempRecSet = Me.RecordsetClone
    
    'Construct a search string.
    LookFor = "Left([" + FldToSearch + "],1) = " + Chr(34) + RoloButtons.Controls.Item(RoloButtons.Value - 1).Name + Chr(34)
    
    'Find the first matching record.
    TempRecSet.FindFirst LookFor
    
    
    'If no match found, look for the closest one up.
    If TempRecSet.NoMatch Then
        LookFor = "Left([" + FldToSearch + "],1) >= " + Chr(34) + RoloButtons.Controls.Item(RoloButtons.Value - 1).Name + Chr(34)
        Debug.Print LookFor
        TempRecSet.FindFirst LookFor
    End If
    
    'Go to the matching record (assuming something was found).
    If Not TempRecSet.NoMatch Then
        Me.Bookmark = TempRecSet.Bookmark
        Me.lstJuniorNames.RowSource = TempRecSet.Bookmark
        Me.lstJuniorNames.AddItem
    End If
    
    'Pop out the pressed toggle button, clear out the recordset.
    Me.RoloButtons.Value = 0
    Set TempRecSet = Nothing
    
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
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?

Author

Commented:
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.
Top Expert 2009
Commented:
Does this have anything to do with running a search, or do you just want to use an option group with lettered toggle buttons to put a selection into a listbox?  If the latter, then write a Select Case statement for the option group's AfterUpdate event to add items to the listbox, something like this:
Private Sub fraChoice_AfterUpdate()
 
   Dim intChoice As Integer
   Dim lst As Access.ListBox
   
   Set lst = Me![lstSelections]
   intChoice = Nz(Me![fraChoice].Value, 1)
   
   Select Case intChoice
   
      Case 1
         lstSelections.AddItem "Selection for A"
         
      Case 2
         lstSelections.AddItem "Selection for B"
         
      Case 3
         lstSelections.AddItem "Selection for C"
         
      Case 4
         lstSelections.AddItem "Selection for D"
         
      'and so forth
      
      End Select
      
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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'
Top Expert 2009

Commented:
I see how it works -- so how do you want the list populated after a button is clicked?
Top Expert 2009

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

Author

Commented:
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.
Top Expert 2009

Commented:
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

Open in new window

Top Expert 2009

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial