Combo Box Form Filter

  • Thread starter Thread starter BWD
  • Start date Start date
B

BWD

Spinning my wheels here....

I have one table with 1,000+ records and each record has a (required)
status; active or inactive. These records are stored in one table
called customers. I have a form that displays the customers and would
like to add a combo box to my form so that I can filter which records
are displayed.

The intended result would be a combo box that displays 2 values;
active and inactive. When one of those values is selected from the
combo box the forms recordset would only contain records that have the
status chosen from the combo box. For example, the user selects
"Active" from the cmbo box, only records with an active status are
available to scroll though on the form.

I have tried docmd.applyfilter and me.filter methods with results that
do not match what I am hoping for. Do I need to write a
clonerecordset command into my VBA or...?

Thanks
 
BWD said:
Spinning my wheels here....

I have one table with 1,000+ records and each record has a (required)
status; active or inactive. These records are stored in one table
called customers. I have a form that displays the customers and would
like to add a combo box to my form so that I can filter which records
are displayed.

The intended result would be a combo box that displays 2 values;
active and inactive. When one of those values is selected from the
combo box the forms recordset would only contain records that have the
status chosen from the combo box. For example, the user selects
"Active" from the cmbo box, only records with an active status are
available to scroll though on the form.

I have tried docmd.applyfilter and me.filter methods with results that
do not match what I am hoping for. Do I need to write a
clonerecordset command into my VBA or...?

Thanks
Click to expand...

If your status field is of type text, containing the words 'Active' or
'Inactive' then:

Me.Filter = "Status='Active'"
MeFilterOn = True

ought to do it (eg in the form's onload event). If however status actually
contains a number, then

Me.Filter = "Status = 1"
MeFilterOn = True

is the way to go.
 
Use the onChange event of the combo and then set the RowSource.

Select Case strStatus
Case "Active"
Me!ctlStatus.RowSource = qryActiveStatus
Case "Inactive"
Me!ctlStatus.RowSource = qryInactiveStatus
End Select


BrerGoose
 
Let me modify that to make it easier....


Use the onChange event of the combo and then set the RecordSource.

Select Case Me!ctlStatus
Case "Active"
Me.RecordSource = qryActiveStatus
Case "Inactive"
Me.RecordSource = qryInactiveStatus
End Select


BrerGoose
 
Thanks for your help. Does this mean that using a filter is nt
possible or practical in this situation? Asking because: 1) Did not
know if the filter was possible B) Filtering is less work than
creating multiple queries.

Thanks
 
Back
Top