filter 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 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 strLinkCriteria As String

stDocName = "company_find_bareboat_operators_spain"
strLinkCriteria = "[record#] = Forms![Areas Inc Lay Company]![areas#]"

DoCmd.ApplyFilter , "Forms![Areas Inc Lay Company]![country] = 'Spain'"

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 a "Enter Parameter Value" popup box asking for Forms!Areas Inc Lay Company!country. If I type in Spain the filter returns all records

Any help much appreciated

 

[pbaldy]

Your syntax is off

http://access.mvps.org/access/forms/frm0031.htm

 

[kenjones]

Hi Paul

Thanks for the reply

I had a look at your link and made some changes. Obviously I'm not grasping this cos I get the same problem. I assume the problems are on the following 2 lines;

strLinkCriteria = "[record#] = Forms![Areas Inc Lay Company]![areas#]"

DoCmd.ApplyFilter , "Forms![Areas Inc Lay Company]![country] = 'Spain'"

Which I changed to;

strLinkCriteria = "Me.[record#] = Me![Areas Inc Lay Company].[Company]![areas#]"

DoCmd.ApplyFilter , "Me![Areas Inc Lay Company].[Company]![country] ='Spain'"

If you could nudge me in the right direction it would be much appreciated

Ken

 

[pbaldy]

Sorry Ken, I lost track of this thread. Did you get this sorted out?

 

[kenjones]

Hi Paul

No I didn't. I'm no further on than my previous post where, after taking a look at the link you posted, I made some changes. If you could a look at the changes and point me in the right direction that would be appreciated

Ken