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

0
votes

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?

3
ms-accessvba
What is JbYr? a string, number or date? – user2204315
JbYr is a 'long integer', number type field. – Crystal Thomas
I figured yr should be integer, but when I try to do so the error doesn't let up. – Crystal Thomas

3 Answers

2
votes

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
1
votes

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
0
votes

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.