CODEWITHSUNDEEP
ms-accessvba
Crystal Thomas
Crystal Thomas

Reputation: 13

Filtering a subform in Microsoft Access using an unbound textbox in main form

I have two forms: "Job Sheet" (the main form) and "Jobs sub" (the subform).

I'm trying to use the 'afterupdate' function with an unbound textbox called yrcheck to filter the datasheet view of the subform, by the JbYr field.

Please tell me, what's wrong with this code? 

Private Sub yrcheck_AfterUpdate()

Dim yr As Integer
yr = Me.yrcheck
[Jobs sub].Form.Filter "JbYr='" & yr & "'"
[Jobs sub].Form.FilterOn = True

End Sub

I get "Invalid Use of Property" as an error.

What's wrong?

Upvotes: 0

Views: 4753

Answers (3)

Gustav
Gustav

Reputation: 55816

A non-code solution is to leave this filter and just include the textbox in the masterfields and expand the childfields:

[Id],[yrcheck]
[ParentID],[JbYr]

This will also remove the option for the user to remove this filter.

Upvotes: 0

HansUp
HansUp

Reputation: 97100

It would be helpful to tell us which line triggers the error. Since you did not, I'll guess this line is the culprit ...

[Jobs sub].Form.Filter "JbYr='" & yr & "'"

If my guess is correct, include an = sign between Filter and the string expression ...

[Jobs sub].Form.Filter = "JbYr='" & yr & "'"
                       ^
                      here

If the datatype of that JbYr field is numeric, eliminate the single quotes before and after the value of yr ...

[Jobs sub].Form.Filter = "JbYr=" & yr

I would also reference the subform control via Me, similar to @Smandoli's suggestion, but I don't think that was the source of your original error ...

Me![Jobs sub].Form.Filter = "JbYr=" & yr

Upvotes: 2

Smandoli
Smandoli

Reputation: 7019

Perhaps it is in the way you reference the subform. Try:

Private Sub yrcheck_AfterUpdate()

    Dim yr As Integer
    yr = Me.yrcheck
    Me![Jobs sub].Filter "JbYr='" & yr & "'"
    Me![Jobs sub].FilterOn = True

End Sub

Upvotes: 1

Related Questions