Solved Keyword search - best method to stack the searches

[coasterman]

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?

 

[theDBguy]

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]

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]

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]

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]

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]

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.

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

If a multiselect list would do then that is easy.

 

[bastanu]

Please have a look at the attached sample and let me know if that is what you're after.
Cheers,
Vlad

 

[Mike Krailo]

That's pretty nice Vlad, I'll bet that is what he was after.

 

[coasterman]

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]

@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]

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]

you can search Without using any extra table or recordset.
just add a Private string to the Form's module:

 

[bastanu]

@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]

For visual clues, you can just use listbox to save the keywords.

 

[bastanu]

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....

 

[arnelgp]

filtering that doesn't seem to work with yours...

see this one, Without Extra table overhead.

 

[bastanu]

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

 

[arnelgp]

I'll let you develop this further,

it's a big NO. if the op is already happy, then there no point.

 

[bastanu]

Cheers,