CODEWITHSUNDEEP
ms-accessvba
Robert Kendall
Robert Kendall

Reputation: 390

Apply filter on subform and open a report based on the filter

I have a subform which allows user to select a value for "ACFTsn" I would like to apply a filter to a table entitled "Logbook", where "sn"(field on the table) = ACFTsn the following is attached to a button on the subform

Me.Form.Filter = "table![Logbook].SN=me.ACFTsn"
Me.FilterOn = True
DoCmd.openreport "logbook", acViewReport, Filter

When I step through the code, I receive error 3071/ expression typed incorrectly or too complex to be evaluated.

I am sure there are syntax errors, as this is my first attempt at a filter with VBA, and I'm not sure this is the correct way to apply it to the report.

Upvotes: 0

Views: 738

Answers (2)

Parfait
Parfait

Reputation: 107737

Alternatively, you can use the ApplyFilter method or even SetFilter (for MS Access 2010+):

Dim strfilter As String
strfilter = "[SN] = '" & Me!ACFTsn.Value & "'"

DoCmd.ApplyFilter , strfilter                          ' FILTERS FORM 
DoCmd.OpenReport "logbook", acViewReport, strfilter    ' FILTERS REPORT

Upvotes: 2

Gustav
Gustav

Reputation: 55981

It should be this simple:

Me.Filter = "[SN] = '" & Me!ACFTsn.Value & "'"
Me.FilterOn = True

If SN is not a string:

Me.Filter = "[SN] = " & Me!ACFTsn.Value & ""
Me.FilterOn = True

To apply the same filter on the report:

DoCmd.openreport "logbook", acViewReport, , Me.Filter

Upvotes: 1

Related Questions