Filter via Code

  • Thread starter Thread starter Branden Fisk
  • Start date Start date
B

Branden Fisk

Hello,

I'd like to have the user be able to use the filter
option using drop boxes and be able to stack the filter
criteria. for example, show factory "A" in one filter,
and then run another showing Customer "123". the result
would return all items in Factory A, with custoemr
123.... how can i do that via code?

my code is as follows:

Private Sub cbo_custName_AfterUpdate()

Me.Filter = "CUST_Name = '" & cbo_custName & "'"
Me.FilterOn = True
Me.OrderBy = "RQST_DLVRY"

End Sub

Any help is welcome!!!

Branden
 
Branden said:
I'd like to have the user be able to use the filter
option using drop boxes and be able to stack the filter
criteria. for example, show factory "A" in one filter,
and then run another showing Customer "123". the result
would return all items in Factory A, with custoemr
123.... how can i do that via code?

my code is as follows:

Private Sub cbo_custName_AfterUpdate()

Me.Filter = "CUST_Name = '" & cbo_custName & "'"
Me.FilterOn = True
Me.OrderBy = "RQST_DLVRY"

End Sub
Click to expand...


You'll need to set the form's Filter in both the factory and
the customer combo box's After Update events. The code
would be the same in both procedures, so I recommend that
you create a Sub procedure to do it and then call this
procedure from both places.

Here's my shot at the procedure:

Private Sub SetFilter()
Dim strFilter As String

If Not IsNull(cbo_custName) Then
strFilter = " AND CUST_Name = '" & cbo_custName & "'"
End If
If Not IsNull(cbo_factory) Then
strFilter = " AND factory = '" & cbo_factory & "'"
End If

Me.Filter = Mid(strFilter, 6)
Me.FilterOn = True
Me.OrderBy = "RQST_DLVRY"
Me.OrderByOn = True
End Sub
 
The following example uses made-up control and field
names, of course, but it checks to see if each control has
a value and concantinates a string to represent the filter
to be applied. Even better would be to place this in a
separate procedure, and have your AfterUpdate() events
call it to eliminate redundancy. Good luck.

Private Sub cbo_custName_AfterUpdate()
Dim strSQL as String
If Nz(Me!cbo_custName,"") = "" Then
strSQL = strSQL & "And [CUST_Name]='" _
& Me!cbo_custName & "' "
End If
If Nz(Me!cbo_Factory,"") = "" Then
strSQL = strSQL & "And [Factory]='" _
& Me!cbo_Factory & "' "
End If
If Nz(Me!cbo_Customer,"") = "" Then
strSQL = strSQL & "And [CustomerID]=" _
& Me!cbo_Customer & " "
End If
strSQL = Mid(strSQL,5)
Me.Filter = strSQL
Me.FilterOn = True
Me.OrderBy = "[RQST_DLVRY]"
End Sub
 
Usually the way I handle the multiple AND clauses is by:

strFilter = " 0=0 "

If Not IsNull(cbo_custName) Then
strFilter = " AND CUST_Name = '" &
cbo_custName & "'"
End If
If Not IsNull(cbo_factory) Then
strFilter = " AND factory = '" &
cbo_factory & "'"
End If

I guess either way it works. Another example of how to do
things differently.


Chris Nebinger
 
Back
Top