Setting Filter in Subform

Question

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

Uzial

Answer 1

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

Carousel

Hi, I've tried that, I'll update my original post with the exact code I've written. – Uzial

I've edited my answer @Uzial - see if this works for you. – Carousel

It's stopped the "Procedure declaration does not match description of event or procedure having same name" - but it's still not setting the filter - don't you have to pass the variable dblRoomID into DoStudentsRoomQuickViewFilter()? Otherwise dblRoomID <> 0 will always be true? When I do pass it across, I get a "Object Dosn't support this property or method" error – Uzial

@Uzial - I've declared dblRoomID outside of the Subs, right at the top of my code. This means that I can access the variable in any of the code for this form. In terms of your issue, can I suggest replacing "Forms.Properties." with "Me."? See my edit above. – Carousel

Great - that's done the trick - just wondering why it would need that? – Uzial

Turning the 'FilterOn' was the duh-help I needed thank you. I think when you use the form-wizard it does that for you. But when your doing it programmatically to a sub-form it does not. – Slingshot

Answer 2

Check your spelling of "Properties"...

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

Chelsea