Listboxes, Multiple Selection & Rowsource! Help!

[gibblin]

I'm Currently building a database to track employees competencies. So certain people will be qualified to do certain tasks.

E.g in a table called Data ive got

Name Tsk1 Tsk2 Tsk3 Tsk4
Bob True True False False
Billy False True True True
Bryan True False False True


I have a form (frm1) with a list box (lst1) which lists the competencies (Tsk1, Tsk2, Tsk3, Tsk4) and has multi-select.
I have another list box (lst2) which is updated when competencies are selected and shows the names of the people who match the competencies.

For selecting 1 item, I've been using a SELECT...FROM....WHERE function to update the ROWSOURCE of lst2.

_________
If Lst1 = "Tsk1" Then

tmp1 = "SELECT Data.Name"
tmp1 = tmp1 & " FROM Data "
tmp1 = tmp1 & (" WHERE (Data.Tsk1=" & True)

Me![Lst2].RowSource = tmp1
End if
__________

However i want to avoid using IF....END IF and need to incorporate multiple selection within lst1. I was thinking about using FOR EACH....IN....NEXT but i'm struggling.

Any help would be appreciated.

 

[fneily]

This won't answer your question as is stated. But let's look at it from a Normalizing view. If you drop the numbers on your column headings Tsk1, Tsk2, etc. you get Tsk, Tsk, Tsk, etc. - repeatitive column headings. This violates the first normal form. What happens if in the future there will be a task5? Then would you create Tsk5? Then you have variable length records which violates the first normal form.
I assume you have a tblEmployee table. The task table should look like - EmployeeID Task.
Then the data would look like
Bill Typing
Bill Copying
Bill Running
Jack Typing
Sue Copying
Etc.

Then any analysis you want falls right out.

I'm sure someone else will address your question, though.

 

[gibblin]

Many thanks for your reply.

The data i've got is already in place from a spreadsheet which i'm trying to incorporate. The 'Tsk' field names were just generic ones i gave for the purposes of making my question a bit easier to understand. The real field names are a bit more complex.

Regardless of these fields, what you are saying makes complete sense and i should've considered that in the first place really.

If setting the table out like that is the best option, i'm still struggling to figure out how to go down the first list box to identify which items are selected, pull the records from the table and then display those records in the 2nd listbox (with no duplicate names).

Cheers.

 

[FastLearnerIThink]

Have you tried something like this? Please note that the sample is from a multi column listbox. Therefore I am indetifying a specific column.

Private Sub cmdTest_Click()
Dim lst1 As ListBox, itm As Variant, strSql As String
Set lst1 = Me.lstCurrent
strSql = "SELECT * FROM tblInventory"
If lst1.ItemsSelected.Count > 0 Then
    strSql = strSql & " WHERE fldPropNo = "
    For Each itm In lst1.ItemsSelected()
        strSql = strSql & lst1.Column(2, itm) & " AND "
    Next itm
    strSql = Left$(strSql, (Len(strSql) - 5))
Else

End If
End Sub

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[FastLearnerIThink]

Reposting because more than likely if you tried the above you got some errors. Tested with message box but not with actual process, but noticed the syntax error after reviewing my post again.

Please try this instead. Sorry for the oversight. Any questions or errors let me know.

Private Sub cmdTest_Click()
Dim lst1 As ListBox, itm As Variant, strSql As String
Set lst1 = Me.lstCurrent
strSql = "SELECT * FROM tblInventory"
If lst1.ItemsSelected.Count > 0 Then
    strSql = strSql & " WHERE "
    For Each itm In lst1.ItemsSelected()
        strSql = strSql & "fldPropNo = '" & lst1.Column(2, itm) & "' AND "
    Next itm
    strSql = Left$(strSql, (Len(strSql) - 5))
Else

End If
End Sub

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[gibblin]

thanks for your reply, i'll try it out. unfortunately the database is at work and i'm not there til monday. But i will reply then.

 

[FastLearnerIThink]

By the way, use "OR" instead. Not AND because one field cannot have three different values. And you are probably hoping the results will be all records in which the specific field has one of the values selected.

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[gibblin]

I'm trying to incorporate this but it's not having it! It probably sounds dumb but what do i put in the SET statement?

I've tried different things but in the end removed it
And therefore it's not picking up the selected item from lst1.


I tried strSql = strSql & "Competency = 'Tsk1'" just to see if the rest of it worked, and it did. Also for this i used Me![Lst2].RowSource = strSql to update Lst2 with this record.

Once i've got the SET statement working is this the correct way to update Lst2? If so, does this go after the NEXT statement or the END IF one?

Apologies for my incompetence!

 

[gibblin]

It's ok, i've finally figured it out. I made a few amendments, but this works perfectly. Thanks for your help.


Dim varItem As Variant
Dim strSQL As String


If Lst1.ItemsSelected.Count <> 0 Then


strSQL = "SELECT Data.Surname,Data.Forename from Data WHERE "
For Each varItem In Lst1.ItemsSelected
strSQL = strSQL & "Data.Competency = '" & Lst1.ItemData(varItem) & "' OR "
Next varItem
strSQL = Left$(strSQL, (Len(strSQL) - 4))

Me.RecordSource = strSQL
Me![Lst2].RowSource = Me.RecordSource
Me![Lst2].ColumnCount = 3
Me![Lst2].ColumnWidths = "3cm; 3cm; 3cm"
Else
MsgBox "Please select from the listbox", vbOKOnly
End If