Query/Table and SLQ link

[thefroggy]

Hi,

Hope that someone can help me.

I currently have a list box with a Table/Query linked with “easy search” application based on SLQ programme, which is working nicely at the moment.

Table/Query

SELECT DISTINCT [Globaltlb].[ID] AS ID, [Globaltlb].[Product] AS Product WHERE ((([Globaltlb].[Product]) Like '*')) ORDER BY [Globaltlb].[Product];

SLQ query

Private Sub TxtNSC_Change()

Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me!TxtNSC.Text

strSQL = "SELECT DISTINCT Globaltlb.ID as ID, Globaltlb.Product as Product FROM Globaltlb "
strSQL = strSQL & "WHERE ((Globaltlb.product) Like '" & txtSearchString & "*') "
strSQL = strSQL & "ORDER BY Globaltlb.product"

Me!LstLocation.RowSource = strSQL
Me!LstLocation.Requery


I have been trying to improve my query by adding information from an second table linked with the master one. The list box Table/Query work nicely, but I cannot conect it with my "easy search" application. As soon as I enter a letter to start my selection the query in my list box change to fit the SLQ query programmed in VBA.

SELECT DISTINCT[ Globaltlb.ID AS ID, Globaltlb.Product AS Product, ED06.FR01 FROM ED06 LEFT JOIN Globaltlb ON ED06. code = Globaltlb. Code WHERE (((Globaltlb.Product) Like '*')) ORDER BY Globaltlb.Product;

Someone can help me to add FR01 from ED06 to my SLQ query?

Many thanks

 

[Frink]

Hallo,

Do you want:

    strSQL = "SELECT DISTINCT Globaltlb.ID as ID, Globaltlb.Product as Product FROM ED06 LEFT JOIN Globaltlb ON ED06.code = Globaltlb.Code "
    strSQL = strSQL & "WHERE ((Globaltlb.product) Like '" & txtSearchString & "*') "
    strSQL = strSQL & "ORDER BY Globaltlb.product"

?

- Frink

 

[PHV]

Anyway, as you test Globaltlb.product in the WHERE clause I'd change LEFT JOIN to RIGHT JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[thefroggy]

Hi Guys,

Thanks for the help,sorry for the delay a was travelling all week anyway the code works like a charm.

Thanks again

Steph