Re: Filter on top of form?
From: Jim Allensworth (jimNOT_at_datacentricsolutions.com)
Date: 07/22/04
- Next message: Oscar: "Count Postive Numbers Only in a Form"
- Previous message: Daniel: "I Found IT !!!"
- In reply to: Mike: "Filter on top of form?"
- Next in thread: Mike: "Re: Filter on top of form?"
- Reply: Mike: "Re: Filter on top of form?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Jul 2004 20:53:35 GMT
On Thu, 22 Jul 2004 13:05:53 -0700, "Mike"
<anonymous@discussions.microsoft.com> wrote:
>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.
>
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
- Next message: Oscar: "Count Postive Numbers Only in a Form"
- Previous message: Daniel: "I Found IT !!!"
- In reply to: Mike: "Filter on top of form?"
- Next in thread: Mike: "Re: Filter on top of form?"
- Reply: Mike: "Re: Filter on top of form?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
