Chris said:
Right now, these reports/queries are set up to each prompt with more
than a dozen different parameter queries for each of the employee
levels we have... "Show Employees Level 1 (Y or N)?" "Show Employees
Level 2 (Y or N)?" "Show Employees Level 3 (Y or N)?"
4...5...6...7...etc, etc.
So you go through each prompt & answer with Y or N for every employee
level!!! The end result is the same, but this is definitely not
convenient or very user-friendly. Thus, I need a simple multi-select
list box.
What I want is that when I open a report, this form pops up with a
multi-select box asking which "employee levels" I want in the report,
the query runs, and the report generates (without having to Y/N
through a dozen plus prompts). Also, i want to use this same form
for several different reports & queries where I'd like to have the
option of picking 1 or more different "employee levels", so I don't
want to set the multi-select box to one particular query.
Also, I'm beginner-level in access....LOL
That's going to make it a bit harder to get where you want to go, but we
can get there if you're patient. This is what I recommend as an overall
approach:
===============
1. Create a dialog form that contains the list box and a public function
that returns the criteria string for the employee level (details on the
function to be supplied below). You can use your existing form,
"frmLevelSelect", for this purpose, but you'll need to modify it. Set
this form up so that it has an OK button that hides the form (by setting
its Visible property to False), and a Cancel button that just closes the
form.
2. Base the relevant reports or forms on a query that doesn't apply any
criteria to the employee level field.
3. In the Open event for each such report, open frmLevelSelect in dialog
mode. That will pause the code in the Open event until the form is
either hidden (the user clicked OK) or closed (the user clicked Cancel).
So, in the report's Open event, when execution resumes after opening the
form, check to see if the form is still open (hidden). If it is, call
the function on the form that returns the criteria string derived from
the list box, and append that to the report's recordsource query,
updating the recordsource.
===============
Here's code for the code module of frmLevelSelect, based on what you
already wrote. I'm going to take the liberty of changing the "Like '*'
& ctl.ItemData(varItem) & '*'" expressions that you've written to plain
tests for equality, but we can go back to something more like your
original code if that's necessary. I'm also going to assume that the
employee-level field is numeric, not text.
'------ start of code for frmLevelSelect ------
Public Function FilterCriteria() As String
Dim varItem As Variant
Dim strCriteria As String
With Me!lstLevel
For Each varItem In .ItemsSelected
strCriteria = strCriteria & ", " & ItemData(varItem)
Next varItem
If Len(strCriteria) > 0 Then
strCriteria = Mid$(strCriteria, 3) ' drop leading ", "
If .ItemsSelected.Count = 1 Then
strCriteria = _
"([Employee List].Level=" & strCriteria & ")"
Else
strCriteria = _
"([Employee List].Level In (" & strCriteria & "))"
End If
End If
End With
FilterCriteria = strCriteria
End Function
Private Sub OkCmd_Click()
Me.Visible = False
End Sub
Private Sub CancelCmd_Click()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
'------ end of code for frmLevelSelect ------
Here's sample code for the Open event of a report that wants to use this
form:
'------ start of example report code ------
Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
Dim strCriteria As String
DoCmd.OpenForm "frmLevelSelect", _
WindowMode:=acDialog
If CurrentProject.AllForms("frmLevelSelect").IsLoaded Then
strCriteria = Forms!frmLevelSelect.FilterCriteria()
DoCmd.Close acForm, "frmLevelSelect", acSaveNo
End If
If Len(strCriteria) > 0 Then
strRecordSource = Me.RecordSource
If Left(strRecordSource, 6) <> "SELECT" Then
strRecordSource = _
"SELECT * FROM [" & strRecordSource & _
"] WHERE " & strCriteria
Else
If Right(strRecordSource, 1) = ";" Then
strRecordSource = _
Left(strRecordSource, Len(strRecordSource) - 1)
End If
If InStr(strRecordSource, " WHERE ") > 0 Then
strRecordSource = _
strRecordSource & " AND " & strCriteria
Else
strRecordSource = _
strRecordSource & " WHERE " & strCriteria
End If
End If
Me.RecordSource = strRecordSource
End If
End Sub
'------ end of example report code ------
Note that the above is all "air code", and that it won't work right if,
for example, the report's recordsource contains an ORDER BY clause. But
reports normally do their own sorting, so an ORDER BY clause is seldom
necessary in a report's recordsource query. More elaborate code could
be used to cope with a wider variety of recordsource queries.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
