Setting Filter in Subform

filter, ms-access, subform, vba

Solution

Did you try turning the filter on?

Forms.Properies.frmStudentsRoomQuickview.Form.Filter = "[Room_ID]=" & dblRoomID
Forms.Properies.frmStudentsRoomQuickview.Form.FilterOn = True

Edit:

Having seen your update, I understand the issue. The problem is that you cannot pass a parameter to the _Enter event. You'd have to do something like this:

Dim dblRoomID As Double

Private Sub Form_Current()

    dblRoomID = 0

    If IsNull(Forms.Properties.frmRoomsByPropertySubform.Form.room_id) Then
        Me.frmRoomsByPropertySubform.Visible = False
        Me.frmStudentsRoomQuickview.Visible = False
    Else
        Me.frmRoomsByPropertySubform.Visible = True
        Me.frmStudentsRoomQuickview.Visible = True
        dblRoomID = Me.frmRoomsByPropertySubform.Form.room_id
        DoStudentsRoomQuickViewFilter
    End If

End Sub

Private Sub DoStudentsRoomQuickViewFilter()
    If dblRoomID <> 0 Then
        Me.frmStudentsRoomQuickview.Form.Filter = "[Room_ID] = " & dblRoomID
        Me.frmStudentsRoomQuickview.Form.FilterOn = True
        Me.frmStudentsRoomQuickview.Requery
        Debug.Print Screen.ActiveForm.Name
    End If
End Sub

Private Sub frmStudentsRoomQuickview_Enter()
    DoStudentsRoomQuickViewFilter
End Sub

Problem

I've a main form "Properties", which has two subforms, one of which displays the rooms in that property, the other one occupants in each room. As you change the property, the rooms change in the first subform, which is continuous. As you scroll down the room subform, making a new room active, I want the occupants to change in the second subform. So far I've written this in the Current event of the "properties" main form: ``` Dim dblRoomID As Double dblRoomID = Forms.Properties.frmRoomsByPropertySubform.Form.room_id ``` Which successfully pulls out the Room_ID from the first Subform. Now I need to use that Room_ID to set a filter in the second subform, which currently displays all occupants of all properties, but has a Room_ID field. I cant get ``` Forms.Properies.frmStudentsRoomQuickview.Form.Filter = "[Room_ID]=" & dblRoomID ``` or docmd.applyfilter to work - I've been working on the assumption that this is because the active form needs to be the 'frmstudentRoomQuickview' for the later to work - but I can't understand why simply setting the .filter won't work. Edit: I should add, I can't use a subform within the "rooms" form, as the rooms form needs to be continuous. Edit 2: ``` Private Sub Form_Current() Dim dblRoomID As Double If IsNull(Forms.Properties.frmRoomsByPropertySubform.Form.room_id) Then Forms.Properties.frmRoomsByPropertySubform.Visible = False Forms.Properties.frmStudentsRoomQuickview.Visible = False Else Forms.Properties.frmRoomsByPropertySubform.Visible = True Forms.Properties.frmStudentsRoomQuickview.Visible = True dblRoomID = Forms.Properties.frmRoomsByPropertySubform.Form.room_id Call frmStudentsRoomQuickview_Enter(dblRoomID) End If End Sub Private Sub frmStudentsRoomQuickview_Enter(dblRoomID) Forms.Properties.frmStudentsRoomQuickview.Filter = "[room_id] = " & dblRoomID Forms.Properties.frmStudentsRoomQuickview.FilterOn = True Forms.Properties.frmStudentsRoomQuickview.Requery Debug.Print Screen.ActiveForm.name End Sub ``` I'm now getting "Procedure declaration does not match description of event or procedure having same name" errors

Original source