By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,190 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,190 IT Pros & Developers. It's quick & easy.

Allen Browne AutoFilter

P: n/a
Follow Up question to the below posted by Allen Browne on 02/06/2005 -
Could something like this work for a form that has a subform and the
subform is a datasheet?

__________________________________________________ _____________________

Re: Multiple Sort in a Continuous Form - Similiar to AutoFilter

06 Feb 2005 06:21
Allen Browne

Okay, so you want to create a string that adds in the value of all the
non-blank combos.
This example is set up so that it creates a filter from any of the combos
that have a value. It tacks an " AND " on the end, so it's easy to add as
many as you wish. Then at the end it chops off the trailing " AND ", and
applies the filter.

You can code it in one of the combos, and then call the code from the other
combos to the filter gets applied as soon as any combo is updated.

Private Sub Combo49_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first

If Not IsNull(Me.Combo49) Then
strWhere = strWhere & "([IPT] = '" & Me.Combo49 & "') AND "
End If

If Not IsNull(Me.Combo83) Then
strWhere = strWhere & "([Tier5] = '" & Me.Combo83 & "') AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lenLen > 0 Then
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn True
Else
MsgBox "No criteria."
End If
End Sub

Private Sub Combo83_AfterUpdate()
Call Combo49_AfterUpdate
End Sub

Note that this example assumes:
- All these combos are unbound (for filtering only), and
- IPT, Tier5, etc are all Text type fields. Remove the extra quotes for
number fields.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
If the main form is bound and you are wanting to filter the main form, then
yes, it will work exactly as indicated.

If you want to apply all the filter choices the filter to the subform
instead of the main form, it will be the same until you get to the lines:
Me.Filter = strWhere
Me.FilterOn True
Replace those two with lines that filter the subform instead, i.e.:
Me.[NameOfYourSubformControlHere].Form.Filter = strWhere
Me.[NameOfYourSubformControlHere].Form.FilterOn = True

If you are wanting to filter the main form based on values that are in the
subform, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Britton via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message
news:06******************************@AccessMonste r.com...
Follow Up question to the below posted by Allen Browne on 02/06/2005 -
Could something like this work for a form that has a subform and the
subform is a datasheet?

__________________________________________________ _____________________

Re: Multiple Sort in a Continuous Form - Similiar to AutoFilter

06 Feb 2005 06:21
Allen Browne

Okay, so you want to create a string that adds in the value of all the
non-blank combos.
This example is set up so that it creates a filter from any of the combos
that have a value. It tacks an " AND " on the end, so it's easy to add as
many as you wish. Then at the end it chops off the trailing " AND ", and
applies the filter.

You can code it in one of the combos, and then call the code from the
other
combos to the filter gets applied as soon as any combo is updated.

Private Sub Combo49_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first

If Not IsNull(Me.Combo49) Then
strWhere = strWhere & "([IPT] = '" & Me.Combo49 & "') AND "
End If

If Not IsNull(Me.Combo83) Then
strWhere = strWhere & "([Tier5] = '" & Me.Combo83 & "') AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lenLen > 0 Then
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn True
Else
MsgBox "No criteria."
End If
End Sub

Private Sub Combo83_AfterUpdate()
Call Combo49_AfterUpdate
End Sub

Note that this example assumes:
- All these combos are unbound (for filtering only), and
- IPT, Tier5, etc are all Text type fields. Remove the extra quotes for
number fields.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.