Filter Form Using Multiple Combo Boxes

[Jim Moore]

Hi all,

I've read through a rather large amount of posts, and I can't seem to find a
direct answer to my question even though I understand there are many related
questions, so please pardon my ignorance and newby status.

I am trying to create a form that lists "risks". These risks are identified
by there "Location", "Process" and "Subprocess". There are other attributes
but I don't think they are important (perhaps I'm wrong). "Risks" are text
based. The other three are actually Id's that relate to their respective
tables.

I have a form currently designed with 3 unbound combo boxes at the top and a
command button. The three unbound combo boxes are Location, Process, and
Subprocess. Subprocess is synchronized with process to limit the subprocess
to select only ones that relate to the previously chosen process. Location
is not currently synchronized because I don't understand how to do that with
a many to many relationship but that is a question for another day.

What I would like is to be able to make a selection in each of the three
fields, press the command button (or possibly after update of the last one,
subprocess) and have only those records (risks and attached attributes) show
up.

Could someone help me with how to do this? Thank you so very much!

Best,

Jim

 

[Roger Carlson]

You would created a SQL statement (query) based on the values in the combo
boxes and set the Record Source to that SQL statement.

On my website (www.rogersaccesslibrary.com) are a couple of small sample
Access Databases that combined should do what you want. Look for
"CreateQueries2.mdb" and "ImproveFormPerformance.mdb". CreateQueries shows
how to create a SQL statement and ImproveFormPerf shows how to set a form's
recordsource to a SQL statement.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ken Sheridan]

Jim:

If values must be selected from all three combo boxes then you only need to
consider the Location and Subprocess values as the Process value is implied
by the Subprocess value if I understand you correctly. So the button needs
to build a string expression on which to filter the form:

Lets assume the unbound combo boxes are called cboLocation, cboProcess and
cboSubprocess; don't give them the same names as the fields in the table as
this will cause confusion. Lets also assume the fields in the table are
Location, ProcessID and SubprocessID. I'm assuming these are all number data
types. The code in the button's Click event procedure would go like this:

Conts conMESSAGE = "Both a location and subprocess must be selected."
Dim strFilter As String

' confirm both Location and Subprocess
' values have been selected
If IsNull(Me.cboLocation) Or IsNull(Me.cboSubprocess) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Else
strFilter = "LocationID = " & me.cboLocation & " And " & _
SubprocessID = " & Me.cboSubprocess
' filter form
Me.Filter = strFilter
Me.FilterOn = True
End If

If on the other and you want each combo box to be optional, i.e. a user can
select form all one, two, three or none then you need to examine each for
Null:

Dim strFilter As String

If Not IsNull(Me.cboLocation) Then
strFilter = strFilter & _
" And LocationID = " & Me.cboLocation
End If

If Not IsNull(Me.cboProcess) Then
strFilter = strFilter & _
" And ProcessID = " & Me.cboProcess
End If

If Not IsNull(Me.cboSubprocess) Then
strFilter = strFilter & _
" And SubprocessID = " & Me.cboSubprocess
End If

' if no values selected then turn form's filter off
' to show all records
If Len(strFilter) = 0
Me.FilterOn = False
Else
' remove redundant leading " And " from filter expression
strFilter = Mid(strFilter,6)
' filter form
Me.Filter = strFilter
Me.FilterOn = True
End If

Note that in this case the ProcessID does need to be included in the
criterion as a user may select a process, but not a subprocess. If they
select both then the former is actually redundant, but it does no harm to
leave it in.

If you want a 'Show All' button on the form as well, then in its Click event
procedure just turn the filter off with:

Me.FilterOn = False

Ken Sheridan
Stafford, England