ANY SUGGESTIONS....List box problems

[Guest]

I've created a Training database and right now we have over 800 employees in
it. I have created a Quality Alert form so when an "alert" is entered, the
supervisor would like to show all the employees that are trained on this
alert. I have a multi-select list box so the user can highlight which
employees are trained on this alert. The supervisor would then like to be
able to see the employees that he selected and that is where I get stumped.
I tried creating a text box but it limits the number of employees I can put
in it. I tried a subform but couldn't figure out how to get that to show the
employees selected.

Is there a better/easier way to do something like this. My main objective
is to allow the supervisor (or anyone) to see which employees have been
selected for each alert so they can run a report on it. Thanks for your
help!!!

 

[Guest]

Hi Michelle,
If you go in the access help and you search for multiselect and then you
choose selected property in the topic list I think that the example of this
topic fit for you.

HTH Paolo

 

[Guest]

Michelle,
I thought we had resolved this, but then I work with so many, it is easy to
get lost.

So, as you have discovered, there are only so many characters you can put in
a text box. Even if you could, it would not be readable. The list box you
are using is the easiest way to see a list of items. There are a couple of
things worth mentioning that may help. First, if the problem is you don't
see enough information about the employees, you can have multiple columns in
a list bos, so you could add additional columns. If the problem is filtering
the list by a specific Alert, then a combo box control that allows the user
to select an alert and filter the contents of the list box would work. It
gets complex, but it is even possible to allow multiple alerts to be selected
from another list box rather than a combo box. So the user can select
multilpe alerts and the employee list box would then present all the
employees trained in all the selected alerts.

Okay, so if you are tyring to make it more complicated than that, there
really is no point. The only other thing you need to consider is how to
filter the report based on the selected alert. Here is a function that will
provide that capability. It turns the selected items in a list box into a
string you can then use for the Where argument of the open report method.

********************************************
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
*****************************************
Here is how you might use it:

Dim strWhere As String

strWhere = BuildWhere(Me.MyListBox)
If Len(strWhere) > 0 Then
strWhere = "WHERE " & strWhere
End If
Docmd.OpenReport "rptAlertTraining", , , strWhere

 

[Guest]

Thanks Dave, I'll give that a try and let you know how it worked. I
appreciate all your help on this.

 

[doyle60]

<I've created a Training database and right now we have over 800
employees in
it. I have created a Quality Alert form so when an "alert" is
entered, the
supervisor would like to show all the employees that are trained on
this
alert. I have a multi-select list box so the user can highlight
which
employees are trained on this alert. >

It doesn't seem to me, from what I read above, that this is a list box
situation. It sounds as if you want results. If you have a field in
a table that makes it clear that an Alert is entered for each
employee, than your job is done. Simply do a report that prints only
such persons out, by doing a query with such criteria. If you simply
want to see the names of each employee that are trained, simply do a
subform.

If you are saying you want such a report but you don't actually want
to show everyone, just some of these employees, well, that's a
different matter. But I've read your post several times and I'm not
sure that is what you are saying.

Before continuing, let's get it clear what you really want to do
here. List boxes are a pain in the ass, quite frankly.

Matt

 

[pietlinden]

<snip>I have created a Quality Alert form so when an "alert" is
entered, the
supervisor would like to show all the employees that are trained on
this
alert.</snip>

I see no reason why you couldn't use a main/subform for this. The
main form would be the Alert, and the subform would be based on a
query to connect all the usual "students/classes" tables, and show the
relevant "students". I disagree that listboxes are terribly hard to
work with - they aren't that hard once you know how and where to use
them. You have to be able to code in order to use them effectively
most of the time.

If you use a main/sub, you can search by Alert, and show everyone. No
code required. you could just run a filter-by-form.

 

[Guest]

After reading all your comments, I'm thinking all I really need is a way to
put the names selected in a report. But I am so confused right now. I tried
your code below and put the function in my declarations page but wasn't sure
where to put the second part (below)...

Dim strWhere As String

strWhere = BuildWhere(Me.Employee)
If Len(strWhere) > 0 Then
strWhere = "WHERE " & strWhere
End If
Docmd.OpenReport "1 Quality Alert Report", , , strWhere

Thanks again for everyones help on this!

 

[doyle60]

In the usual case, the names selected would appear in the report as
headers or something and so no special attention is needed. The ones
selected is obvious.

If you mean to say, you want to list them at the head of the report
(because some may be null or for some other reason), then no code is
needed. You can simply add a subreport that lists the names
selected. (Evidentally, if you are using a list box, you already of a
query that returns the ones selected.)

The sub may appear a bit ugly since the names will have to appear
vertically. To make them a string so they can appear horizontally is
a bit harder to do.

Matt