Solved Keyword search - best method to stack the searches (1 Viewer)

coasterman

Registered User.
Local time
Today, 13:13
Joined
Oct 1, 2012
Messages
59
I have a main form with a number of subforms one of which is a continuous form for adding keywords about the parent record. There is a many to many relationsip between the two tables.

I then Select the parent records by an SQL Inner Join (FK of the keyword added from combo on mainfrm) and that all works fine but its limited to just the one search was is very limiting.

What I dont know how to do is select a second keyword (and so on) to further contsrain the records returned by the first and any subsequent keywords selected - at a guess I need to be able to modify the first SQL statement to append the additional criteria of the FK of the Keyword selected? If correct then I'm not clear on how to write that.

In the attached cut down example 5 of the 10 parent records have 'square' as a keyword - what I would like to be able to do is select say 'circle' and only have the 1 record where 'circle' and 'square' appear rather then the 3 records where 'circle' appears as a keyword. I should mention that due to the subject matter the users wont always know what their 2nd and 3rd choices will be and are likley to be guided by the 'whats left' portion. There are likely to be 100's of keywords as the parent record count increases so a method by which the combo box could display only what is availble (after the first keyword is selected) would be a fantastic addition if thats even possible?
 

Attachments

  • keyword search1.zip
    36.8 KB · Views: 36
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 13:13
Joined
Oct 29, 2018
Messages
15,230
Hi. Showing "what's left" in succeeding comboboxes is called a "cascading combobox." There are plenty of tutorials on how to create those. I'll take a look at your file when I get a chance, just thought you might try doing a search on it in the meantime. Cheers!

Sent from phone...
 

coasterman

Registered User.
Local time
Today, 13:13
Joined
Oct 1, 2012
Messages
59
Hi , I do have cascading combo boxes on the main form already with certain users having 'not in list' privileges but the keyword bit is a much looser affair. The keywords will be looked after entirely by the users and they can add whatever they like that is meaningful to them individually.

Hopefully the parent record ends up with a cluster of keywords (some of which may just be synonyms of keywords already entered) but as the users have had the freedom to choose they, collectively, will have described enough about the records content that it can be found easily at a future date. As the record is re-visited more keywords may be entered so increasing the potential 'hit' ratio on the search.

The anaology for the data I am working with might be random magazine articles - there will be attributes of the articles content which I can more easily classify and manage with the cascading combo boxes but other aspects of the article say a name, a place, a mood etc which may only appear in a handful of the 1000s of parent records and keywords seemed the way to go.

The search is the problem for me as I neeed to give flexiblity to the user to think of their own search criteria which may be something they remembered tagging some weeks before or a keyword they remembered when simply browsing. Having made their initial choice, and reduced the parent record count, I would like the keyword combo to just return those keywords that exist in the Parent records after that first selection and once selected have it applied to just the pool of records that were returned after the first search. The user being able see what keywords 'remain' should help inform their second selection rather than having to simply guess or scrolldown a much longer list.

I may need to think about the OR aspect of the SQL in the future (synonyms being the most likely issue) but at the moment I'd be just as happy to get the stacking/filtering concept working.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:13
Joined
Oct 29, 2018
Messages
15,230
Hi. I just downloaded your file to take a look. When I open the form, there are only three records showing and none of the has circle in it. Not sure I can use it to understand what you need.
 

coasterman

Registered User.
Local time
Today, 13:13
Joined
Oct 1, 2012
Messages
59
I must have saved it having entered the first keyword search - apologies, the reset button on main form should revert the recordsource count back to 9 parent records
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:13
Joined
May 21, 2018
Messages
5,037
I am a little confused on how you would want this to work.
I would think having a multi select listbox where you can pick as many keywords and return those records that match all the keywords, but that does not seem what you want. I do not get the "just return 1 record"

If I pick 1 keyword, you want the first record returned to be the parent Record. What is the rule for what is the first record. Then you only want the keywords from the first record. I do not get that logic, but if that is correct then I would have a single select combobox followed by a multi select listbox. Select the key word in the combo, find the keywords related to the first record and populate the listbox. Then use the listbox to return records that match any keywords selected in the multiselect. That is what I interpret, and that does not make any sense to me.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:13
Joined
May 21, 2018
Messages
5,037
Maybe something similar to this. This gave the OP a chance to select multiple potential key words. Then pick as many as those keywords to either require the returned records to have all or some of the keywords.


If a multiselect list would do then that is easy.
 

bastanu

AWF VIP
Local time
Today, 13:13
Joined
Apr 13, 2010
Messages
803
Please have a look at the attached sample and let me know if that is what you're after.
Cheers,
Vlad
 

Attachments

  • keyword searchVlad.zip
    47 KB · Views: 43

Mike Krailo

Active member
Local time
Today, 16:13
Joined
Mar 28, 2020
Messages
287
That's pretty nice Vlad, I'll bet that is what he was after.
 

coasterman

Registered User.
Local time
Today, 13:13
Joined
Oct 1, 2012
Messages
59
Hi Vlad, just seen this and exactly relates to the concept I was attempting to describe. I will take time to study the VBA, I kind of understand the logic behind it it but will definately need more study on my part but thank you so much and I wiill see it is put to good use.
 

coasterman

Registered User.
Local time
Today, 13:13
Joined
Oct 1, 2012
Messages
59
@MajP had a look at the list box search. It wasn't really the fit for my particular situation but I have definately bookmarked it for study , excellent stuff
 

bastanu

AWF VIP
Local time
Today, 13:13
Joined
Apr 13, 2010
Messages
803
Glad to be able to help, the code needs some cleanup (close the recordset and set to nothing, etc.) but should be failry easy to follow and interpret. And I hope that is what you wanted for the combo box to progressively eliminate the choices already made.

Cheers,
 

arnelgp

error reading drive A:
Local time
Tomorrow, 04:13
Joined
May 7, 2009
Messages
12,838
you can search Without using any extra table or recordset.
just add a Private string to the Form's module:
 

Attachments

  • keyword search1.zip
    47.1 KB · Views: 19

bastanu

AWF VIP
Local time
Today, 13:13
Joined
Apr 13, 2010
Messages
803
@arnelgp
got some compile errors and after fixing those still can't get the expected result (adding "square" and "circle" in any order should get you record 43 only). Also I think it would be helpful as a visual clue to see what keywords were already used and also to remove those from the combo's source as it was one of the requests in the initial post.
Cheers,
 

arnelgp

error reading drive A:
Local time
Tomorrow, 04:13
Joined
May 7, 2009
Messages
12,838
For visual clues, you can just use listbox to save the keywords.
 

bastanu

AWF VIP
Local time
Today, 13:13
Joined
Apr 13, 2010
Messages
803
Of course you can, among many other ways ( like appending to a textbox), I was trying to give the OP an integrated solution for most of his requests, especially the progressive filtering that doesn't seem to work with yours....
 

bastanu

AWF VIP
Local time
Today, 13:13
Joined
Apr 13, 2010
Messages
803
Sorry but still doesn't work for me, and the number of code lines seems to be going up.... we have loops now and private subs, not just one private string anymore as advertised.... I'll let you develop this further, I am sure you'll come up with a better solution than mine, at least we don't have a timer...(I'm trying to be funny not sarcastic, I really enjoy your posts and I've learned a lot from you)!
Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom