Using Option group to filter check box

[Pixie78]

I am drawing a blank today. I have added an "Inactive" checkbox to my
employees table (tblEmployees).
I have a print reports form (prtEmpReports) for employee information. This
form has an option group linked to a list box so as to print by Employee
Name, Department, Job Title, etc......All works fine.
I wanted to be able to filter in or out the Inactive employees. I added an
option group with 2 check boxes so I am thinking it should work like this:
1. If neither box is selected then there is no filter, show all employees
regardless of Inactive status, basically how the form works originally.
2. Choose this box (opt1) to show Only Active Employees
3. Choose this box (opt2) to show Only Inactive Employees

Thanks for any suggestions.

 

[Arvin Meyer [MVP]]

Won't quite work like that because once checked, an option group can be
changed, but not unchecked. I suggest using 3 check boxes (1 for each
choice) with the default with the filter for All, and also the default
checkbox. Use a bit of VBA code in the option group's afterupdate event to
choose the filter that you wish.

 

[Bob Quintal]

It cannot be unchecked; however, using VBA, it can be returned
to a Null value.
Me.opgEmp = Null
One possibiity may be to include this in the report Close
event to reset it for another report.
Forms!FormName!opgEmp = Null

With that being said, it probably is more information to the
user to have 3 options
Active
Inactive
All

Itr may be more intuitive for users to have the two checkboxes,
defaulted to both checked, for Inactive and Active Employees. Any
attempt to uncheck both boxes should pop up a message box chiding
the user that since an employee can be only active or inactive, not
checking any box will produce a blank report.

 

[Klatuu]

It cannot be unchecked; however, using VBA, it can be returned to a Null value.
Me.opgEmp = Null
One possibiity may be to include this in the report Close event to reset it
for another report.
Forms!FormName!opgEmp = Null

With that being said, it probably is more information to the user to have 3
options
Active
Inactive
All

 

[Klatuu]

I agree.
Were I doing it, I would not use check boxes. I would use Option Buttons
labeled (cleverly) Active, InActive, and All, and default to All.

 

[Pixie78]

Fabulous. I'm down with the 3 option buttons. Can I get a little help with
the coding? It would be a select case statement, yes?
Wait, I guess I would need the Active/Inactive option buttons to filter the
other part of the form that contains the list box and an option group to
select what fills the list????? Employee Names, Departments, etc......

?

 

[Klatuu]

Yes, a Select Case statement would be appropriate, but we need to know what
you are doing. First, you mentioned a report. Filtering the report would be
simple. You could use the Where argument of the OpenReport method to do
that, but I don't know what you want to do with a list box, etc. I would
need more detail; however, here is a simple way to do the report filtering:

Select Case Me.opgEmp
Case 1 'All
strWhere = vbNullString
Case 2 'Active
strWhere = "[Emp_Status] = 'Active'"
Case 3
strWhere = "[Emp_Status] = 'Inactive'"
End Select

Docmd.OpenReport "rptEmployees", acViewPreview, ,strWhere