Populate combo on for with distinct current records

[Moss100]

Hello,

I would like to use a combobox to let users filter a form.


So for instance I have a form called frmProperty, a field called PropertyID and a combobox called CboFilter

Is it possible to look at the current forms recordset and select all unique PropertyID and then use this as the values for the combobox?

Many thanks Mark

 

[dhookom]

I expect you could use a Value List for the combo box type and loop through the recordset clone to find unique PropertyID values. I tried this successfully with a form of the Orders table in Northwinds. You would need to modify to fit your column names.

Private Sub FilterCboFilter()
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    Dim strRowSource As String
    With rs
        .MoveFirst
        Do Until .EOF
            If InStr(strRowSource, .Fields("CustomerID")) = 0 Then
                strRowSource = strRowSource & .Fields("CustomerID") & ","
            End If
            .MoveNext
        Loop
        .Close
    End With
    Debug.Print strRowSource
    Me.cboFilter.RowSource = Left(strRowSource, Len(strRowSource) - 1)
    
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016