Keyword search Form based on Union Query (1 Viewer)

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Hi Folks,

I have a form with a continuous sub form in it, I'm trying to get a keyword search to work. But as the continuous form is based on a union query from 3 tables I'm having a struggle.

The keyword search is for one column only 'Product'. The problem is in two of the three tables the 'Product' column isn't called product it's called 'part' and 'used on' so to get the query to work its 'Part As Product' and 'Used on As Product'. My WHERE clause is therefore wrong, any tips?

I have the following code behind an on click event of a button:

Code:
Private Sub SearchBtn_Click()
 Dim SQL As String
    
SQL = "SELECT [DDP Issue Control].[DDP Ref] AS [Document Number], [DDP Issue Control].Description, [DDP Issue Control].Part AS Product, [DDP Issue Control].Type, [DDP Issue Control].[File Name], [DDP Issue Control].[Hyperlink to File] FROM [DDP Issue Control] 
UNION SELECT [AS Specs Issue Control].[Specification No] AS [Document Number], [AS Specs Issue Control].[Title of Specification] AS Description, [AS Specs Issue Control].[Used On] AS Product, [AS Specs Issue Control].[Doc Type] AS Type, [AS Specs Issue Control].FileName AS [File Name], [AS Specs Issue Control].[Hyperlink to File] FROM [AS Specs Issue Control] 
UNION SELECT [CMM Issue Control].[CMM No] AS [Document Number], [CMM Issue Control].Description, [CMM Issue Control].Product, [CMM Issue Control].Type, [CMM Issue Control].[File Name], [CMM Issue Control].[Hyperlink to File] FROM [CMM Issue Control] 
[COLOR=yellow][COLOR=black]WHERE [Product] Like "" * " & Me.KeywordsTxt & " * "";"[/COLOR] [/COLOR]
 
Me.SubProductList.Form.RecordSource = SQL
Me.SubProductList.Requery

End Sub

Thanks!! =]
 

plog

Banishment Pending
Local time
Today, 17:30
Joined
May 11, 2011
Messages
11,612
Your WHERE clause is only working with the last SELECT, the first two are not having the criteria applied to it. The way to fix that is to have a WHERE on every SELECT...however...

I don't think your tables are properly structured. Why do you have 3 tables so similiar in structure? You should have all that data in one table, then have another field that tells you if the record is for CMM, DDP or As Specs. You are essentially storing a value that should go into a field in the table name. That is wrong.
 

Minty

AWF VIP
Local time
Today, 22:30
Joined
Jul 26, 2013
Messages
10,355
I think you just need to add or clauses to your WHERE - something like;
Code:
WHERE [Product] Like *" & Me.KeywordsTxt & "*  OR [Used By] Like *" & Me.KeywordsTxt & "* OR [Part] Like *" & Me.KeywordsTxt & "* ;"

I've removed extra spaces in your wildcard expression as well as some quotes that appeared superfluous.

This will get very slow if you have a large amount of data as putting wild cards in front of a search string will mean the indexes won't be used on the searched fields. (X3)

Edit - actually Plog is obviously correct you need the extra WHERE against each select.
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Your WHERE clause is only working with the last SELECT, the first two are not having the criteria applied to it. The way to fix that is to have a WHERE on every SELECT...however...

I don't think your tables are properly structured. Why do you have 3 tables so similiar in structure? You should have all that data in one table, then have another field that tells you if the record is for CMM, DDP or As Specs. You are essentially storing a value that should go into a field in the table name. That is wrong.

Hi,

Thanks for the speedy reply, I had figured the WHERE clause was only working for the last one. Do I place the individual WHERE clause's before the UNION's?

I thought someone might pick up on the table structure...The Data in the query isn't the only data in each table, I have only picked the columns from each table that are linked/related. I have forms to update each table so then getting those updates to follow to another table would get confusing I think.

Thanks for the tips! :)
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
I think you just need to add or clauses to your WHERE - something like;
Code:
WHERE [Product] Like *" & Me.KeywordsTxt & "*  OR [Used By] Like *" & Me.KeywordsTxt & "* OR [Part] Like *" & Me.KeywordsTxt & "* ;"
I've removed extra spaces in your wildcard expression as well as some quotes that appeared superfluous.

This will get very slow if you have a large amount of data as putting wild cards in front of a search string will mean the indexes won't be used on the searched fields. (X3)

Edit - actually Plog is obviously correct you need the extra WHERE against each select.

I tried the exact same thing first Minty!
I'm running access 2016 and keep getting errors when I remove the spaces and quote marks .
 
Last edited:

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Errrrrmmm... This is what I came up with but on click no values are shown even when the keywords text box is empty.

Code:
Private Sub SearchBtn_Click()
 Dim SQL As String
    
