Help with Query and Subform

[mforema]

Hi everybody,

I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureAr ticles:

Abbreviation(FK )
ArticleID(PK)
Author
Title
etc...

tblArticleKeywo rd (Join Table btn tblLiteratureAr ticles & tblKeywords)

ArticleID
Keyword
(these two create a composite key)

tblKeywords

Keyword(PK)

tblLitCategorie s (one-to-many relationship with tblLiteratureAr ticles)

MajorCategory
Abbreviation(PK )
Selected(Yes/No field)

I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCateg ories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.

I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?

Thanks for your help/suggestions!

[mforema]

Hi everybody,

I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureAr ticles:

Abbreviation(FK )
ArticleID(PK)
Author
Title
etc...

tblArticleKeywo rd (Join Table btn tblLiteratureAr ticles & tblKeywords)

ArticleID
Keyword
(these two create a composite key)

tblKeywords

Keyword(PK)

tblLitCategorie s (one-to-many relationship with tblLiteratureAr ticles)

MajorCategory
Abbreviation(PK )
Selected(Yes/No field)

I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCateg ories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.

I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?

Thanks for your help/suggestions!

I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?

SELECT
tblLiteratureAr ticles.Abbrevia tion,
tblLiteratureAr ticles.ArticleI D,
tblLiteratureAr ticles.Principa lAuthor,
tblLiteratureAr ticles.TITLE,
tblLiteratureAr ticles.Journal,
tblLiteratureAr ticles.Volume,
tblLiteratureAr ticles.Number,
tblLiteratureAr ticles.PAGES,
tblLiteratureAr ticles.DATE,
tblLiteratureAr ticles.OtherAut hors,
tblLiteratureAr ticles.Link,
tblArticleKeywo rd.Keyword
FROM
(tblLitCategori es INNER JOIN tblLiteratureAr ticles ON tblLitCategorie s.Abbreviation = tblLiteratureAr ticles.Abbrevia tion) INNER JOIN tblArticleKeywo rd ON tblLiteratureAr ticles.ArticleI D = tblArticleKeywo rd.[Article ID]
WHERE
(((tblArticleKe yword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategor ies.Selected)=T rue));

[FishVal]

I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?

SELECT
tblLiteratureAr ticles.Abbrevia tion,
tblLiteratureAr ticles.ArticleI D,
tblLiteratureAr ticles.Principa lAuthor,
tblLiteratureAr ticles.TITLE,
tblLiteratureAr ticles.Journal,
tblLiteratureAr ticles.Volume,
tblLiteratureAr ticles.Number,
tblLiteratureAr ticles.PAGES,
tblLiteratureAr ticles.DATE,
tblLiteratureAr ticles.OtherAut hors,
tblLiteratureAr ticles.Link,
tblArticleKeywo rd.Keyword
FROM
(tblLitCategori es INNER JOIN tblLiteratureAr ticles ON tblLitCategorie s.Abbreviation = tblLiteratureAr ticles.Abbrevia tion) INNER JOIN tblArticleKeywo rd ON tblLiteratureAr ticles.ArticleI D = tblArticleKeywo rd.[Article ID]
WHERE
(((tblArticleKe yword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategor ies.Selected)=T rue));

Hi.

I'd like to bring your back to the post -

http://www.thescripts.com/forum/thread671666.html msg#4

Add this function to a public module. SQL will look like this

SELECT 
tblLiteratureArticles.Abbreviation, 
tblLiteratureArticles.ArticleID, 
tblLiteratureArticles.PrincipalAuthor, 
tblLiteratureArticles.TITLE, 
tblLiteratureArticles.Journal, 
tblLiteratureArticles.Volume, 
tblLiteratureArticles.Number, 
tblLiteratureArticles.PAGES, 
tblLiteratureArticles.DATE, 
tblLiteratureArticles.OtherAuthors, 
tblLiteratureArticles.Link, 
tblArticleKeyword.Keyword
FROM 
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE 
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True;

[mforema]

Hi.

I'd like to bring your back to the post -

http://www.thescripts.com/forum/thread671666.html msg#4

Add this function to a public module. SQL will look like this

SELECT 
tblLiteratureArticles.Abbreviation, 
tblLiteratureArticles.ArticleID, 
tblLiteratureArticles.PrincipalAuthor, 
tblLiteratureArticles.TITLE, 
tblLiteratureArticles.Journal, 
tblLiteratureArticles.Volume, 
tblLiteratureArticles.Number, 
tblLiteratureArticles.PAGES, 
tblLiteratureArticles.DATE, 
tblLiteratureArticles.OtherAuthors, 
tblLiteratureArticles.Link, 
tblArticleKeyword.Keyword
FROM 
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE 
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True;

