Hi John,
I'm not real clear on the criteria that decides whether or not to include a
member in the results, so bear with me and my assumptions, while I attempt
to make this as flexible as possible.
In this situation let's say you want members displayed where either *one
field* OR *one of many fields* contains a "True " value. (ie. field names
"Ford", or "Chev", or "Dodge" contain a "True" value.)
This can be accomplished best (IMHO) by using a multi-select listbox to
specify criteria.
1.) Design an unbound main form with a list-box control containing a field
list.
Create this list-box using the following method:
a.) Choose the list-box tool from the toolbar, and place it on your
form.
b.) When the list-box wizard starts up ... hit the [Cancel] button ...
we want to do something that the wizard is not designed to do.
c.) Open the property sheet for the list-box (if not already open).
d.) Name: Lets call it "lstFieldList"
RowSource Type: Leave as "Table/Query" for now, but we'll change this in
a minute.
Multi Select: Choose "Extended"
e.) In the "Row Source" field, select the table (Volunteer) that
contains the Yes/No Fields.
f.) After selecting the table, click on the ellipse (...) to invoke the
Query Builder, and answer "Yes" when prompted.
g.) When the "Design View" QBE grid appears, select JUST the Yes/No
fields that you want to be displayed in the field list.
h.) If you like, you can confirm that the selected fields DO contain
either Yes or No values by opening the "query" in datasheet view.
i.) Now switch from "Design or "Datasheet" view to "SQL" View and insert
this string -- Bogus As '(All)', -- just after the word "SELECT" in your SQL
statement.
It should now look something like this:
SELECT Bogus As '(All)', Volunteers.Ford, Volunteers.Chev, Volunteers.Dodge
FROM Volunteers;
Note: The idea of adding an '(All)' to the field list is so that we can
decide to show all records in the table rather than JUST by criteria.
j.) Close the QBE grid. When prompted to "save the changes to the SQL
statement and update the property...", say Yes
k.) Now change the list-box's RowSource Type to "Field List"
l.) Select Form view, and check out the "lstFieldList" list-box. It should
now contain '(All)', followed by a list of the desired fields.
2.) Insert a bound subform control (again based on Volunteer table) using
the subform tool / wizard to select the desired fields that you'd like to
display in your results. Lets name this subform "sbfVolunteers"
3.) Seeing as how the subform is based on the Volunteers table, all records
will be displayed by default. We're going to change the subform's
recordsource to an SQL statement that we will build in VBA code:
************************************************** ***********
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
sRequerySubform
End Sub
Private Sub lstFieldList_AfterUpdate()
sRequerySubform
End Sub
Public Sub sRequerySubform()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.
'in order to alow the user to view:
' 1.) ALL records
' 2.) filtered by one field only
' 3.) filtered by multiple fields
Dim ctl As Control
Set ctl = Me.lstFieldList
Dim Msg As String
Dim MySQL As String
Dim CR As String
CR = vbCrLf
Dim varItm As Variant
Dim whr As String
'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT Volunteers.*"
MySQL = MySQL & " FROM Volunteers "
'Add a "safety" step which prevents the user from combining "All" with any
other criteria.
If ctl.ItemsSelected.Count > 1 And ctl.Selected(0) = True Then
Msg = ""
Msg = Msg & "You cannot select '(All)' along with " & CR
Msg = Msg & "any other criteria."
MsgBox (Msg)
ctl.Selected(0) = False
End If
'Build the WHERE portion
whr = "" 'initialize variable
If ctl.ItemsSelected.Count > 0 Then
For Each varItm In ctl.ItemsSelected
If Len(whr) > 0 Then
whr = whr & " OR " & ctl.ItemData(varItm) & "= True"
Else
whr = whr & ctl.ItemData(varItm) & "= True"
End If
Next varItm
End If
If Len(whr) > 0 Then
MySQL = MySQL & "WHERE (" & whr & ")"
End If
MySQL = MySQL & "; "
'Debug.Print MySQL
Me.sbfVolunteers.Form.RecordSource = MySQL
Set ctl = Nothing
End Sub
************************************************** **
After implementing all of the above, you should be able to select "Chev" and
have all of the "Chev = True" records displayed.
Press [Ctrl] and click on "Ford" ... the subform should then display all of
the "Chev = True" and "Ford=True" records, and so on!
The beauty of this approach is that you could add a field "Volvo" (ya,
right!) later on. All that you need do to add this to the field list in the
combo-box is to modify the listbox as in 1.g.) above.
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...
I have been looking for an answer to a problem and have found this
group and hope you can assist .
I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..
I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated
I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future
Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge
I am using Windows XP and Access 2002
Your help would be gratefully accepted
