Keyword search that searches field names of true checkboxes

[TWins]

Hello all, I've recently finished constructing a database that uses forms with checkboxes i.e. when a user opens a form there are several checkboxes associated with labels, but the table only stores the check-boxes and the form labels are the field (column) names on the table.

Now, what I'm trying to do is create a search feature whereby if a keyword search is conducted, a search through the column names from the table with checkboxes takes place and only lines (rows) with true (checked) checkboxes are returned. Can anyone help me out with this? Any help would be appreciated! Thanks!

 

[JHB]

... a search through the column names from the table with checkboxes takes place and only lines (rows) with true (checked) checkboxes are returned.

What are you searching after, from where does that come? Or are you only searching for rows where all check boxes fields in the table is -1 (true)?

 

[TWins]

What are you searching after, from where does that come? Or are you only searching for rows where all check boxes fields in the table is -1 (true)?

I'm searching for the column names. The database is set up as a multi-user database where users log on and fill out forms. So, each line on a table is specific to a user. What I would like to do is be able to search through those tables (which are set up with check box fields only), and have rows returned where a keyword search matches a column name and has a -1 value for the check box. So yes, I'm only searching for rows where all check box fields in the table are -1.

I apologize if I'm not being clear enough.

 

[JHB]

Could you show it by print screen of the table, with some sample data in, and how this search should run/find?

 

[TWins]

Could you show it by print screen of the table, with some sample data in, and how this search should run/find?

Yes of course. I have attached a sample table with check boxes along with 2 example search forms. I would like to enter a few keywords and have the table searched (mainly the column names) for those key words, but only returning rows that have -1 (true) checked boxes.

e.g.
Keyword1: Database
Keyword2: It/Network
Keyword3: Spreadsheet

Desired results: ID 3 & 4 rows show up because they have checked boxes for those search terms.

I'm familiar with queries and using the like statements, but I believe something like this requires vba coding which I'm not very experienced in. I appreciate the help.

 

[JHB]

Keyword1: Database
Keyword2: It/Network
Keyword3: Spreadsheet

Desired results: ID 3 & 4 rows show up because they have checked boxes for those search terms.

You don't mean ID 4, do you?

Thanks - your print shots cleared up what you want to do.
How many tables do you have to search in? If more as one, do you want to search them all each time or how?
Why are you not using a listbox with multi select, to pick out the "key word" (field name), to search in, it could prevent you from misspelling, and it is faster as typing in?
What if a "key word" (field name) is not found in the table, what should then happen?

 

[JHB]

I've made a small example for you in the attached database.

 

[TWins]

You don't mean ID 4, do you?

Thanks - your print shots cleared up what you want to do.
How many tables do you have to search in? If more as one, do you want to search them all each time or how?
Why are you not using a listbox with multi select, to pick out the "key word" (field name), to search in, it could prevent you from misspelling, and it is faster as typing in?
What if a "key word" (field name) is not found in the table, what should then happen?

No, you're right. I would only want ID 3 to show up in that case. My mistake.

I have about 12 tables altogether and yes I'd want them all searched every time a search is conducted.

I have also considered switching all of those fields to multi-select listboxes, however, only as a last resort. The reason being, it will require a great deal of work to switch all the tables and add individual keywords to each box. Some tables have over 100 columns. I also personally prefer the checkbox design for users to complete the forms faster more easily.

If a keyword is not found in the table then I don't want anything to show up. I want the search window to indicate that 0 results were found.

 

[TWins]

I've made a small example for you in the attached database.

Thanks for the example.

My tables all have different column names and I would be searching through all table columns for each search. I'm not looking to select each field name for each search because there will be 100's of fields in that list. Also, I'd want to be able to enter a search query with keywords that would search through the field list for those keywords and return the MyID from the row that the keyword was found in (or found to have the -1 checkbox in). Is there any way to accomplish that?

 

[Mihail]

Interesting... as programming exercise.
On the other hand, from what I seen in your table, it is a many-to-many relationship between users and their skills (or rights).
So, this table should be split into a table with users, a table with skills (rights) and one more (bridge) table user-skill.

 

[JHB]

Thanks for the example.

My tables all have different column names and I would be searching through all table columns for each search. I'm not looking to select each field name for each search because there will be 100's of fields in that list. Also, I'd want to be able to enter a search query with keywords that would search through the field list for those keywords and return the MyID from the row that the keyword was found in (or found to have the -1 checkbox in). Is there any way to accomplish that?

I think I've given you a good example how you could provide the above.
I'll leave to you, because there are no difficulties in it.

 

[TWins]

Interesting... as programming exercise.
On the other hand, from what I seen in your table, it is a many-to-many relationship between users and their skills (or rights).
So, this table should be split into a table with users, a table with skills (rights) and one more (bridge) table user-skill.

Good point. I already have a table with users, but adding a separate skills one along with the bridged one is a good idea. Thanks.

I think I've given you a good example how you could provide the above.
I'll leave to you, because there are no difficulties in it.

You have provided me with a good example but not an adequate one for what I'm trying to accomplish. I don't think you truly understand what I'm trying to do if you're saying there are no difficulties. Thanks for your help though.

 

[TWins]

Solved

In case anyone is interested, I solved my issue with a simple workaround. I converted all of my checkboxes to listboxes (and added the label text to each box), made them the same size and shape of a checkbox, changed to font colour to white and added coding for the dbl-click event to Null the list box. This way it can be selected and unselected similarly to a checkbox.

This method of storing the information in the tables allows the data to be more easily searchable since it's stored as text. Thus, eliminating the need to figure out how to search checkbox labels.

Cheers