Thanks! I pasted the SQL exactly, and I also pasted the function into a module. But it's not working. I tried to type in two keywords into the textbox(txtKeyw ords), but I got no results in the query. Was I supposed to change something in the function?

Thanks for your help!

[mforema]

Thanks! I pasted the SQL exactly, and I also pasted the function into a module. But it's not working. I tried to type in two keywords into the textbox(txtKeyw ords), but I got no results in the query. Was I supposed to change something in the function?

Thanks for your help!

The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
ByVal varField As Variant) As Boolean
Dim intPos As Integer
Dim strKeyWord As String
 
KeyWordsInStr = False
 
Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
strKeyWord = Trim(strKeyWords)
Else
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
 
KeyWordsInStr = True
End Function

[kepston]

The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it.

[CODE=vb]Public Function KeyWordsInStr(B yVal strKeyWords As String, _
ByVal varField As Variant) As Boolean
Dim intPos As Integer
Dim strKeyWord As String

KeyWordsInStr = False

Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
strKeyWord = Trim(strKeyWord s)
Else
strKeyWord = Trim(Left(strKe yWords, intPos - 1))
strKeyWords = Trim(Mid(strKey Words, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0

KeyWordsInStr = True
End Function[/CODE]

I have been doing something similar.
The code (quoted here) worked for me.
Are you separating your search keywords with commas?
Have you tried to debug the code yourself, using the 'step through' facility within VBA?

[mforema]

I have been doing something similar.
The code (quoted here) worked for me.
Are you separating your search keywords with commas?
Have you tried to debug the code yourself, using the 'step through' facility within VBA?

Yes, I separate the keywords by commas.
I have tried to debug the code myself, but I have not used the 'step through' facility; I've never heard of it. If the code worked for you, I am even more confused. I copy-and-pasted the SQL and the Function code, so I shouldn't have any syntax errors.
Do you think the problem could be in the way my tables are set-up? I have the keywords in a join table, so that each article can be assigned mutliple keywords using a composite key. So, article 1 could be paired with more than one keyword. Is this code designed to take that into account, or does it even matter?

Thanks for your help!

[FishVal]

The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
ByVal varField As Variant) As Boolean
Dim intPos As Integer
Dim strKeyWord As String
 
KeyWordsInStr = False
 
Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
strKeyWord = Trim(strKeyWords)
Else
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
 
KeyWordsInStr = True
End Function

Hi and apologies. Certainly this code will not work anyway. Here is working one.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal varField As Variant) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
    Loop Until intPos = 0
     
End Function

[mforema]

Hi and apologies. Certainly this code will not work anyway. Here is working one.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal varField As Variant) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
    Loop Until intPos = 0
     
End Function

Awesome! It worked! Well, kinda...there are two problems:

1. It didn't narrow the search. If I type in two keywords like this, "distillati on, column", the query will return articles that have either one keyword or the other. I need it to only show the articles that have both keywords.

2. When the results show up in datasheet view, articles show up multiple times. For instance, article 4 has distillation and column as keywords. If I type in 'distillation, column' into the search box, article 4 is displayed twice - one time for 'distillation' and one time for 'column.'

Thanks for your help! I will keep trying!

[mforema]

Awesome! It worked! Well, kinda...there are two problems:

1. It didn't narrow the search. If I type in two keywords like this, "distillati on, column", the query will return articles that have either one keyword or the other. I need it to only show the articles that have both keywords.

2. When the results show up in datasheet view, articles show up multiple times. For instance, article 4 has distillation and column as keywords. If I type in 'distillation, column' into the search box, article 4 is displayed twice - one time for 'distillation' and one time for 'column.'

Thanks for your help! I will keep trying!

Okay, I think I got it fixed.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal varField As Variant) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
    Loop Until intPos = 0
    If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
 
End Function

I only need to show the article if the last keyword typed into the search box is a match. For instance, if I again use "distillati on, column" in the search box, each article will be searched for the first keyword and then the second keyword. Well, if the second keyword is a match then the article will show, BUT if the second keyword is not a match then the article will not show. I did this by putting the If Then statement outside the Do Loop.

I will keep testing it, but I think it's right!

Thanks again for your help!

[mforema]

Okay, I think I got it fixed.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal varField As Variant) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
    Loop Until intPos = 0
    If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
 
End Function

I only need to show the article if the last keyword typed into the search box is a match. For instance, if I again use "distillati on, column" in the search box, each article will be searched for the first keyword and then the second keyword. Well, if the second keyword is a match then the article will show, BUT if the second keyword is not a match then the article will not show. I did this by putting the If Then statement outside the Do Loop.

