Solved Filter Form(datasheet view) using CheckBox

[rayape]

All -

I have a Form in the datasheet view. There are several fields in the form. And one of the fields is 'Attendance'. It's a Yes/No datatype.

I have two ComboBox pull downs that can filter the form by two different fields (Name and Company). Meaning: If I use the pull down and select a name, the datasheet filters all records and displays records of only the person selected. And the same thing with the company name.

I would like to further filter this form by 'Attendance'. I would like to use a CheckBox to filter only those attended. I would appreciate input on how to do this. Thank you.

Please see the attached picture for how the form looks like.

 

[theDBguy]

Hi. What have you tried so far? If you can filter by name, you shouldn't have any problems filtering by Yes/No as well. Please show us your code and mention any error message you may be encountering.

 

[June7]

Attendance is a Yes/No field? Controls used to enter filter criteria should be UNBOUND.

Suggest you use radio buttons in an Option Frame or a combobox with two choices.

You are using dynamic parameterized query? Including Boolean criteria is tricky if you want to allow for no parameter so all records will return. I prefer VBA to build filter criteria and apply to form or report. Review http://allenbrowne.com/ser-62.html

 

[rayape]

Hi. What have you tried so far? If you can filter by name, you shouldn't have any problems filtering by Yes/No as well. Please show us your code and mention any error message you may be encountering.

Function searchCriteria()

Dim Company, ParticipantName As String
Dim task, strCriteria As String


If IsNull(Me.cboCompany) Then
Company = "[CompanyID] like '*'"
Else
Company = "[CompanyID] = " & Me.cboCompany
End If

If IsNull(Me.cboName) Then
ParticipantName = "[ParticipantID] like '*'"
Else
ParticipantName = "[ParticipantID] = " & Me.cboName
End If

strCriteria = Company & "And" & ParticipantName
task = "select * from qry_All where " & strCriteria
Me.qry_All_subform.Form.RecordSource = task
Me.qry_All_subform.Form.Requery

End Function

 

[rayape]

Attendance is a Yes/No field? Controls used to enter filter criteria should be UNBOUND.

Suggest you use radio buttons in an Option Frame or a combobox with two choices.

You are using dynamic parameterized query? Including Boolean criteria is tricky if you want to allow for no parameter so all records will return. I prefer VBA to build filter criteria and apply to form or report. Review http://allenbrowne.com/ser-62.html

Will read that material. Thank you.

 

[theDBguy]

Function searchCriteria()

Dim Company, ParticipantName As String
Dim task, strCriteria As String


If IsNull(Me.cboCompany) Then
Company = "[CompanyID] like '*'"
Else
Company = "[CompanyID] = " & Me.cboCompany
End If

If IsNull(Me.cboName) Then
ParticipantName = "[ParticipantID] like '*'"
Else
ParticipantName = "[ParticipantID] = " & Me.cboName
End If

strCriteria = Company & "And" & ParticipantName
task = "select * from qry_All where " & strCriteria
Me.qry_All_subform.Form.RecordSource = task
Me.qry_All_subform.Form.Requery

End Function

Okay, so you could try adding a third criteria, like:

...
AttendanceCriteria = "[YesNoFieldName]=" & Me.YesNoControl

strCriteria = Company & " AND " & ParticipanName & " AND " & AttendanceCriteria
...

Hope that helps...

 

[rayape]

Okay, so you could try adding a third criteria, like:

...
AttendanceCriteria = "[YesNoFieldName]=" & Me.YesNoControl

strCriteria = Company & " AND " & ParticipanName & " AND " & AttendanceCriteria
...

Hope that helps...

How do I make the selection for people that attended? Meaning where do I select 'Yes'. Thank you.

 

[theDBguy]

How do I make the selection for people that attended? Meaning where do I select 'Yes'. Thank you.

That's up to you. Like @June7 said, you could use an Option Group with radio buttons or a dropdown with options for Yes and No. Or, you could simply use a single Checkbox.

 

[rayape]

That's up to you. Like @June7 said, you could use an Option Group with radio buttons or a dropdown with options for Yes and No. Or, you could simply use a single Checkbox.

I went with the dropdown option. It works. Thank you very much.

 

[theDBguy]

I went with the dropdown option. It works. Thank you very much.

Congratulations! Glad to hear you got it to work. Good luck with your project.