Using check boxes to select items in a multi select list box

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

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
http://I.Am/DougSteele
(no private e-mails, please)


"Ken" wrote in message
...
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

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 a 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" wrote in message
...
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
http://I.Am/DougSteele
(no private e-mails, please)


"Ken" wrote in message
...
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

Is this a multi-user database, or just single user?

If just single, add the boolean Selected field to tblKeywords, and have the
subform display the checkbox and the word. To build a WHERE clause of the
selected keywords, you'd use something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT Keyword FROM tblKeywords " & _
"WHERE Selected = True"
strWhere = vbNullString

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

Do While rsCurr.EOF = False
strWhere = strWhere & Chr$(34) & _
rsCurr!Keyword & Chr$(34) & ", "
rsCurr.MoveNext
Loop

' Remove the extraneous ", " from the last
' entry

If Len(strWhere) 0 Then
strWhere = Left$(strWhere, Len(strWhere) - 2
strWhere = "Keyword IN (" & strWhere & ")"
End If

This won't work for multi-user situations, since you might have more than
one user trying to update tblKeywords simultaneously. You could work by
having a copy of tblKeywords in the front-end, but you'd have to ensure that
you always updated the copy before presenting the form to the user, in case
things have changed in the "real" copy.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ken" wrote in message
...
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 a 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" wrote in message
...
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
http://I.Am/DougSteele
(no private e-mails, please)


"Ken" wrote in message
...
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

This will be a single user situation, and if there are multiple users, they
won't be trying to do data entry and filter records at the same time. This
is because all dataentry will be done at one time and then the database will
remain static.

Screen shots at:
http://download.yousendit.com/C7F5C67341533B5C

I have to appologize in that I see that I left out an important piece of
info for you. You noticed that I didn't add the field Selected. This is
because I got away from the check box idea and went with a subfrom
consisting of one combo field where the user selects the keyword and the
list keeps growing thereby relating the choosen keywords to the main record
(see screen shot). Selecting and saving keywords works perfectly. I guess I
did this because I hit a comfort zone for myself as I 've used that approach
so many times before, but never had to filter on them. This doesn't mean
that I won't go
back to it.

So getting back on track Using the selected field concept, I went back and
added Selected to tblKeywords (see screen shot). This subform now shows all
the possible keywords and allows you to check the desired ones. However,
then the check boxes stay the same for every record. So my relationships
must not be correct (see screen shot)?

Ken

"Douglas J. Steele" wrote in message
...
Is this a multi-user database, or just single user?

If just single, add the boolean Selected field to tblKeywords, and have
the
subform display the checkbox and the word. To build a WHERE clause of the
selected keywords, you'd use something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT Keyword FROM tblKeywords " & _
"WHERE Selected = True"
strWhere = vbNullString

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

Do While rsCurr.EOF = False
strWhere = strWhere & Chr$(34) & _
rsCurr!Keyword & Chr$(34) & ", "
rsCurr.MoveNext
Loop

' Remove the extraneous ", " from the last
' entry

If Len(strWhere) 0 Then
strWhere = Left$(strWhere, Len(strWhere) - 2
strWhere = "Keyword IN (" & strWhere & ")"
End If

This won't work for multi-user situations, since you might have more than
one user trying to update tblKeywords simultaneously. You could work by
having a copy of tblKeywords in the front-end, but you'd have to ensure
that
you always updated the copy before presenting the form to the user, in
case
things have changed in the "real" copy.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ken" wrote in message
...
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 a 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" wrote in message
...
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
http://I.Am/DougSteele
(no private e-mails, please)


"Ken" wrote in message
...
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

Dear

I have List box in that list box if i select any item it will display
one the screen


Regards
lavanya kumar