Search Form with Checkboxes and Keywords

I suggest using listboxes for both if it's possible. This would save some form space and possible look a little cleaner. To do the select all for major categories you could consider using two radio buttons. One if they want to select specific categories from your listbox and the other if they want to select all categories.

From this search what do you plan on opening the information as? Are you opening another form that will display the records that match this criteria. Or a report or query?

In a case like this I prefer to create a help table with the Keywords and a Yes/No field.
Showing this in a datasheet subform allows the user to select the values needed and allows also an easy way to add or remove keywords.
For the search I just JOIN the table with the original table and filter for temp table rows with the Yes/No field being true.
Adding a button for (re-)setting all Keywords ca be done by using:

currentdb.execute ("UPDATE tblTemp SET YesNoField = True")

Getting the idea ?

Nic;o)

I suggest using listboxes for both if it's possible. This would save some form space and possible look a little cleaner. To do the select all for major categories you could consider using two radio buttons. One if they want to select specific categories from your listbox and the other if they want to select all categories.

From this search what do you plan on opening the information as? Are you opening another form that will display the records that match this criteria. Or a report or query?

I am hoping to have a subform that is bound to the table that I am searching. The table, of course, has the "major categories" field and the "keywords" field among many others.

I don't know what a radio button is, and I can't find any info about it in the Microsoft Help search. Also, I don't really know how to create list boxes that allow users to choose multiple options. Thanks for your help!

In a case like this I prefer to create a help table with the Keywords and a Yes/No field.
Showing this in a datasheet subform allows the user to select the values needed and allows also an easy way to add or remove keywords.
For the search I just JOIN the table with the original table and filter for temp table rows with the Yes/No field being true.
Adding a button for (re-)setting all Keywords ca be done by using:

currentdb.execute ("UPDATE tblTemp SET YesNoField = True")

Getting the idea ?

Nic;o)

The keywords are tricky, because each record has multiple keywords. The user has to be able to type in any combination of keywords. Thankfully, I already have code that can do that. However, I need to be able to ammend it to allow the user to not only search by keyword(s), but also by any combination of "major categories." The original code is found below; it's a bit tricky in my opinion, but I don't have much experience in vba. Also keep in mind that originally the form had a drop down box with all field names that allows the user to search by ANY field within the table, which I thought was pretty cool:

Thanks for you help!

A radio-button can be placed on a form within a frame.
Such a button is a small round button and pressing one will de-activate the other selected button as only one can be active at a time. (Check the Toolsbox besides the form when in edit mode).

Did you check my previous comment ?

Nic;o)

Hmmm, cross-posting <LOL> :-)

When I read:"The keywords are tricky, because each record has multiple keywords." I must conclude that your table hasn't been "Normalized" and that's asking for trouble.
The correct way would be to have an additional table with the keywords per unique (prime) key of the original table. That would allow you to do the filtering as described above and even make it possible to sort the results by the number of keyword hits.

Can you still change the table design ?

Nic;o)

A radio-button can be placed on a form within a frame.
Such a button is a small round button and pressing one will de-activate the other selected button as only one can be active at a time. (Check the Toolsbox besides the form when in edit mode).

Did you check my previous comment ?

Nic;o)

I checked the toolsbox, and I found no radio button. Is that the standard term that Microsoft uses or is it jargon?

Nope, it's the standard name as it's behaviour resembles the old buttons on a radio.
I see however that in the toolbox the tool is called "Option button" and MS even allows you to chose in the wizard to have checkboxes instead of the round buttons. From a GUI perspective that's cursing in the Church (old Dutch proverb :-)

Nic;o)

Hmmm, cross-posting <LOL> :-)

When I read:"The keywords are tricky, because each record has multiple keywords." I must conclude that your table hasn't been "Normalized" and that's asking for trouble.
The correct way would be to have an additional table with the keywords per unique (prime) key of the original table. That would allow you to do the filtering as described above and even make it possible to sort the results by the number of keyword hits.

Can you still change the table design ?

Nic;o)