I will keep testing it, but I think it's right!

Thanks again for your help!

NOOOO! It doesn't work! I tried switching the keywords' order from "distillati on, column" to "column, distillation." Well, some articles only have "distillati on" as a keyword. So, since the code only chooses based on the last keyword, the query shows only the articles for the last keyword. I don't know why I thought it would work. I guess I jumped the gun :(

But, I will keep trying!

[kepston]

NOOOO! It doesn't work! I tried switching the keywords' order from "distillati on, column" to "column, distillation." Well, some articles only have "distillati on" as a keyword. So, since the code only chooses based on the last keyword, the query shows only the articles for the last keyword. I don't know why I thought it would work. I guess I jumped the gun :(

But, I will keep trying!

To step through your code you need to set a breakpoint - press F9 on a line of code (or Menu bar, Debug, Toggle Breakpoint. Or click in the vertical grey bar to the left of the code line), a brown spot will appear in the vertical grey bar and the line of code will be highlighted brown.
When the code is executed, it will run until the breakpoint is encountered.
From that point you can step through line by line, using F8 (other options available from Debug menu). You can see the values of your variables by hovering over their name with your mouse pointer or by right-clicking and selecting Add Watch.
This way you can see exactly what is happening and why.
Often you will want to stop the code - Menu bar, Run, Reset (or Reset button - looks like a VCR stop button, i.e. solid square)
Other times you will want the code to run without prompting - Menu bar, Run, Continue (F5), (or Continue button - VCR play)

Back to your code:
By placing the test for keywords outside of the loop, you will only be testing the current, i.e. last keyword, effectively ignoring all the others.
Testing inside the loop compares every keyword in order.
[CODE=vb]If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
Loop Until intPos = 0[/CODE]Will set the comparison to True for ANY keyword match

[CODE=vb]If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
KeyWordsInStr = True[/CODE]Will set the comparison to False for the first keyword that IS NOT found.

If you are using the function in it's intended manor, then it will be called for every record in your table.
Note that the field containing your keywords must contain all of the keywords, and not a separate record for each keyword.
E.g.
Keywords = "Distillati on Column Retort Fraction"
Article = 4

Not:
Keyword = "Distillati on", Article = 4
Keyword = "Column", Article = 4
etc

Hopefully that clarifies a few points.

[mforema]

To step through your code you need to set a breakpoint - press F9 on a line of code (or Menu bar, Debug, Toggle Breakpoint. Or click in the vertical grey bar to the left of the code line), a brown spot will appear in the vertical grey bar and the line of code will be highlighted brown.
When the code is executed, it will run until the breakpoint is encountered.
From that point you can step through line by line, using F8 (other options available from Debug menu). You can see the values of your variables by hovering over their name with your mouse pointer or by right-clicking and selecting Add Watch.
This way you can see exactly what is happening and why.
Often you will want to stop the code - Menu bar, Run, Reset (or Reset button - looks like a VCR stop button, i.e. solid square)
Other times you will want the code to run without prompting - Menu bar, Run, Continue (F5), (or Continue button - VCR play)

Back to your code:
By placing the test for keywords outside of the loop, you will only be testing the current, i.e. last keyword, effectively ignoring all the others.
Testing inside the loop compares every keyword in order.
[CODE=vb]If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
Loop Until intPos = 0[/CODE]Will set the comparison to True for ANY keyword match

[CODE=vb]If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
KeyWordsInStr = True[/CODE]Will set the comparison to False for the first keyword that IS NOT found.

If you are using the function in it's intended manor, then it will be called for every record in your table.
Note that the field containing your keywords must contain all of the keywords, and not a separate record for each keyword.
E.g.
Keywords = "Distillati on Column Retort Fraction"
Article = 4

Not:
Keyword = "Distillati on", Article = 4
Keyword = "Column", Article = 4
etc

Hopefully that clarifies a few points.

So, I shouldn't have a join table with a composite key as described in my first post of this discussion? I should have all keywords in the same field for each article/record? I tried that at first, but I was posting on another discussion, and someone told me that my tables weren't normalized. He/She said that I should use a composite key in a join table. I understand the code now, but I am confused about my tables.

Thanks for your help!

[kepston]

So, I shouldn't have a join table with a composite key as described in my first post of this discussion? I should have all keywords in the same field for each article/record? I tried that at first, but I was posting on another discussion, and someone told me that my tables weren't normalized. He/She said that I should use a composite key in a join table. I understand the code now, but I am confused about my tables.

Thanks for your help!

Can you post a link to the other discussion for me please?

[mforema]

Can you post a link to the other discussion for me please?

http://www.thescripts.com/forum/thre...arch+form.html - post #7