More than One Record on filtered Continuous Form

[Williams485]

Hello Access Forum,


I've put together a form [Report Viewer], which is bound to a table [Opportunities], on which there are two combo boxes and a subform [qryTtlRev subform], whose default view is 'continuous form'. I'm trying to use the two combo boxes as controls for users to apply filters to the data that will be showed in the subform. So far, I have only one combo box in play and the desired functionality isn't quite there.

Currently, my first combo box [cbStageSelect] applies the filter, but instead of all records that meet the filter criteria being displayed, only one record is shown. How can I change this so that ALL records that fit criteria are shown?

The event code that applies the filter is as follows:

Private Sub cbStageSelect_AfterUpdate()
    If cbStageSelect = "ALL" Then
        DoCmd.ShowAllRecords
    Else
        DoCmd.GoToControl "qryTtlRev subform"
        Forms![Report Viewer]![cbStageSelect].SetFocus
        DoCmd.ApplyFilter , "Stage='" & Me!cbStageSelect.Value & "'"
    End If
End Sub

[Rawb]

You need to use the Subform's Filter Property, not the Application's ApplyFilter Method. Try using this instead:

Private Sub cbStageSelect_AfterUpdate()
    If cbStageSelect = "ALL" Then
        Forms![Report Viewer]![cbStageSelect].FilterOn = False
    Else
        Forms![Report Viewer]![cbStageSelect].Filter = "Stage='" & Me!cbStageSelect.Value & "'"
        Forms![Report Viewer]![cbStageSelect].FilterOn = True
    End If
End Sub

[Williams485]

Thanks, Rawb. I used your code but needed to modify it a little:

Private Sub cbStageSelect_AfterUpdate()
    If cbStageSelect = "ALL" Or cbStageSelect = "" Then
        Forms![Report Viewer]![qryTtlRev subform].Form.FilterOn = False
    Else
        Forms![Report Viewer]![qryTtlRev subform].Form.Filter = "Stage='" & Me!cbStageSelect.Value & "'"
        Forms![Report Viewer]![qryTtlRev subform].Form.FilterOn = True
    End If
End Sub

However, I still don't get all records showing - even when 'ALL' is selected and the filter is off.

[Williams485]

I appear to have solved this issue. The subform had both 'Link Master Fields' and 'Link Child Fields' properties set to the primary key in the table. I removed this (without any knowledge of why this might work, if I'm honest!) and my form now performs as I want it to.

Any comments on why my solution is in fact the solution would be welcomed!