Yes, I can make changes to the table design. Right now I have just one keywords field in the main table. Within that one field, I have a list of keywords for each record (it's an article database if you were wondering, and each article has various keywords attached to it). So, I should have a separate keywords table, but I am confused about the set-up (how it will look and how it will be related to the main table). Can you elaborate on what you said: "have an additional table with the keywords per unique (prime) key of the original table?" I don't really understand what you mean.

Thanks for your help!

Nope, it's the standard name as it's behaviour resembles the old buttons on a radio.
I see however that in the toolbox the tool is called "Option button" and MS even allows you to chose in the wizard to have checkboxes instead of the round buttons. From a GUI perspective that's cursing in the Church (old Dutch proverb :-)

Nic;o)

:) That's funny! Thanks for the clarification!

Hmm, would be nice when you could use A2007 as that will support multiple values in one field...
But OK, in the traditional Access database (and many others) we would create a table "tblKeywords" holding all allowed keywords and we could allow a user to add new words to that table.
Now create an additional "relation" table "tblArticleKeyword" and place the ArticleID and the Keyword as a field in there.
On the Article form you ca now add a subform based on the tblArticleKeyword and accept the proposed linkfield by the wizard. Thus Access will automatically add the ArticleID when a new row is created and when you make the Keyword field a "Lookup" field the user can use a combobox to select the needed value.
Each Keyword will appear as a separate row.
To make it easy on the user you could even use a temp table with all keywords and a YesNo field and let the user (de-)select the Keywords. This will however require some code to fill the table and to update the "tblArticleKeyword".

To extract the hits you can create a query that JOINs the selected keywords from a "tblKeywordFilter" (With the keyword and the YesNo field) with the "tblArticleKeyword" and get the ArticleID's. By using a GroupBy query and a so-called "outer-join" (in Access a LEFT or RIGHT JOIN) count the hits per Article and use the count for sorting them on most hits.

Just create the tables, fill some data and play around with it.

Nic;o)

Hmm, would be nice when you could use A2007 as that will support multiple values in one field...
But OK, in the traditional Access database (and many others) we would create a table "tblKeywords" holding all allowed keywords and we could allow a user to add new words to that table.
Now create an additional "relation" table "tblArticleKeyword" and place the ArticleID and the Keyword as a field in there.
On the Article form you ca now add a subform based on the tblArticleKeyword and accept the proposed linkfield by the wizard. Thus Access will automatically add the ArticleID when a new row is created and when you make the Keyword field a "Lookup" field the user can use a combobox to select the needed value.
Each Keyword will appear as a separate row.
To make it easy on the user you could even use a temp table with all keywords and a YesNo field and let the user (de-)select the Keywords. This will however require some code to fill the table and to update the "tblArticleKeyword".

To extract the hits you can create a query that JOINs the selected keywords from a "tblKeywordFilter" (With the keyword and the YesNo field) with the "tblArticleKeyword" and get the ArticleID's. By using a GroupBy query and a so-called "outer-join" (in Access a LEFT or RIGHT JOIN) count the hits per Article and use the count for sorting them on most hits.

Just create the tables, fill some data and play around with it.

Nic;o)

I don't think a combobox would be good to use for the keywords, because there are about 50 or 100 keywords, and I don't want the user to have to scroll through that many choices. Can you tell me which fields need to be primary keys and what fields need to be secondary keys? Also, an article can have many keywords, and that is where I am getting confused. If I'm understanding what you are saying, there should be three tables overall: 1.tblKeywords (with only one field for the keywords), 2. tblArticleKeyword (with only two fields - ArticleID and Keywords), and 3. Main Article Table (will include the ArticleID, but not the Keywords). If that's how it works, what are the primary and secondary keys?

Thanks for your help!

I don't think a combobox would be good to use for the keywords, because there are about 50 or 100 keywords, and I don't want the user to have to scroll through that many choices. Can you tell me which fields need to be primary keys and what fields need to be secondary keys? Also, an article can have many keywords, and that is where I am getting confused. If I'm understanding what you are saying, there should be three tables overall: 1.tblKeywords (with only one field for the keywords), 2. tblArticleKeyword (with only two fields - ArticleID and Keywords), and 3. Main Article Table (will include the ArticleID, but not the Keywords). If that's how it works, what are the primary and secondary keys?

Thanks for your help!

You're already getting the picture !

