Problem with Serverfilter for Subform

[Bodo]

Hi,
in my Access application I have a form with a subform both linked by the
master/child properties.
In the subforms header I have a combobox that allows the user to display a
subset of records that matches with the value in the combobox plus the values
of the current master/child properties.

I use the subforms ServerFilter property to apply the filter to the form.
My vba code in the subform:

Private Sub Form_Load()
Me.ServerFilter = vbNullString
End Sub

Private Sub Form_Close()
Me.ServerFilter = vbNullString
End Sub

Private Sub cmdlFilter_Click()
If Me!KomboDefaultOrga <> 0 Then
Me.ServerFilter = "ORGABAUM_ID = " & Me!KomboDefaultOrga
Me.Refresh
End If
End Sub

However, the code above has no effect on the resultset displayed in the
subform when sub cmdlFilter_Click runs. The subform displays only those
records matching the master/child properties values, not the value the user
selects in the subform.

Anyone has any ideas how to troubleshoot?

 

[Sylvain Lafontaine]

Try using Me.Requery instead of Me.Refresh.

Not sure if the filter ORGABAUM_ID = " & Me!KomboDefaultOrga is supposed to
subtract records or to add new one to the filtering already done with the
master/child properties.

Take a look with the SQL-Server Profiler to see what Access is doing with
these two properties and if you don't find an obvious solution, change the
record source of the subform so that it will directly include the new
condition instead of using a server filter. (BTW, you won't need to make a
requery after changing the record source of a form.)

 

[Vadim Rapp]

B> However, the code above has no effect on the resultset displayed in the
B> subform when sub cmdlFilter_Click runs. The subform displays only those
B> records matching the master/child properties values, not the value the
B> user selects in the subform.

I think you are confusing Access by using parent/child and serverfilter at
the same time. For the subform, parent/child defines the same serverfilter
that you are trying then to override by the code, so Access most likely is
trying to protect master/child.

If you want to control what the subform is showing, then don't use
master/child for the subform, and control everything by code.

Vadim Rapp