473,597 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with Query and Subform

72 New Member
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!
Jul 25 '07 #1
23 2429
mforema
72 New Member
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));
Jul 26 '07 #2
FishVal
2,653 Recognized Expert Specialist
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
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. tblLiteratureArticles.Abbreviation, 
  3. tblLiteratureArticles.ArticleID, 
  4. tblLiteratureArticles.PrincipalAuthor, 
  5. tblLiteratureArticles.TITLE, 
  6. tblLiteratureArticles.Journal, 
  7. tblLiteratureArticles.Volume, 
  8. tblLiteratureArticles.Number, 
  9. tblLiteratureArticles.PAGES, 
  10. tblLiteratureArticles.DATE, 
  11. tblLiteratureArticles.OtherAuthors, 
  12. tblLiteratureArticles.Link, 
  13. tblArticleKeyword.Keyword
  14. FROM 
  15. (tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
  16. WHERE 
  17. KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True; 
Jul 26 '07 #3
mforema
72 New Member
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
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. tblLiteratureArticles.Abbreviation, 
  3. tblLiteratureArticles.ArticleID, 
  4. tblLiteratureArticles.PrincipalAuthor, 
  5. tblLiteratureArticles.TITLE, 
  6. tblLiteratureArticles.Journal, 
  7. tblLiteratureArticles.Volume, 
  8. tblLiteratureArticles.Number, 
  9. tblLiteratureArticles.PAGES, 
  10. tblLiteratureArticles.DATE, 
  11. tblLiteratureArticles.OtherAuthors, 
  12. tblLiteratureArticles.Link, 
  13. tblArticleKeyword.Keyword
  14. FROM 
  15. (tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
  16. WHERE 
  17. 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!
Jul 27 '07 #4
mforema
72 New Member
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) As Boolean
  3. Dim intPos As Integer
  4. Dim strKeyWord As String
  5.  
  6. KeyWordsInStr = False
  7.  
  8. Do
  9. intPos = InStr(1, strKeyWords, ",")
  10. If intPos = 0 Then
  11. strKeyWord = Trim(strKeyWords)
  12. Else
  13. strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14. strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15. End If
  16. If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. End Function
Jul 27 '07 #5
kepston
97 Recognized Expert New Member
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) As Boolean
  3. Dim intPos As Integer
  4. Dim strKeyWord As String
  5.  
  6. KeyWordsInStr = False
  7.  
  8. Do
  9. intPos = InStr(1, strKeyWords, ",")
  10. If intPos = 0 Then
  11. strKeyWord = Trim(strKeyWords)
  12. Else
  13. strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14. strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15. End If
  16. If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. End Function
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?
Jul 27 '07 #6
mforema
72 New Member
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!
Jul 27 '07 #7
FishVal
2,653 Recognized Expert Specialist
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) As Boolean
  3. Dim intPos As Integer
  4. Dim strKeyWord As String
  5.  
  6. KeyWordsInStr = False
  7.  
  8. Do
  9. intPos = InStr(1, strKeyWords, ",")
  10. If intPos = 0 Then
  11. strKeyWord = Trim(strKeyWords)
  12. Else
  13. strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14. strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15. End If
  16. If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. End Function
Hi and apologies. Certainly this code will not work anyway. Here is working one.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  17.     Loop Until intPos = 0
  18.  
  19. End Function
  20.  
Jul 27 '07 #8
mforema
72 New Member
Hi and apologies. Certainly this code will not work anyway. Here is working one.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  17.     Loop Until intPos = 0
  18.  
  19. End Function
  20.  
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!
Jul 27 '07 #9
mforema
72 New Member
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.     Loop Until intPos = 0
  17.     If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  18.  
  19. 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!
Jul 27 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
5171
by: Kunal | last post by:
Hi, I need some help on writing an update query to update "UnitsSold" field in Products Table whenever I save a transaction. The transaction may contain several "Subtransactions", one for each product sold in that transaction. I have a subtransaction table that maintains record of each subtransaction, with data such as: ProductID, Quantity, TransactionID etc. The Products table has fields: ProductID, UnitsSold, UnitsOrdered,...
2
5238
by: John | last post by:
Hi - I am trying to perform a simple append query, with no luck. I have a table (MktPrices) that has the following fields: BondID, PriceDate, Price. The objective is to allow the user to input a date and have the append query create a new record for each BondID, using the input date and the most recent price - which the user can then update on a tabular form. I have created a totals query (qryMostRecentPrice) which selects the most...
3
3413
by: radioman | last post by:
Hi all, I would appreciate some help please. I just need pointing in the right direction as I am at a loss. Basically I have a form (frmAddMaster) which displays two subforms "Master Stock subform" and "Client Stock Template subform". Each subform contains the following fields "Product ID,Product Description and Size". What I am trying to do is to select rows from the "Master Stock subform" and copy them to "Client Stock Template...
2
1363
by: Josh Felker | last post by:
Hey everyone. I have a daily log form with my investing info. In that form I have 2 subforms, the first shows my profits on all the stocks I traded that day, the second shows my profits on those stocks during special times of the day. I want to make a query that will show my total profits each day (sum of subform 1), my profits from subform 2 (sum of subform 2), and the difference between the two. I can't seem to make the difference...
4
2269
by: Laura | last post by:
Here's the situation: I'm trying to use an update query to copy data from one row to another. Here is the situation: I have 5 companies that are linked to each other. I need to show all 5 companies on a form. 3 of the companies have common employees. I have a table that looks like this:
14
3090
by: alwayshouston | last post by:
Hi All! I am working on this very small database and I am confused in the designing a simple form. I only have three tables in the database. First Table: tblExpense Columns: ExpenseID ; ExpenseType Data: 1 ; FOOD 2 ; AIRLINE 3 ; FARE
5
1845
by: Ron | last post by:
Hi All, I've got a form called frmCust that has a subform on it called sfrmBalance. sfrmBalance's control source is a query called qryTrans4CustBal. sfrmBalance has one field on it called fldTransTotal where the control source is =Sum(). LineTotal is derived from Quantity * Amount in the above query. I have a checkbox called chkActive on frmCust that I want to allow the operators to uncheck only if the balance of the customer is...
3
3330
by: mcmahonb | last post by:
Hey people... I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my side. I am trying to learn how to manipulate Dynamic Queries by forms via the example database: QrySampl.MDB, offered by Microsoft (as a learning tool, I suppose.) In particular, I am working with code from the example: "Query By Form (QBF) Using...
5
4891
by: samdev | last post by:
I have created two combo boxes in a subform.... For example 1. Combo Box State 2. Combo Box City 3. When a state from the Combo Box State is selected, the City combo box updates to reflect the State chosen by only showing cities in that selected state. 4. When I open just the subform it works just fine but when I open the
2
1384
by: adigga1 | last post by:
Good Day Experts, I have an issue dealing with a subform operation whereby, each time I select the value field of the subform, that same value is then placed into the source table as a new record: I do not want this to occur! What I want is for the subform to just reference the data contained in the source table and only be reflected in the Query that I had set up where my subform was created and runs from. Here is my code from the...
0
7894
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8392
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8046
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5437
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3894
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2410
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1500
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.