Thanks for your reply and direction. I've used that approach many times
before (multiple addresses per contact, multiple phone numbers with
multiple phone number types, etc.) and for whatever reason it didn't hit
me to use this approach. So this was easy. Now the hard part.
I have a form where the form header contains unbound fieilds the usere
enters data to filter on. The main form itself consists of a datasheet
record set that initially opens to all records, but is then filtered
based
on the criteria the user selects in the header. So I need to put a
keywords subform in the header to use for filtering...easy to add this of
course. My code (see attaached) includes a starter SQL statement and then
adds the criteria selections to it like this:
MySQL = "SELECT tblTrialDoc.* FROM tblTrialDoc WHERE "
MyCriteria = ""
MyCriteria = MySearchWhat & " AND " & MySearchWhy
My tables are: tblTrialDoc (main table), tblKeywords (list of keywords to
choose), and tblDocKeywords (middle tbl stores selected keywords for
tblTrialDoc).
The tblKeywords only has a key field called Keyword.
The tblDocKeywords only has RecordID, KeywordID, and Keyword.
So now what I need is the code to loop through the sbfKeywords to see
which, if any, the user has selected to filter on along with other filter
criteria such as a date range. Then add the results to MyCriteria shown
above.
I'm pretty that I would start by replacing the starter MySQL statement
with one similar to:
SELECT tblTrialDoc.[Bates Number], tblTrialDoc.DocDate,
tblTrialDoc.DocYear, tblTrialDoc.To, tblTrialDoc.From, tblTrialDoc.What,
tblTrialDoc.Why, tblDocKeywords.Keyword
FROM tblTrialDoc INNER JOIN (tblKeywords INNER JOIN tblDocKeywords ON
tblKeywords.keyword = tblDocKeywords.Keyword) ON tblTrialDoc.RecordID =
tblDocKeywords.RecordID;
Would you mind helping create the MyKeywords filter to add on to the
MyCriteria?
Thank you.
Ken
keningilbert at yahoo dot com
Douglas J. Steele said:
Sorry, but the list box in Access doesn't have checkboxes.
What you could do is add a boolean "Selected" field to your table, and
use a
subform rather than a list box.
For what you're describing, you need 3 tables. You've got a many-to-many
relationship (one record can be linked to many keywords, one keyword can
be
linked to many records), therefore you need the 3rd table to resolve
that
many-to-many.
Take a look in the Northwind database that comes with Access: this is
similar to the relationship between Products and Orders, so the Order
Details table is required for resolution. Take a look at the Orders and
Orders Subform for how to handle data entry.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
I am trying to create a multi select list box consisting of keywords.
However, I don't want the user to remember to hold the Ctrl key down,
etc.
I'd like to present them with a list box showing the keywords and a
check
box next to each to use instead. Eliminates them accidently untagging
one.
Thinking ahead I will then need another list box on a filtering form to
filter these records by selecting multiple keywords.
Also are these the tables and fields that I will need? Just doesn't
seem
correct.
tblTrialDocs.RecordID (1)---->(00) tblKeywords.RecordID
tblKeywords.Keyword
Thank you.
Ken