Using two combo boxes to filter a form

[Opal]

Using Access 2003, I am using two combo boxes to filter a form with
the following code at the top of the form's event procedures:

Option Compare Database
Option Explicit
Private Sub SetFil()

' Clear filter if neither cbo is set
If IsNull(Me.cboFilter1) And IsNull(Me.cboFilter2) Then
Me.FilterOn = False
Exit Sub
End If

' here if either cboFilter1 or cboFilter2 or both have data
If IsNull(Me.cboFilter2) Then ' just use cboFilter1 for the filter
Me.Filter = "AreaID = " & Me.cboFilter1
Me.FilterOn = True
Exit Sub
End If

' here when both cbo's have data
Me.Filter = "AreaID = " & Me.cboFilter1 & _
" and EquipmentName = " & Me.cboFilter2
Me.FilterOn = True
Exit Sub

End Sub

In the on open event for the form as well as the after update event
for both
combo boxes, I call this routine.

I am having 2 small problems with it, however:

When I select an item from the cboFilter1 drop down, the EquipmentName
field
goes blank. Even after I select an item from cboFilter2, the form
filters correctly,
but the EquipmentName field still remains blank. I have verified the
data in
the table and there is data in this field. The SQL statement behind
each is very
similar, could this be the problem?

cboFilter2:

SELECT EquipmentNumber.EquipmentNumberID,
EquipmentNumber.EquipmentName, EquipmentNumber.AreaID
FROM EquipmentNumber
WHERE (((EquipmentNumber.AreaID)=[Forms]![frmConcern]![cboFilter1]));

EquipmentName:

SELECT EquipmentNumber.EquipmentNumberID,
EquipmentNumber.EquipmentName, EquipmentNumber.AreaID
FROM EquipmentNumber
WHERE (((EquipmentNumber.AreaID)=[Forms]![frmConcern]![AreaID]));

Also, I need to requery the filter boxes in case the user wants to
filter the records
more than once. I have never been clear to me as to which event event
I should
put this in. I tried the after update even for the combo boxes, but
nope....

Also, I wondered if it wouldn't be more efficient to use an "If
IsNull .... Else..."
similar to a FindFirst sub....?

 

[Opal]

Using Access 2003, I am using two combo boxes to filter a form with
the following code at the top of the form's event procedures:

Option Compare Database
Option Explicit
Private Sub SetFil()

' Clear filter if neither cbo is set
If IsNull(Me.cboFilter1) And IsNull(Me.cboFilter2) Then
Me.FilterOn = False
Exit Sub
End If

' here if either cboFilter1 or cboFilter2 or both have data
If IsNull(Me.cboFilter2) Then ' just use cboFilter1 for the filter
Me.Filter = "AreaID = " & Me.cboFilter1
Me.FilterOn = True
Exit Sub
End If

' here when both cbo's have data
Me.Filter = "AreaID = " & Me.cboFilter1 & _
" and EquipmentName = " & Me.cboFilter2
Me.FilterOn = True
Exit Sub

End Sub

In the on open event for the form as well as the after update event
for both
combo boxes, I call this routine.

I am having 2 small problems with it, however:

When I select an item from the cboFilter1 drop down, the EquipmentName
field
goes blank.  Even after I select an item from cboFilter2, the form
filters correctly,
but the EquipmentName field still remains blank.  I have verified the
data in
the table and there is data in this field.  The SQL statement behind
each is very
similar, could this be the problem?

cboFilter2:

SELECT EquipmentNumber.EquipmentNumberID,
EquipmentNumber.EquipmentName, EquipmentNumber.AreaID
FROM EquipmentNumber
WHERE (((EquipmentNumber.AreaID)=[Forms]![frmConcern]![cboFilter1]));

EquipmentName:

SELECT EquipmentNumber.EquipmentNumberID,
EquipmentNumber.EquipmentName, EquipmentNumber.AreaID
FROM EquipmentNumber
WHERE (((EquipmentNumber.AreaID)=[Forms]![frmConcern]![AreaID]));

Also, I need to requery the filter boxes in case the user wants to
filter the records
more than once.  I have never been clear to me as to which event event
I should
put this in.  I tried the after update even for the combo boxes, but
nope....

Also, I wondered if it wouldn't be more efficient to use an "If
IsNull .... Else..."
similar to a FindFirst sub....?

D'oh....Okay, I re-query the cboFilter2 after the cboFilter1 after
update event.
but why is my box blanking out....?