The ArticleID will be the primary key of tblArticle and the Keyword of tblKeyword.
The relationtable's primarykey will be a so-called "composite key" existing of the combination of ArticleID and Keyword.
Purists will add a KeywordID in the table tblKeyword, but when you define the relationship (see the relationship window) as "cascading update", then that won't be needed.
With 50 to 100 keywords it's advisable to use a tblKeyword to prevent typo's by the user. Best probably to use a datasheet subform with the Keywords and a YesNo field for selection, but also two listboxes (like Access uses for fieldselection in some wizards) can be very userfriendly. Check the "frmMoveLeftRight" in:
http://www.geocities.com/nico5038/xS...p-Down2000.zip
for a sample of both textbox and datasheet.

There's also a fourth table to confuse you even further, the "temp" tblKeywordsFind. That holds the keywords and a YesNo field. You can however add the YesNo field to the tblKeywords and use that.
The "temp" table is only needed when you want to show the user all Keywords with a YesNo to rearrange the selection. In that case you'll have to fill the YesNo depending on the found values in the relation-table and update that relationtable when the user has finished changing the keywords.

Confused ? :-)

Nic;o)

You're already getting the picture !

The ArticleID will be the primary key of tblArticle and the Keyword of tblKeyword.
The relationtable's primarykey will be a so-called "composite key" existing of the combination of ArticleID and Keyword.
Purists will add a KeywordID in the table tblKeyword, but when you define the relationship (see the relationship window) as "cascading update", then that won't be needed.
With 50 to 100 keywords it's advisable to use a tblKeyword to prevent typo's by the user. Best probably to use a datasheet subform with the Keywords and a YesNo field for selection, but also two listboxes (like Access uses for fieldselection in some wizards) can be very userfriendly. Check the "frmMoveLeftRight" in:
http://www.geocities.com/nico5038/xS...p-Down2000.zip
for a sample of both textbox and datasheet.

There's also a fourth table to confuse you even further, the "temp" tblKeywordsFind. That holds the keywords and a YesNo field. You can however add the YesNo field to the tblKeywords and use that.
The "temp" table is only needed when you want to show the user all Keywords with a YesNo to rearrange the selection. In that case you'll have to fill the YesNo depending on the found values in the relation-table and update that relationtable when the user has finished changing the keywords.

Confused ? :-)

Nic;o)

I'm trying to understand the set-up for tblArticleKeyword. I've heard of the "composite key" before, but I have never used one. For each ArticleID, am I supposed to have just one keyword in the Keyword field, because I still need to have multiple keywords related to each article. I'm still confused about how that relationship will work.

Thanks for you help!

As the relation table holds both keys, the composite key makes the combination unique.
Every N:M relation is realised in such a manner.

Sample for article A and B with some keywords:

A - Help
A - Not
B - Accent
B - Not
etc.

Neither A or B nor NOT is unique, but the combination is.
This will make sure that only one word can be combined with one article as:
A - Help
A - Not
A - Not (*)
B - Accent
B - Not
will fail as the second A - Not is in violation of the uniqueness of both fields.
Just select in the table definition borh fields and press the Key-button to get such a compound key.

Nic;o)

As the relation table holds both keys, the composite key makes the combination unique.
Every N:M relation is realised in such a manner.

Sample for article A and B with some keywords:

A - Help
A - Not
B - Accent
B - Not
etc.

Neither A or B nor NOT is unique, but the combination is.
This will make sure that only one word can be combined with one article as:
A - Help
A - Not
A - Not (*)
B - Accent
B - Not
will fail as the second A - Not is in violation of the uniqueness of both fields.
Just select in the table definition borh fields and press the Key-button to get such a compound key.

Nic;o)

Awesome! I now understand the power of the compound key! Thanks for your help. I will get to work on the tables. I have another problem after that though, but I will probably save it for another forum discussion. Here's the problem: I'm supposed to give users (with password authority) the ability to add new articles to the table, along with all of its pertinant data and keywords. With this table set-up, do you think that's possible? Again, I will probably repost this problem in a new forum discussion.

Thanks again for your help!

Glad to see that you learned something new :-)

About the access for users, that's not "table bound", but should be supported by form(s) and/or code.
Personally I offer all users access, but have 4 additional fields in the tables like:
CreatedBy
CreatedOn
LastUpdatedBy
LastUpdatedOn
These fields are made visible, but are edited automatically from code.
You can use the function:
Environ("username")
to get the loggedin username and Now() to get the datetime.

This way every user can see who's responsible and that does "solve" a lot of problems on forehand.

Success with your application !

Nic;o)

Glad to see that you learned something new :-)

