Filter on top of form?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello,

I saw an access form and the top of the form was a filter
for the data below. For example if you put MI in the
state filter it would give you only the MI records. If
you then put Lansing in the City filter, it would filter
for both Lansing and MI. If they were all blank, all
records were shown.

It would be VERY helpful if I could do that. I tried a
subform to make as a filter but when I put it in the
header it wouldn't allow continuous forms. Even if it
did, I am not sure that I could have made it work.

Any ideas?

Mike
 
Hello,

I saw an access form and the top of the form was a filter
for the data below. For example if you put MI in the
state filter it would give you only the MI records. If
you then put Lansing in the City filter, it would filter
for both Lansing and MI. If they were all blank, all
records were shown.

It would be VERY helpful if I could do that. I tried a
subform to make as a filter but when I put it in the
header it wouldn't allow continuous forms. Even if it
did, I am not sure that I could have made it work.
Click to expand...

Basically what you do is step through the criteria controls (in the
form header) and construct a filter string. And then apply it.

Depending on how complex your criteria might be, you could approach it
something like this one that I did ...

=====< watch for wrapping >=======
Dim ctl As Control
Dim sect As Section

mstrWhere = ""
Me.txtWhere = ""
If Len(Me.txtInOp) > 0 Then
If Left(Me.txtInOp, 4) <> " AND" Then
Me.txtInOp = " AND " & Me.txtInOp
End If
End If

Set sect = Me.FormHeader
For Each ctl In sect.Controls
With ctl
If .ControlType = acTextBox Or .ControlType = acComboBox _
Or .ControlType = acCheckBox Then
'Not the zip list or zipcritera textbox
Select Case .Name
Case "lstZip", "txtZipCriteria", "txtZipCount", _
"txtWhere", "txtInOp"
'do nothing
Case "cboAddEvent"
'Add an in operator for event if requested
If Not IsNull(Me.cboAddEvent) Then
If Len(mstrWhere) = 0 Then
mstrWhere = "tblCustomer.CustomerID "
_
& "In (SELECT CustomerID " _
& "FROM tblCustEvents WHERE EventID="
_
& Me.cboAddEvent & ";)"
Else
mstrWhere = mstrWhere & " AND " _
& "tblCustomer.CustomerID In " _
& "(SELECT CustomerID " _
& "FROM tblCustEvents WHERE EventID="
_
& Me.cboAddEvent & ";)"
End If
End If

Case "chkEmail" 'bring email into the equation
Select Case Me.chkEmail
Case Null

Case True
If Len(mstrWhere) = 0 Then
mstrWhere = "Email Is Not Null"
Else
mstrWhere = mstrWhere _
& " AND Email Is Not Null"
End If
Case False
If Len(mstrWhere) = 0 Then
mstrWhere = "Email Is Null"
Else
mstrWhere = mstrWhere _
& " AND Email Is Null"
End If

End Select
Case Else
If Len(ctl & "") > 0 Then
.SetFocus
CreateWhere
End If
End Select
End If
End With
Next
If Len(Me.txtZipCriteria & "") > 0 Then
If Len(mstrWhere) > 0 Then
mstrWhere = mstrWhere & " AND (" _
& Me.txtZipCriteria & ")"
Else
mstrWhere = Me.txtZipCriteria
End If
End If
Me.txtWhere = mstrWhere

Me.Filter = Me.txtWhere & Me.txtInOp
Me.FilterOn = True
===================


- Jim
 
hmm... I'll try it. I am not strong at all with VB, but I
can cut and paste so hopefully I can figure it out. Thanks

Mike
-----Original Message-----


Basically what you do is step through the criteria controls (in the
form header) and construct a filter string. And then apply it.

Depending on how complex your criteria might be, you could approach it
something like this one that I did ...

=====< watch for wrapping >=======
Dim ctl As Control
Dim sect As Section

mstrWhere = ""
Me.txtWhere = ""
If Len(Me.txtInOp) > 0 Then
If Left(Me.txtInOp, 4) <> " AND" Then
Me.txtInOp = " AND " & Me.txtInOp
End If
End If

Set sect = Me.FormHeader
For Each ctl In sect.Controls
With ctl
If .ControlType = acTextBox Or .ControlType = acComboBox _
Or .ControlType = acCheckBox Then
'Not the zip list or zipcritera textbox
Select Case .Name
Click to expand...
Case "lstZip", "txtZipCriteria", "txtZipCount", _
 
Hi,

Here's an alternative to the last posting.

It assumes you use combo boxes for the filer entry.
Watch out for line wrapping.


'at the top of the code page
Private strFilter1 As String
Private strFilter2 As String
Private strFilter3 As String

Private Sub CboFilter1_Change()
strFilter1 = IIf(CboFilter1.Text <> "", "([Field1]=" & CboFilter1.Value
& ")", "")
UpdateFilter
End Sub

Private Sub CboFilter2_Change()
strFilter2 = IIf(CboFilter2.Text <> "", "([Field2]=" & CboFilter2.Value
& ")", "")
UpdateFilter
End Sub

Private Sub CboFilter3_Change()
strFilter3 = IIf(CboFilter3.Text <> "", "([Field3]=" & CboFilter3.Value
& ")", "")
UpdateFilter
End Sub


Private Sub UpdateFilter()
Dim strFilter As String

On Error GoTo UpdateFilter_Error

strFilter = IIf(strFilter1 <> "", strFilter1, "")
strFilter = strFilter & IIf(strFilter2 <> "", IIf(strFilter <> "", " AND
", "") & strFilter2, "")
strFilter = strFilter & IIf(strFilter3 <> "", IIf(strFilter <> "", " AND
", "") & strFilter3, "")

Me.FilterOn = False
Me.Filter = strFilter
Me.FilterOn = True

Exit Sub

UpdateFilter_Error:
'check for "not in list" error
If Err.Number = 2237 Then
Me.ActiveControl.Text = ""
Else
MsgBox Err.Description
End If
End Sub

Selecting the contents of a combo and deleting will remove that field from
the filter.
You should be able to see that adding more filter fields is very easy.

HTH

MFK.
 
Back
Top