SQL = "SELECT [DDP Issue Control].[DDP Ref] AS [Document Number], [DDP Issue Control].Description, [DDP Issue Control].Part AS Product, [DDP Issue Control].Type, [DDP Issue Control].[File Name], [DDP Issue Control].[Hyperlink to File] FROM [DDP Issue Control] WHERE [Part] Like "" * " & Me.KeywordsTxt & " * "" " _
& "UNION SELECT [AS Specs Issue Control].[Specification No] AS [Document Number], [AS Specs Issue Control].[Title of Specification] AS Description, [AS Specs Issue Control].[Used On] AS Product, [AS Specs Issue Control].[Doc Type] AS Type, [AS Specs Issue Control].FileName AS [File Name], [AS Specs Issue Control].[Hyperlink to File] FROM [AS Specs Issue Control]  WHERE [Used On] Like "" * " & Me.KeywordsTxt & " * "" UNION SELECT [CMM Issue Control].[CMM No] AS [Document Number], [CMM Issue Control].Description, [CMM Issue Control].Product, [CMM Issue Control].Type, [CMM Issue Control].[File Name], [CMM Issue Control].[Hyperlink to File] FROM [CMM Issue Control] WHERE [Product] Like "" * " & Me.KeywordsTxt & " * "";"
 
Me.SubProductList.Form.RecordSource = SQL
Me.SubProductList.Requery
End Sub
 

plog

Banishment Pending
Local time
Today, 17:30
Joined
May 11, 2011
Messages
11,612
Here's your fishing lesson for the day: Divide, simplify and conquer.

Your query composed of 3 SELECTs isn't working as intended. Get it to work with 1 SELECT and then rebuild the query back up.
 

Minty

AWF VIP
Local time
Today, 22:30
Joined
Jul 26, 2013
Messages
10,355
You have added back the extra spaces and quotes I removed into you Like criteria. Look at the example I posted for each query individually - as per Plog's advice.

Currently you have have Like * Fred * if it even constructs correctly.

Add a Debug.Print SQL before your recordsource statement and look at what appears in the immediate window.
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Here's your fishing lesson for the day: Divide, simplify and conquer.

Your query composed of 3 SELECTs isn't working as intended. Get it to work with 1 SELECT and then rebuild the query back up.

Thanks, Great advice!
 

almahmood

Registered User.
Local time
Tomorrow, 04:00
Joined
Mar 28, 2017
Messages
47
Hi,

Use where clause for each SELECT statement or you can write this as nested query something like this and use WHERE clause for whole results:

Sql = "Select t.* from (SELECT [DDP Issue Control].[DDP Ref] AS [Document Number], [DDP Issue Control].Description, [DDP Issue Control].Part AS Product, [DDP Issue Control].Type, [DDP Issue Control].[File Name], [DDP Issue Control].[Hyperlink to File] FROM [DDP Issue Control]" & _
" UNION SELECT [AS Specs Issue Control].[Specification No] AS [Document Number], [AS Specs Issue Control].[Title of Specification] AS Description, [AS Specs Issue Control].[Used On] AS Product, [AS Specs Issue Control].[Doc Type] AS Type, [AS Specs Issue Control].FileName AS [File Name], [AS Specs Issue Control].[Hyperlink to File] FROM [AS Specs Issue Control]" & _
" UNION SELECT [CMM Issue Control].[CMM No] AS [Document Number], [CMM Issue Control].Description, [CMM Issue Control].Product, [CMM Issue Control].Type, [CMM Issue Control].[File Name], [CMM Issue Control].[Hyperlink to File] FROM [CMM Issue Control]) as t" & _
" WHERE t.[Product] Like '*" & Me.KeywordsTxt & "*';"
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
You have added back the extra spaces and quotes I removed into you Like criteria. Look at the example I posted for each query individually - as per Plog's advice.

Currently you have have Like * Fred * if it even constructs correctly.

Add a Debug.Print SQL before your recordsource statement and look at what appears in the immediate window.

Hi Minty, I have attached a screen print of the error I get when I remove the quotes and space's.

I have never used Debug.Print before, I have added it into my code like you said and opened the immediate window but nothing happens. I'm not sure how it works.

Thanks for your advice! :)
 

Attachments

  • Database Error.png
    Database Error.png
    26.6 KB · Views: 160

sxschech

Registered User.
Local time
Today, 15:30
Joined
Mar 2, 2010
Messages
791
In case you are interested for future testing of the WHERE clauses prior to using in vba, this technique enables the query to be viewed in the graphical interface as a standard select query. Of course, if you need to edit the union query internals, that would still need to be done in the sql view.

Code:
SELECT u.*
FROM     (SELECT a, b, c FROM table1
    UNION
    SELECT h, i , k FROM table2
    UNION 
    SELECT w, j, m FROM table3
    ) AS u
WHERE a like ...
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Hi,

Use where clause for each SELECT statement or you can write this as nested query something like this and use WHERE clause for whole results:

