Form: Find ALL records matching a criteria from header combobox (1 Viewer)

[cathie]

I have a form with header combobox that I am allowing the user to select find criteria with. The problem is that it is only returning 1 record. I want it to select all records. The box contains states of the union. When for example "NJ" is selected, all NJ records, not just the first, should be displayed. This same code works great when the criteria is a date, or vendor, but for some reason not working on the state field. Any suggestions? (There is some commented code of other attempts).

Private Sub Combo43_AfterUpdate()

    Dim st As DAO.Recordset
    
'    strSQL = "SELECT * FROM [All2] WHERE [MState] = '" & Me.Combo43 & "'"
 
    If Not IsNull(Me.Combo43) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        'Set st = CurrentDb.OpenRecordset(strSQL)
        
        Set st = Me.RecordsetClone
        st.FindFirst "[MState] = '" & Me.Combo43 & "'"
        If st.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            Me.Bookmark = st.Bookmark
        End If
        Set st = Nothing
    End If

    Me.Combo45.RowSource = "SELECT DISTINCT City " & _
                    "FROM All2 " & _
                    "WHERE MState = '" & Me.Combo43 & "'" & _
                    "ORDER BY City"
                    
    'Me.Combo43 = ""
    
End Sub

 

[Cronk]

Me.Bookmark = st.Bookmark

positions the form to show the first record matching the state, not filters the records.

Is your form set only to display a single record?

You can filter the records by

me.filter= "[MState] = '" & Me.Combo43 & "'"
me.filteron= true

 

[cathie]

Thanks so much Cronk! That made all the difference!