filter main form by value in subform

[kenjones]

Hi

I have a form (Company) with a subform (Areas Inc Lay Company)

The subform has a combo box (country)

(record#) and (areas#) are the related fields in the underlying tables

With a command button (company_find_bareboat_operators_spain), on the main form I would like to filter records of company by the value of the combo box, in this example the value is Spain

Here is the buttons script;

-----------------------------

Private Sub company_find_bareboat_operators_spain_Click()
On Error GoTo Err_company_find_bareboat_operators_spain_Click

Dim stDocName As String
Dim strSQL As String

stDocName = "company_find_bareboat_operators_spain"

strSQL = "SELECT * FROM company INNER JOIN areas ON company.record# = areas.areas# WHERE ((areas.country)= 'Spain' )"

Me.RecordSource = strSQL

Exit_company_find_bareboat_operators_spain_Click:
Exit Sub

Err_company_find_bareboat_operators_spain_Click:
MsgBox Err.Description
Resume Exit_company_find_bareboat_operators_spain_Click

End Sub
--------------------------

When I click on the button I get 'Syntax error (missing operator) in query expression 'company.record# = areas.areas#'

I've been struggling with this for some time after several different approaches. So in addition to specific help with the above I'd also welcome comments on how I return a recordset of the main form by searching on subforms

 

[Taruz]

Hi..

Try this..:

SELECT * FROM company INNER JOIN areas ON company.[record#] = areas.[areas#] WHERE areas.country= 'Spain'

 

[kenjones]

Great! Thanks very much Taruz!

More generally. If I want to search by different countries I can do it with numerous buttons or I can change the code. Neither seem that neat a solution.

Before I waste a lot of time can you suggest a solution. I think I would like to click a button and have a box pop up asking me for a "country". Is there an easy (or difficult) way to do this?

Ken

 

[Taruz]

Hi again Ken..

Dim stDocName As String
Dim strSQL As String
Dim trz as string
trz= InputBox("country??")

stDocName = "company_find_bareboat_operators_spain"

strSQL = "SELECT * FROM company INNER JOIN areas ON company.[record#] = areas.[areas#] " _
& "WHERE areas.country= '" & trz & "'"

Me.RecordSource = strSQL

this way try..

in fact, more appropriate to use the combo box..

 

[kenjones]

Great! Again thanks Taruz!

This is so easy when you have somebody by your side who knows what they are doing!

So the combo box. And I'm going to reach for the skies here.

What if I want to search the table countries by both "country" and a second field "areas" And then another table with a "category" and a "sub category" as fields

I know I need a second "Inner Join" in the SQL which I can do (perhaps). And then I probably need a series of IF and ELSE IF arguments to handle various combinations of data I enter

Does that sound right in principle?

And so if I'm entering maybe 1, 2, 3 or 4 different parameters how do I activate the combo box search? When I was experimenting I did flirt with a combo box using "On Change". But if I want to enter several parameters I'll be changing the box every time I enter another parameter

At this stage perhaps you can just tell me if I'm approaching this in the right way. And if I am I'll then go away and have a little play

Thanks again