what is wrong with this code?

[Machlink]

I have need to limit selection available in a combox based on another combo box

If Not IsNull(Me!cboProtect) Then
Me!cboProtect.RowSource = "SELECT tblProtect.ID, tblProtect.Description" _
& " FROM (tblProtect Left Outer JOIN tblCauselookup ON tblCauselookup.CodeID )" _
& " WHERE ((tblProtect.CodeID=tblcauselookup.CodeID " & Me!Cbocause.Column(2) & ")) " _
& " ORDER BY tblProtect.CodeID;"
Else
Me!cboProtect.RowSource = "SELECT [ID],[Description]FROM [tblProtect]ORDER BY [ID] ASC; "

Thanks in advance

 

[PHV]

Your JOIN clause and your WHERE clause have both syntax error.

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

 

[lespaul]

to elaborate a little bit:

FROM (tblProtect  Left Outer JOIN tblCauselookup ON tblCauselookup.CodeID = [COLOR=blue]someothertable.CodeIDequivalent[/color])

WHERE (([COLOR=red]tblProtect.CodeID=tblcauselookup.CodeID[/color] " & Me!Cbocause.Column(2) & ")) "

In the blue section you need to add that information to your join.
In the red section you need to choose ONE of the fields and set that equal to Me!Cbocause.Column(2)

WHERE tblcauselookup.CodeID = " & Me!Cbocause.Column(2) & "))"

Leslie

 

[Machlink]

Hi Leslie!

I have made those changes still If I one pick animals in one combo box it is not filter in second combo box. what I need was suppose if user picks animals in cbocause it should bring all the animals codes in second cboprotect.

Thanks in advance

 

[lespaul]

you need to run the query that populates the second combo box in an event (AfterUpdate maybe???) of the first combo box. I don't really do much with Access Forms, so that's not my strong point. There are several threads that discuss in detail how to do what you need. Search for 'second combobox' and I'm sure you'll find something to help.

Leslie