Help with Where Statement

[Taff]

Hi All,

I have a form with A text box, Combo box and List box on. On the OnChange event of the text box I have the following:-

Private Sub ProductSearchtxt_Change()
Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me![ProductSearchtxt].Text

    strSQL = "SELECT DISTINCTROW [Products].ContractID, [Products].ProductID, [Products].Name, [Products].UnitCost, [Products].Points FROM [Products] "
strSQL = strSQL & "WHERE (([Products]." & Me.Combo6.Column(0) & ") Like '" & txtSearchString & "*')"
strSQL = strSQL & "ORDER BY [Products].ProductID, [Products].Name"

Me!listProducts.RowSource = strSQL
Me!listProducts.Requery
Me!ProductSearchtxt.SetFocus

End Sub

What I would like is to insert another where clause which says that the ContractID from the Products table = Text14 but am really unsure how to do it. My knowledge of the above is very limited and copied the code from another example and tailored it to suit my needs.

Any help would be greatly appreciated.

Many Thanks


Anthony

 

[Mile-O]

WHERE criteria AND othercriteria

 

[michaellysons]

I've added an extra line to your code that should help you out. It's after the first 'WHERE' clause.

Private Sub ProductSearchtxt_Change()
Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me![ProductSearchtxt].Text

    strSQL = "SELECT DISTINCTROW [Products].ContractID, [Products].ProductID, [Products].Name, [Products].UnitCost, [Products].Points FROM [Products] "
strSQL = strSQL & "WHERE (([Products]." & Me.Combo6.Column(0) & ") Like '" & txtSearchString & "*')  "

strSQL = strSQL & "  AND  Products.ContractID = " & Text14

strSQL = strSQL & "  ORDER BY [Products].ProductID, [Products].Name"

Me!listProducts.RowSource = strSQL
Me!listProducts.Requery
Me!ProductSearchtxt.SetFocus

End Sub

 

[Taff]

Hi guys thanks for the speedy replies.

I used michaellysons code and I am getting the Dubug window and the message:

Run-time Error '2447'

There is an invalid use of the .(dot) or ! operator or Invalid Parentheses.


Any ideas?

many thanks,

Ant.

 

[Mile-O]

Slight change.

Private Sub ProductSearchtxt_Change()

    Dim strSQL As String

    strSQL = "SELECT [Products].ContractID, [Products].ProductID, [Products].Name, [Products].UnitCost, [Products].Points FROM [Products] "
strSQL = strSQL & "WHERE (([Products]." & Me.Combo6.Column(0) & ") Like """ & Me.ProductSearchtxt & "*"")  "

    strSQL = strSQL & "  AND  Products.ContractID = " & Me.Text14

    strSQL = strSQL & "  ORDER BY [Products].ProductID, [Products].Name;"

    Me.listProducts.RowSource = strSQL
    Me.listProducts.Requery
    Me.ProductSearchtxt.SetFocus

End Sub

Name's a bad name for a field as it's a reserved word. I'd also consider building this into a QueryDef rather than an SQL statement in VBA.

 

[Mile-O]

WHERE (([Products]." & Me.Combo6.Column(0) & ") Like """ & Me.ProductSearchtxt & "*"")

I take it that this combobox contains a list of fields?

 

[Taff]

Hi Mile-O-Phile,

WHERE (([Products]." & Me.Combo6.Column(0) & ") Like """ & [/QUOTE] 

Combo6 does contain field names, however with the code you provided I receive no errors however no information is listed in the Listbox.

Regards,
Ant.

 

[Taff]

Not sure if it is any more help but attached is smaller version of what I am trying to accomplish.

Many Thanks
Ant.

 

[Jon K]

I have modified your code slightly.

 

[Taff]

Hi Jon,

Thanks for replying, however the list box still displays products for all contracts. I am trying to get it to only display products with the same contractID as that of Text5.

Regards,
Anthony

 

[Jon K]

I have added the text box in the code.

 

[Taff]

Jon,

Exactly what I wanted.

Thanks Mate, greatly appreciated.

Ant.