With your present table design you'll need a query which checks each Boolean
(Yes/No) column against the relevant unbound check box on the form, but also
checks for the check box being FALSE. Each of these need to be done in an
independent OR operation. It will be easier for me to illustrate this with a
sample query which assumes just the two Boolean columns you've mentioned.
Adding the others will be a trivial task. For the example I'm going to
assume that the table is called projects and has a column Project along with
the dozen or so Boolean columns:
SELECT [Project], [EducationRelated], [Funding]
FROM [Projects]
WHERE
([Education Related] = Forms![YourForm]![chkEducationRelated]
OR NOT Forms![YourForm]![chkEducationRelated])
AND
([Funding] = Forms![YourForm]![chkFunding]
OR NOT Forms![YourForm]![chkFunding])
ORDER BY [Project];
You'll see that each OR operation is enclosed within parentheses. This
forces each of them to evaluate independently of the AND operations which
tack all the parenthesised operations together.
So if we take Education Related, if the user checks the unbound
chkEducationRelated check box on the form the first part of the first OR
operation will evaluate to TRUE for each row where the EducationRelated
column contains a TRUE value, and the second part of the OR operation will
evaluate to FALSE. Consequently the complete parenthesised expression will
evaluate to TRUE, and the row will be returned.
If the user leaves the chkEducationRelated check box unchecked the complete
parenthesised expression will evaluate to TRUE for every row, regardless of
the value of the EducationRelated column because the second part of the OR
operation evaluates to TRUE. This is what you want as the user is not
interested in whether EducationRelated is TRUE or FALSE.
By tacking each parenthesised expression together with a series of AND
operations only those rows where all the parenthesised expressions evaluate
to TRUE will be returned, i.e. those where the value of the relevant column
matches that of the checked checkbox, or where the checkbox is unchecked
regardless of the value of the column.
Its important that each unbound checkbox starts off as FALSE, and you'll
also need a means to reset them all to FALSE and clear the search, so to
avoid duplication put the code for this in a function in the form's module:
Private Function ResetCheckBoxes()
Me. [chkEducationRelated] = False
Me. [chkFunding] = False
' and so on
' requery form
Me.Requery
End Function
You can call the function as the form's On Open event property, and as the
On Click event property of a 'Show All' button simply be entering the
following as the property in the form's properties sheet:
= ResetCheckBoxes()
To implement the search after checking the check boxes all that's necessary
is to requery the form, which you can do in the Click event procedure of the
form rather than directly from the properties sheet with a single line of
code:
Me.Requery
OK, that should work, but your table design is poor. By having a separate
Boolean column for each 'factor' you are doing what's known as 'encoding data
as column headings'. In a relational database data should only be stored as
values at column positions in rows in tables; its called the Information
Principle.
The correct way to set this up would be to use three related tables:
1. Project: (or whatever), with primary Project. Each row would represent
one project.
2. Factors: This would be a table of your dozen or so factors (or whatever
you like to call them), "Education Related", "Funding" etc. in a column Factor
3. ProjectFactors: this has two foreign key columns, Project and Factor
referncing the primary keys of the other two tables. What this table is
doing in fact is modelling a many-to-many relationship type between the other
two tables.
For data entry you'd have a form in single form view based on Projects, and
within it a subform in continuous form view based on ProjectFactors. The
latter would be lnked to the main form on Project and would contain a combo
box bound to the Factor column with a RowSource of:
SELECT Factor FROM Factors ORDER BY Factor;
To add a factor to the current project is simply a case of adding a new row
in the subform by selecting an item from the combo box in the blank 'new
record' row of the subform.
To search the database, instead of using a series of unbound check boxes,
use a multi-select list box (setting its MultiSelect property to 'simple'
would probably be best in this case as this enables you to select items by
simply clicking one after the other), lstFactors, with a RowSource of:
SELECT Factor FROM Factors ORDER BY Factor;
Add a button to the form to confirm the search, and in its Click event
property put code like this:
Dim varItem As Variant
Dim strFactorList As String
Dim strSQL as String
Dim ctrl As Control
Set ctrl = Me.lstFactors
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strFactorList = strFactorList & ",""" & ctrl.ItemData(varItem) &
""""
Next varItem
' remove leading comma
strFactorList = Mid(strFactorList, 2)
strSQL = " SELECT * FROM [Projects] " & _
"WHERE EXISTS (" & _
"SELECT * FROM [ProjectFactors] " & _
"WHERE [Factor] IN(" & strFactorList & ")) " & _
"ORDER BY [Project]"
' set form's RecordSource property to restricted query
Me.RecordSource = strSQL
' requery form
Me.Requery
Else
MsgBox "No factors selected", vbInformation, "Warning"
End If
For a 'Show All' button the code for its Click event would set the form's
RowSource to an unrestricted query:
Const conSQL = "SELECT * FROM Projects ORDER BY Project"
' set form's RecordSource property to un unrestricted query
Me.RecordSource = conSQL
' requery form
Me.Requery
You don't need any code in the form's open event procedure for this, just
set its RecordSource to:
SELECT * FROM Projects ORDER BY Project
in its properties sheet.
Ken Sheridan
Stafford, England
Marie-Lynn said:
Hello,
I have a problem. I have a databse with a form where a user enters
information. Among this information are about a dozen true/false lines. To
illistrate, these lines are all decriptive words for organizations (e.g.
'Education related true/false') I want to create a function within this form
or create a query where a user can check the true/false lines that they want
to search. In other words, if they were looking for an organization that is
education related, they would check the 'education related' box, which would
then draw up all the entries that have that entered. This part I have all
but fingured out. However, the other part that I want it to do is that I
want it to be searchable under multiple true conditions. (e.g. 'Education
related' is true AND 'funding' is TRUE).
I am stumped at how to do this. Can anyone point me in the right direction?
Thanks,
ML
