Subform Filter from Mainform ComboBox

[klopstan]

Hi guys, 1st time poster, long time lurker ; )

I know this issue has been covered a number of times, but I've been experiencing problems applying the various solutions listed to my problem.

Basically, I have a form (frmCompany) which lists Company Details and a linked subform (frmContractsSubform) as a datasheet that shows the Company's Contracts that I would like to filter to show grouped records, particularly by the field "ContractYear" which lists the year the Contract became active, 2001, 2002 and so on.

I would like the user to be able to Select a year value from a ComboBox on the main form that will then filter the corresponding records for the linked company subform datasheet.

I have attempted to use the following code:

Private Sub cboFilterYear_AfterUpdate()
Me.frmContractsSubform.Form.Filter = "[ContractYear] = " & Me.cboFilterYear

End Sub

I have also tried including the following code as a second line:

Me.frmContractsSubform.Form.FilterOn = True



​

But receive the error: Runtime Error '3071' the expression is typed incorrectly.

I figured that once I have the concept down, I can apply it to other fields.

Any advise or assitance would be greatly appreicated, and apologies in advance if this question is somewhat idiotic

 

[revver]

Your question isn't entirely clear so I am guessing here.

I suspect that [ContractYear] might not be numeric. Try this

Private Sub cboFilterYear_AfterUpdate()
  Me.frmContractsSubform.Form.Filter = "[ContractYear] = '" & Me.cboFilterYear & "'"
  Me.frmContractsSubform.Form.FilterOn = True
End Sub

The FilterOn=True line should be included.

Also you need to be sure that the first column of the combo is the ContractYear and not some other field which is hidden. Look at the RowSource to check. Then look at Column Widths.

 

[klopstan]

Thanks revver, your suggestion worked and I've been able to apply it to other areas in my db. Much appreciation and kind regards to you