Filtering based on alphabetic buttons then list

[mstevens]

Guys,

I'm somewhat rusty with VBA but have been put in a spot trying to find a routine(s) that achieve the following on a form

a) 1st level filter - Alphabetic buttons, 1 each, A-Z

results filtered by selected alphabetic letter to

b) A list - then click on list item to sub-select matching items in a given datasheet

I have not coded yet but thought I would ask first if anyone has such a set of routines I could adapt or is aware of a source?

[maximus]

I have a simple table to demonstrate what I have done:
Table Name: exp_2
Fields: ID(Autonumber PK), Employee_Name(Text)

I have a form RecordSource table exp_2. One the form Header I have a multi-select list box with alphabet buttons A-Z. When I click on one of the alphabet buttons e.g. "A" all Employee's name starting with the Alphabet "A" is displayed in the List box. The code attached to Alphabet button A as follows:

Private Sub Command0_Click()
'The Name of the Listbox is List13
Dim strSQL As String
strSQL = "SELECT Employee_Name FROM exp_2 WHERE exp_2.Employee_Name Like 'a*'"
Me.List13.RowSource = strSQL
Me.List13.Requery
End Sub

Now I will select the names from the List Box based on which I want to filter my form and then Click Filter button. Then I use the code attached to the filter button to loop through the options selected in the list box to generate the filter criteria and filter the form. The code attached to filter button:

Private Sub Command12_Click()
Dim ctlList As Control, varItem As Variant, strCriteria As String
 
 'Return Control object variable pointing to list box.
 Set ctlList = Me.List13
 'Loop through selected items.
 For Each varItem In ctlList.ItemsSelected
 If Not IsNull(ctlList.ItemData(varItem)) Then
    
    If Len(strCriteria) > 0 Then
        
        strCriteria = strCriteria & " Or Employee_Name='" & ctlList.ItemData(varItem) & "'"
    Else
        strCriteria = "Employee_Name='" & ctlList.ItemData(varItem) & "'"
    End If
        
End If
 Next varItem
 If Len(strCriteria) > 0 Then
        Me.Filter = strCriteria
    Me.FilterOn = True
Else
    MsgBox "Please Select Names from drop down "
End If
strCriteria = ""
End Sub

To Remove Filter Create a Command button and attach this Code to its OnClick Even:
Me.FilterOn = False

[libraccess]

this is one I've saved for future use. In fact very good future use

[mstevens]

I have a simple table to demonstrate what I have done:

Maximus, this is just the job and thank you for helping me out of a hole (with minimal, codeless, info from me for which I apologise).

Thanks again - this solution will be put to very good use....

[maximus]

Please mark the Thread Solved this will help others searching for the same solution.