Combo Box Form Filter

[jereece]

I would like to build a filter on a form using a combo box. I would like the options to be "Open Records", "Closed Records" and "All Records".

I already have a combo box that has the options "Open" and "Closed". This combo box is set to Open when a new record is created and the user selects Closed when the issue is closed. So I can't use this combo box as a filter.

So, how can I use another combo box as a filter so the user can select "Open Records", "Closed Records" and "All Records" as view options on the form? I am using a query to provide the data from a table to the form. In the past I have used something like the code below to filter records, but since I am already using the Status field to change the status of the record, I don't know how else to do this.

[Forms]![frm_Data]![cbo_filter] Or Like [Forms]![frm_Data]![cbo_filter] & "*"

Thanks,

Jim

 

[Tim L]

A couple of suggestions:

1. Open/Closed/All Record combo based on a table, two fields "Filter Type" and "Filter Code". In Filter Type is "Open", "Closed" & "All", in Filter Code is the filter to be applied. In the the After Update event of the combo put the Filter Code from the table into the form filter criteria box (Me.Filter = Me.comboName.Column(1)) and apply the filter (may be Requery.

2. Value list "Open", "Closed" and "All" in the combo. Again in the After Update, but this time using a Select Case, apply the filter code (this time stored in the code) to the form.

HTH

Tim

 

[jereece]

I appreciate the help, but I am not totally following your instructions.

I am using a Value List of "Open";"Closed";"*". In the After Update event I have the following code

Private Sub cbo_filter_AfterUpdate()
Me.Requery

End Sub

I don't understand what you mean by

but this time using a Select Case, apply the filter code (this time stored in the code) to the form.

Again, I appreciate the help.

Jim

 

[Tim L]

If you're using the Value List method then the Select Case solution is, perhaps what you need.

[COLOR="Blue"]Private Sub[/COLOR] cbo_filter_AfterUpdate()

    [COLOR="blue"]Select Case[/COLOR] cbo_filter
        [COLOR="blue"]Case Is[/COLOR] = "Open"
            [COLOR="SeaGreen"]' Put in the code to apply your filter for OPEN records[/COLOR]
            Me.Filter = [B]<your filter>[/B]
            Me.FilterOn = True
        [COLOR="blue"]Case Is[/COLOR] = "Closed"
            '[COLOR="seagreen"] Put in the code to apply your filter for CLOSED records[/COLOR]
            Me.Filter = [B]<your filter>[/B]
            Me.FilterOn = True
        [COLOR="blue"]Case Is[/COLOR] = "*"
            [COLOR="seagreen"]' Remove the filter entirely to show ALL records.[/COLOR]
            Me.FilterOn = False
    [COLOR="blue"]End Select[/COLOR]

    Me.Requery

[COLOR="Blue"]End Sub[/COLOR]

Work out and put in the filters replacing the <your filter> bits accordingly. If your table was called tblData and the status field was called status (being real imaginative here... lol), then the whole line for the 'Open' scenario would be:

Me.Filter = "((tblData.status = 'Open'))"

(Although note, the filter is not case sensitive, so 'oPEN', 'OPEN' and 'open' would all have worked just as well.)

HTH

Tim