O
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....?
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....?