Two Filters for a Continuous Form

[Alspread]

Probably an easy question for many of you, but I have been going just crazy
with this (simple?)problem.
I'm trying to filter a form using two combo boxes (cboPartNumber and
cboOpNumber).

The form is set up as a continuous form with these combo boxes (and other
stuff) in the header and lots of fields in the detail section.

The two combo boxes were created using the combo box wizard, so they have the
code created by Access.

How do I set up the code statements in the after update event for each combo
box to limit the detail to only those items that me the combo box values?

 

[Douglas J. Steele]

What I typically do is create a sub (in the form's module) along the lines
of:

Private Sub SetFilter()
Dim strFilter As String

If IsNull(Me!Combo1) = False Then
strFilter = "Field1 = " & Me!Combo1 & " And "
End If

If IsNull(Me!Combo2) = False Then
strFilter = "Field2 = " & Me!Combo2 & " And "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

End Sub

I then call that sub in the two AfterUpdate events.

 

[Alspread]

Thanks for your help.

What have I done wrong?

I added your recommended code and I got an error.

The error states the following:

"YOU CANCELED THE PREVIOUS OPERATION"

The following line of code is highlighted in the code windoe:

Me.Filter = strFilter

Here is the entire code behind this form.

Private Sub cboOpNumber_AfterUpdate()
Dim R As Object
Set R = Me.Recordset.Clone
R.FindFirst "[Operation] = '" & Me![cboOpNumber] & "'"
Me.Bookmark = R.Bookmark
Call SetFilter
End Sub

Private Sub cboPartNumber_AfterUpdate()
Dim R As Object
Dim strWhere As String
Dim lngLen As Long
Set R = Me.Recordset.Clone
R.FindFirst "[Part Number] = '" & Me![cboPartNumber] & "'"
Me.Bookmark = R.Bookmark
Call SetFilter

End Sub


Private Sub SetFilter()
Dim strFilter As String

If IsNull(Me!cboPartNumber) = False Then
strFilter = "[Part Number] = " & Me!cboPartNumber & " And "
End If

If IsNull(Me!cboOpNumber) = False Then
strFilter = "Operation = " & Me!cboOpNumber & " And "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

End Sub

 

[Douglas J. Steele]

What's the actual value in strFilter?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for your help.

What have I done wrong?

I added your recommended code and I got an error.

The error states the following:

"YOU CANCELED THE PREVIOUS OPERATION"

The following line of code is highlighted in the code windoe:

Me.Filter = strFilter

Here is the entire code behind this form.

Private Sub cboOpNumber_AfterUpdate()
Dim R As Object
Set R = Me.Recordset.Clone
R.FindFirst "[Operation] = '" & Me![cboOpNumber] & "'"
Me.Bookmark = R.Bookmark
Call SetFilter
End Sub

Private Sub cboPartNumber_AfterUpdate()
Dim R As Object
Dim strWhere As String
Dim lngLen As Long
Set R = Me.Recordset.Clone
R.FindFirst "[Part Number] = '" & Me![cboPartNumber] & "'"
Me.Bookmark = R.Bookmark
Call SetFilter

End Sub


Private Sub SetFilter()
Dim strFilter As String

If IsNull(Me!cboPartNumber) = False Then
strFilter = "[Part Number] = " & Me!cboPartNumber & " And "
End If

If IsNull(Me!cboOpNumber) = False Then
strFilter = "Operation = " & Me!cboOpNumber & " And "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

End Sub

Probably an easy question for many of you, but I have been going just
crazy
with this (simple?)problem.
I'm trying to filter a form using two combo boxes (cboPartNumber and
cboOpNumber).

The form is set up as a continuous form with these combo boxes (and other
stuff) in the header and lots of fields in the detail section.

The two combo boxes were created using the combo box wizard, so they have
the
code created by Access.

How do I set up the code statements in the after update event for each
combo
box to limit the detail to only those items that me the combo box values?

 

[Alspread via AccessMonster.com]

strFilter is equal to the OpNumber after I select OpNumber (i.e. "[Operation]
= 050] or Part Number. The sql string does not seem to be including both
filters. Should the statement be a combination of the 2 strings?

What's the actual value in strFilter?

Thanks for your help.

[quoted text clipped - 69 lines]

 

[Douglas J. Steele]

Hold on. In your two AfterUpdate events, you're putting quotes around the
values, but you're not in the SetFilter routine. If Operation and Part
Number are text fields, you need quotes in both places (if they're numeric
fields, you don't need quotes in either place)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

strFilter is equal to the OpNumber after I select OpNumber (i.e.
"[Operation]
= 050] or Part Number. The sql string does not seem to be including both
filters. Should the statement be a combination of the 2 strings?

What's the actual value in strFilter?

Thanks for your help.

[quoted text clipped - 69 lines]

combo
box to limit the detail to only those items that me the combo box
values?

 

[Alspread via AccessMonster.com]

Operation and Part Number are both text fields.

Where should I put the quotes?

Hold on. In your two AfterUpdate events, you're putting quotes around the
values, but you're not in the SetFilter routine. If Operation and Part
Number are text fields, you need quotes in both places (if they're numeric
fields, you don't need quotes in either place)

strFilter is equal to the OpNumber after I select OpNumber (i.e.
"[Operation]

[quoted text clipped - 9 lines]

 

[Douglas J. Steele]

The quotes are correct in your AfterUpdate events. Do the same in your
SetFilter routine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Operation and Part Number are both text fields.

Where should I put the quotes?

Hold on. In your two AfterUpdate events, you're putting quotes around the
values, but you're not in the SetFilter routine. If Operation and Part
Number are text fields, you need quotes in both places (if they're numeric
fields, you don't need quotes in either place)

strFilter is equal to the OpNumber after I select OpNumber (i.e.
"[Operation]

[quoted text clipped - 9 lines]

box to limit the detail to only those items that me the combo box
values?