Sql = "Select t.* from (SELECT [DDP Issue Control].[DDP Ref] AS [Document Number], [DDP Issue Control].Description, [DDP Issue Control].Part AS Product, [DDP Issue Control].Type, [DDP Issue Control].[File Name], [DDP Issue Control].[Hyperlink to File] FROM [DDP Issue Control]" & _
" UNION SELECT [AS Specs Issue Control].[Specification No] AS [Document Number], [AS Specs Issue Control].[Title of Specification] AS Description, [AS Specs Issue Control].[Used On] AS Product, [AS Specs Issue Control].[Doc Type] AS Type, [AS Specs Issue Control].FileName AS [File Name], [AS Specs Issue Control].[Hyperlink to File] FROM [AS Specs Issue Control]" & _
" UNION SELECT [CMM Issue Control].[CMM No] AS [Document Number], [CMM Issue Control].Description, [CMM Issue Control].Product, [CMM Issue Control].Type, [CMM Issue Control].[File Name], [CMM Issue Control].[Hyperlink to File] FROM [CMM Issue Control]) as t" & _
" WHERE t.[Product] Like '*" & Me.KeywordsTxt & "*';"

Hi,

Thank you for your response, I just tried your suggestion and while I got no errors the results are records are not displaying. Just blank when you click. Will go back to trying individual WHERE statements.

Thanks :)
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
In case you are interested for future testing of the WHERE clauses prior to using in vba, this technique enables the query to be viewed in the graphical interface as a standard select query. Of course, if you need to edit the union query internals, that would still need to be done in the sql view.

Code:
SELECT u.*
FROM     (SELECT a, b, c FROM table1
    UNION
    SELECT h, i , k FROM table2
    UNION 
    SELECT w, j, m FROM table3
    ) AS u
WHERE a like ...


Thanks for the tip! Will definitely try this :)
 

almahmood

Registered User.
Local time
Tomorrow, 04:00
Joined
Mar 28, 2017
Messages
47
Hi,

Thank you for your response, I just tried your suggestion and while I got no errors the results are records are not displaying. Just blank when you click. Will go back to trying individual WHERE statements.

Thanks :)

Could you please check if there is any data with that criteria? Also, add Me.Requery after setting the RowSource and see if anything shows.
 

Minty

AWF VIP
Local time
Today, 22:30
Joined
Jul 26, 2013
Messages
10,355
Try this if all else fails
Code:
Private Sub SearchBtn_Click()
   Dim SQL As String
   
   SQL = "SELECT [DDP Issue Control].[DDP Ref] AS [Document Number], [DDP Issue Control].Description, [DDP Issue Control].Part AS Product, [DDP Issue Control].Type, [DDP Issue Control].[File Name], [DDP Issue Control].[Hyperlink to File] "
   SQL = SQl & "FROM [DDP Issue Control] WHERE [Part] Like '*" & Me.KeywordsTxt & "*' "
   SQL = SQl & "UNION "
   SQL = SQl & "SELECT [AS Specs Issue Control].[Specification No] AS [Document Number], [AS Specs Issue Control].[Title of Specification] AS Description, [AS Specs Issue Control].[Used On] AS Product, [AS Specs Issue Control].[Doc Type] AS Type, [AS Specs Issue Control].FileName AS [File Name], [AS Specs Issue Control].[Hyperlink to File] "
   SQL = SQl & "FROM [AS Specs Issue Control]  WHERE [Used On] Like '*" & Me.KeywordsTxt & "*' "
   SQL = SQl & "UNION "
   SQL = SQl & "SELECT [CMM Issue Control].[CMM No] AS [Document Number], [CMM Issue Control].Description, [CMM Issue Control].Product, [CMM Issue Control].Type, [CMM Issue Control].[File Name], [CMM Issue Control].[Hyperlink to File] "
   SQL = SQl & "FROM [CMM Issue Control] WHERE [Product] Like '*" & Me.KeywordsTxt & "*' ;"
 
   Debug.Print SQL   [COLOR="Green"] ' Press Ctrl & G in the debug window to show the immediate window.[/COLOR]

   Me.SubProductList.Form.RecordSource = SQL
   Me.SubProductList.Requery 

End Sub

I find it neater and easier to read this style of construct without the continuation symbols. Just my personal preference.
 

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Thank you all for your help, annoyingly I didn't manage to get my search form working with the union query I gave up when I realised Hyperlink fields built from the query were no longer active.

I did get a working form I just went about it a different way.

My solution was... rather than having one sub form built from a query from 3 tables I just has three sub forms in my search form that were linked to the same keyword search textbox.

I actually think it looks neater this way so it worked out well!

Thanks again for all the tips!
 
Last edited:

SimoneRene

Registered User.
Local time
Today, 22:30
Joined
Mar 15, 2017
Messages
58
Here's your fishing lesson for the day: Divide, simplify and conquer.

Your query composed of 3 SELECTs isn't working as intended. Get it to work with 1 SELECT and then rebuild the query back up.

Your advice to divide, simplify and conquer definitely helped me get to a solution and look at the problem from a different angle! Thanks.
 

Users who are viewing this thread

Top Bottom