Split Form filter problem

[Sue Compelling]

Hi

My DB in AC2007 uses the brilliant split form capability with a filter that
generates a nice subset in my datasheet half ...

Unfortunately my users only have AC2003 and I have to replicate the split
form capability.

My problem is - how do I get my expression to filter on the subform that I'm
using to represent a data sheet view of the data.

My subform is called ... TblPrimesubform

and my filter code is below (thanks to Mr B and Ken)

Private Sub txtStreetName_AfterUpdate()

Dim strFilterVal As String
If Not IsNull(Me.txtStreetName) Then
strFilterVal = Me.txtStreetName
Me.Filter = "StreetName Like '*" & strFilterVal & "*'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub


TIA

 

[Allen Browne]

You can run into problems if you apply a filter to a main form and to a
subform in Access 2003 and earlier. It's issue #1 here:
http://allenbrowne.com/bug-02.html

There are also issues with creating a new record if the
LinkMasterFields/LinkChildFields uses an autonumber. The steps under 'Other
Causes' here explain how to demonstrate that problem:
http://allenbrowne.com/ser-40.html#OtherCauses

In some versions of Access, there are also concurrency issues with memo
fields if you use 2 editable copies of the data at once (even though both
are not being edited at the same time.)

A workaround might be to use a list box instead of a subform for the
datasheet part of your simulated split form.

BTW, MS also had trouble getting the split form to work correctly. Seach
for:
Split forms: records disappear on undo
on this page:
http://allenbrowne.com/Access2007.html#Bugs

 

[Sue Compelling]

Hi Allen

Thanks for that (and so quickly too)

I will not be meeting ANY of the conditions you've decribed (ie - will not
be using reports, will instruct the user that they cannot filter on the
subform, that the form overrides the subform filters and the on order
function will not be used)

So ... how do I change my code to filter both the form and subform? (I'm
very new to the filter command by code)

TIA


ps - unfortunately there are a range of fields I want the user to have
visibility to as a result of filtering on a street name - so the listbox work
around is out.

 

[Allen Browne]

Dim strFilterVal As String
Dim strFilter As String

If Not IsNull(Me.txtStreetName) Then
strFilter = "StreetName Like ""*" & Me.txtStreetName & "*"""
Me.Filter = strFilter
Me.FilterOn = True
Me.[Sub1].Form.Filter = strFilter
Me.[Sub1].Form.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

 

[Sue Compelling]

Thanks Allen

Does exactly what I wanted it to do -

Cheers
--
Sue Compelling

Dim strFilterVal As String
Dim strFilter As String

If Not IsNull(Me.txtStreetName) Then
strFilter = "StreetName Like ""*" & Me.txtStreetName & "*"""
Me.Filter = strFilter
Me.FilterOn = True
Me.[Sub1].Form.Filter = strFilter
Me.[Sub1].Form.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

Thanks for that (and so quickly too)

I will not be meeting ANY of the conditions you've decribed (ie - will not
be using reports, will instruct the user that they cannot filter on the
subform, that the form overrides the subform filters and the on order
function will not be used)

So ... how do I change my code to filter both the form and subform? (I'm
very new to the filter command by code)

TIA


ps - unfortunately there are a range of fields I want the user to have
visibility to as a result of filtering on a street name - so the listbox
work
around is out.