About the access for users, that's not "table bound", but should be supported by form(s) and/or code.
Personally I offer all users access, but have 4 additional fields in the tables like:
CreatedBy
CreatedOn
LastUpdatedBy
LastUpdatedOn
These fields are made visible, but are edited automatically from code.
You can use the function:
Environ("username")
to get the loggedin username and Now() to get the datetime.

This way every user can see who's responsible and that does "solve" a lot of problems on forehand.

Success with your application !

Nic;o)

I was able to relate tblKeywords to the Main Article table with tblArticleKeyword and its composite key. However, I have very little experience with queries and radio buttons. I still need to know how to create a form that will let the user type in keyword(s) and/or choose from any combination of main categories. I would prefer the user to type in the keyword(s) into a text box. The user must also be able to choose any combination of main categories. How do I create a query that can filter based on these multiple criteria, and how do I then create a form to act as the user interface? Please explain in detail, because I am still new to Access and VBA.

Thanks for your help!

Assuming youu have an "AND" relation between the keywords and Categories, the easy way will be to have a tblKeyword and a tblCategories with the Keyword/Category field and a YesNo field for the selection.
You can create a separate form for the user to maintain the Keyword/Category to select from and use the YesNo field for dynamic filtering.

For the filtering use a mainform with a subform for tblKeyword and tblCategories, showing the value and the YesNo field.
Finally add a subform for the result being based on a query that's linking the tblArticle to the tblKeyword and tblCategories where the YesNo field equals "True".

When the user manipulates the two selection tables the result will show in the result subform.
The nice part is that you need no code :-)

Getting the idea ?

Nic;o)

Assuming youu have an "AND" relation between the keywords and Categories, the easy way will be to have a tblKeyword and a tblCategories with the Keyword/Category field and a YesNo field for the selection.
You can create a separate form for the user to maintain the Keyword/Category to select from and use the YesNo field for dynamic filtering.

For the filtering use a mainform with a subform for tblKeyword and tblCategories, showing the value and the YesNo field.
Finally add a subform for the result being based on a query that's linking the tblArticle to the tblKeyword and tblCategories where the YesNo field equals "True".

When the user manipulates the two selection tables the result will show in the result subform.
The nice part is that you need no code :-)

Getting the idea ?

Nic;o)

I hope my questions don't seem dumb, but I am still confused. I'm not sure what an "AND" relationship is. Also, what do you mean by "Keyword/Category" field? I guess I'm tripping over your first two paragraphs. Can you elaborate on your first two paragraphs, because they are really confusing.

Thanks for your help and patience!

There are no dumb questions, only not asking is dumb :-)

The AND relation means that both requirements need to be met like:
Category X AND Keyword Z
So only rows with both fields will show like:
Article1 X Z
Article5 X Z

The opposite is the OR relation:
Category X AND Keyword Z
So all rows with X and all rows with Z will appear like:
Article1 X Z
Article2 X A
Article3 Y Z
Article5 X Z

I used "Keyword/Category" out of "lazyness" as both tables have the same structure and can be treated / will be working. in the same way.
So just read the same sentence for Keyword and again the same for Category.

Create the two tables and see what happens when you create the query for the result subform.

Nic;o)

There are no dumb questions, only not asking is dumb :-)

The AND relation means that both requirements need to be met like:
Category X AND Keyword Z
So only rows with both fields will show like:
Article1 X Z
Article5 X Z

The opposite is the OR relation:
Category X AND Keyword Z
So all rows with X and all rows with Z will appear like:
Article1 X Z
Article2 X A
Article3 Y Z
Article5 X Z

I used "Keyword/Category" out of "lazyness" as both tables have the same structure and can be treated / will be working. in the same way.
So just read the same sentence for Keyword and again the same for Category.

Create the two tables and see what happens when you create the query for the result subform.

Nic;o)

I'm hesitant to use a subform for the keywords table, because there are almost 150 of them. My boss wants me to use a text box. So, can I use a text box for users to type in keywords (separated by commas, if typing more than one keyword)? I still want the filter to work based on the combination of keywords and categories. I created a subform for the categories table, and it looks great, because there are only 21 of them.

I know this complicates things, but I have to find a way to make it work like this. Do you have any suggestions/examples of code that can do this? I'm not sure if I can do it with a query or not.

Thanks again for your help!