Form design question

[natesternberg]

I'm looking for some high-level guidance here. I have two tables, tblVolunteers and tblSkills. tblVolunteers contains a lookup column listing every skill for each volunteer. I'm writing a form that has a listbox with all skills; I want the user to highlight whatever skills she's interested in, and the second listbox will contain all volunteers that have those skills.

What's the best way to do this? Do I bind some VBA to the AfterUpdate event of the skills listbox, which performs a query, and pushes the results into the second listbox? Or do I set the row source of the volunteers listbox to a query whose WHERE clause is based on the skills listbox? Or something else?

How would you approach this?

 

[Kiwiman]

Howzit

I would have a third table called "tblVolumteersSkills" having 3 columns (PSID <PK> VolunteerID<FK> and SkillID<FK>) where you can list as many skills as you like for each volunteer.

You will end up with 4 forms - frmSkills, frmVolunteer - which has a subform holding the frmVolunteerSkills, and a SearchForm.

Your search form will have a list box of all the skills, and you would run a query against the 2 joined tables (tblVoluneeers and tblVolunteerSkills) to return your list of volunteers who meet the users criteria.

 

[vbaInet]

I would have a third table called "tblVolumteersSkills" having 3 columns (PSID <PK> VolunteerID<FK> and SkillID<FK>) where you can list as many skills as you like for each volunteer.

That is if a volunteer can have more than one skill. If this is not the case then perhaps this isn't necessary. natersternberg can clarify this.

If this is a simple case of a one-to-one relationship between Volunteers and Skills then:

1. Create a Continuous form based on Volunteers
2. Drop a combo box (not a listbox) in the Form Header section of the form
3. Select the third option that reads, "Find a record on my form based on the value I selected in my combo box"
4. Follow the wizard through and base it on the Skills table.

 

[natesternberg]

Thanks to everyone who replied! Yes, a volunteer can have multiple skills. This will sound dumb of me, but I didn't realize you could just write VBA to perform a SQL query when an update event is fired from one UI element, and in that event, add the query results to another element on the form. Once I realized you could do that, I just wrote the following Sub. Which I'll include in case someone else stumbles across this thread later.

Private Sub lbSkills_AfterUpdate()
    lbVolunteers.RowSource = ""             ' Clear names listbox
    queryString = "SELECT Lname & ', ' & Fname AS Name FROM tblVolunteers INNER JOIN tblSkills ON tblSkills.SkillID = tblVolunteers.Skills.Value "
    orderClause = " ORDER BY Lname"
    whereClause = " WHERE"
    firstClause = True
    For I = 0 To lbSkills.ListCount
        If lbSkills.Selected(I) Then
            If firstClause = False Then
                whereClause = whereClause & " AND"
            End If
            whereClause = whereClause & " tblSkills.Skill = '" & lbSkills.ItemData(I) & "'"
            firstClause = False
        End If
    Next I
    If firstClause = True Then              ' No elements were selected, so remove the WHERE clause
        whereClause = ""
    End If
    queryString = queryString & whereClause & orderClause & ";"
    'MsgBox queryString
    tbOutput.Value = queryString
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(queryString)
    Do While Not rst.EOF
        lbVolunteers.AddItem (Chr(34) & rst![Name] & Chr(34))       ' Quotes required because there's a comma in the [Name] column
        rst.MoveNext
    Loop
    rst.Close
    dbs.Close
End Sub

 

[vbaInet]

Good job for coming up with that code natesternberg.

It can be optimised and I will give you some pointers:

1. Instead loop through the entire listbox use the ItemsSelected property to loop through just the selected items.
2. You don't need to add the item as a Value List. Use the IN() operator against the concatenated values from suggestion 1 and concatenate it to querystring. Then set the RowSource property of the listbox to the finished querystring.

 

[natesternberg]

Thanks for the pointers! I didn't know about the ItemsSelected property. So now I have the code below, addressing #1 in your suggestions.

I don't entirely understand your second suggestion, though. Do you mean, change my SQL query so that, rather than saying "WHERE F=A AND F=B", etc., I say, "WHERE F IN (A, B, etc.)" And then, rather than executing a query and pushing the results into the listbox, just set the listbox RowSource to the query itself?

Private Sub lbSkills_AfterUpdate()
    lbVolunteers.RowSource = ""             ' Clear names listbox
    queryString = "SELECT Lname & ', ' & Fname AS Name FROM tblVolunteers INNER JOIN tblSkills ON tblSkills.SkillID = tblVolunteers.Skills.Value "
    orderClause = " ORDER BY Lname"
    whereClause = " WHERE"
    firstClause = True
    For Each Item In lbSkills.ItemsSelected
        If firstClause = False Then
            whereClause = whereClause & " AND"
        End If
        whereClause = whereClause & " tblSkills.Skill = '" & lbSkills.ItemData(Item) & "'"
        firstClause = False
    Next Item
    If firstClause = True Then              ' No elements were selected, so remove the WHERE clause
        whereClause = ""
    End If
    queryString = queryString & whereClause & orderClause & ";"
    'MsgBox queryString
    tbOutput.Value = queryString
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(queryString)
    Do While Not rst.EOF
        lbVolunteers.AddItem (Chr(34) & rst![Name] & Chr(34))       ' Quotes required because there's a comma in the [Name] column
        rst.MoveNext
    Loop
    rst.Close
    dbs.Close
End Sub

 

[spikepl]

Since vbaInet seemingly is off for a break:

change my SQL query so that, rather than saying "WHERE F=A AND F=B", etc., I say, "WHERE F IN (A, B, etc.)"

That's what the words mean

rather than executing a query and pushing the results into the listbox, just set the listbox RowSource to the query itself?

That's what the words mean

 

[vbaInet]

Since vbaInet seemingly is off for a break:

Well deserved for that matter Pretty short though.