How to Populate ListBoxes Based on the Column Value of Another Listbox

**RunTime91** · 12-13-2019, 03:24 PM

Hello...

I'm using listboxes to display employee performance in 7 key areas

Problem is, the data is coming from 4 different tables - so what I have done (for now - I'm sure there is a better way) is create 8 listboxes

In the first Listbox (List23 - I'll rename them later) I have 3 columns which hold the EmpID, EmpName & EmpTeam -

What I'm trying to do is simply populate the other 7 listboxes with the data from the four tables based on the EmpID in Column(0) of List23

This works great for a single employee - but when I populate List23 with many employees (a full team) I am unable to populate the other listboxes with those employees stats.

The following works well for pulling the stats - but instead of lining the stats up with each employee listed in List23 it just writes over itself on the top row

I think I need to do something with the ListIndex of List47 - But not sure and not sure how

Any help would be greatly appreciated

Code:

With Me.List23
           If .ListCount > 1 Then
            .Selected(1) = True
          For i = 1 To .ListCount - 1
            .Selected(i) = True
              For Each Item In .ItemsSelected




                Me.List47.RowSource = "SELECT SUM (TblACD.HandleTime) AS HandleTime, SUM (TblACD.CallsHandled) AS CallsHandled, Format((HandleTime / CallsHandled),'###0') AS [AHT-YD] " & _
                                     "FROM TblACD " & _
                                     "WHERE TblACD.CallDate = #" & TxtHuddleDate - 1 & "# And TblACD.AgentID = '" & Me!List23.Column(0) & "' " & _
                                     "GROUP BY CallDate, AgentID, EmpName, TeamName, Location;"


        Next
      Next
    End If
  End With

**ranman256** · 12-13-2019, 03:42 PM

you dont cycle thru records filling a combo box (or listbox), instead , use a query.
list2 query looks at list1 as criteria:
select cities from table where [state]=forms!myForm!lstBox1

user picks item in list1, then list2 requeries to adjust for the new criteria:

Code:

sub lstBox1_afterupdate()
  lstbox2.requery
end sub

don't waste time writing code you don't need to.

**RunTime91** · 12-13-2019, 04:43 PM

Ranman - Thank you

This works as you described - ListBox1 Populates with 10 members of Team A and selecting each member will populate ListBox 2 with the respective data

But what I'm trying to do is get ListBox2 to populate with all of the data for each of the 10 team members - is there a way to do that?

I am asking to populate ListBox2 based on AgentID field in the ListBox1 - But I don't know how to populate the entire dataset without looping

And Yes! I agree, I do not want more code than I need...

**moke123** · 12-13-2019, 05:23 PM

Not totally clear on what you want but I think your saying you want a multiselect listbox for the first LB and cascade the selections to LB 2.

something like the attached?

**ssanfu** · 12-13-2019, 05:32 PM

Maybe you would post your dB so we don't have to guess what you are attempting to do. Sounds like you are setting up cascading combo/list boxes.
(Change any sensitive data before posting....)

**RunTime91** · 12-13-2019, 06:01 PM

I can see why there is the thought that what I'm trying to do is cascade - actually, what I'm trying to do has nothing to do with cascading...

When the user selects a value from either the Agent or the Team comboboxes I want the listboxes below to populate with the data

When I slect an agent all of the available values populate accross each of the listboxes - but when I select a team from the Team combobox

the first listbox (List23) populates just fine but what I'm trying to do is get the second Listbox (List47) to populate with the corresponding data.

To set-up a test:

Plug 12/11/2019 into Huddle Date
Plug 12/2/2019 into Last Week

At that point you can test the agent name: Anna Joseph
Or the Team Name: TC_MillerR

**moke123** · 12-13-2019, 07:10 PM

I dont think the query your building works. TxtHuddleDate should be .TxtHuddleDate, etc. Your form references are off.

you are using "With Me" so your references should all start with a dot. You'll notice with a dot intellisense kicks in. When I corrected them they appear to work.

You should also use Option Explicit as you have undeclared variables.

**ssanfu** · 12-13-2019, 08:08 PM

OK, I see what you are trying to do, but there are issues.

Question: Are you using the 8 list boxes for just viewing data or are you going to select a row for further processing?

Using the Form "Copy Of FrmSOPs":
You have 8 list boxes. When you select the Team Name, the query (in code) pulls every EmpName for the selected Team Name and sets List23 row source to that query (9 Employees).
But then you loop through List23, calculating "[AHT-YD]" and CHANGING List47 row source EVERY time you loop. Thus you only see one row in List 47 (no matter how many employees are in List23.
Selecting Anna Joseph in the Agent combo box results in [AHT-YD] equaling 235, but selecting the Team combo box results in [AHT-YD] equaling 256 (which is for Tyler Emery).

One way to fix this is to use a temp table (the data is temporary, the table is permanent) and base 1 list box on the temp table. (my preference in this case

)
Using a temp tables would also help if you are going to print the results......

Using 8 list boxes will be very hard to keep values synchronized across list boxes.

Other issues I saw:

- Modules to not have "Option Explicit" as the 2nd line.
- Can't tell if the tables are related.
- Tables do not have a PK defined.
- In table "TblSC" you have calculated fields. The calculations should be done in a query. (formatting is wrong also - should NOT be a time)
- I would have a table for team names and a table for employees (agents??).

- In code, you have statements like:

Code:

    With Me

        '***** SUPPLIES AGENT ID, NAME & TEAM OF AGENT *****
        .List23.RowSource = "SELECT AgentID, EmpName, TeamName " & _
                            "FROM TblACD " & _
                            "WHERE TblACD.CallDate = #" & TxtHuddleDate - 1 & "# And TblACD.AgentID = '" & Me!CmboAgent.Column(0) & "' " & _
                            "GROUP BY AgentID, EmpName, TeamName;"

Both TxtHuddleDate and Me!CmboAgent.Column(0) refer to controls on the active form.
But "With Me" also refers to the form you are on. If you are going to use "With Me", would be better to use

Code:

    With Me

        '***** SUPPLIES AGENT ID, NAME & TEAM OF AGENT *****
        .List23.RowSource = "SELECT AgentID, EmpName, TeamName " & _
                            "FROM TblACD " & _
                            "WHERE TblACD.CallDate = #" & .TxtHuddleDate - 1 & "# And TblACD.AgentID = '" & .CmboAgent.Column(0) & "' " & _
                            "GROUP BY AgentID, EmpName, TeamName;"

.TxtHuddleDate
.CmboAgent.Column(0) (which is the same as .CmboAgent because .Column(0) is the default property)

BTW - interesting problem.

Never thought of using multiple list boxes like this.

**RunTime91** · 12-18-2019, 01:26 PM

Moke & Ssnafu - Can't Thank You guys enough for the help!!

Sorry for the delayed response - I was pulled away from this project for a few days...

I created a temp table and have checked off some of the items on the check list - some I've delayed until I can get the query to work

Which it now does - perfectly for 1/2 of what I need -

The following provides the correct data for previous days results

Code:

.List23.RowSource = "SELECT EmpID, EmpName, TeamName, FORMAT(SUM(TblSOPTmp.HandleTime) / SUM(TblSOPTmp.CallsHandled),'###0') AS [AHT-YD], " & _
                            "FORMAT(SUM(TblSOPTmp.CallWorkTime) / SUM(TblSOPTmp.CallsHandled),'###0') AS [AWT-YD]," & _
                            "FORMAT(SUM(TblSOPTmp.SchCompliance) / COUNT(1),'Percent') AS [SC-YD], " & _
                            "FORMAT(SUM(TblSOPTmp.QualityScore) / COUNT(1),'Percent') AS [QA-YD] " & _
                            "FROM TblSOPTmp " & _
                            "WHERE TblSOPTmp.CallDate = #" & .TxtHuddleDate - 1 & "# And TblSOPTmp.TeamName = '" & .CmboTeam & "' " & _
                            "GROUP BY EmpID, EmpName, TeamName " & _
                            "ORDER BY EmpName;"

The next phase I need help with is trying to pull the previous weeks data from the same table and display it in the lase listbox

I have exhausted my brain and Google's brain not finding anything even close - Thus I fear it may not be possible

But I'll wait to hear that from you guys - (there has been a lot I've thought was impossible - then I come here and realize it is very possible)

I have created the following thinking I could just add it as a sub query - but I can't get it to work

Code:

.List23.RowSource = "SELECT EmpID, EmpName, TeamName, FORMAT(SUM(TblSOPTmp.HandleTime) / SUM(TblSOPTmp.CallsHandled),'###0') AS [AHT-YD], " & _
                            "FORMAT(SUM(TblSOPTmp.CallWorkTime) / SUM(TblSOPTmp.CallsHandled),'###0') AS [AWT-YD]," & _
                            "FORMAT(SUM(TblSOPTmp.SchCompliance) / COUNT(1),'Percent') AS [SC-YD], " & _
                            "FORMAT(SUM(TblSOPTmp.QualityScore) / COUNT(1),'Percent') AS [QA-YD] " & _
                            "FROM TblSOPTmp " & _
                            "WHERE TblSOPTmp.CallDate BETWEEN #" & .TxtLastWeek & "# AND #" & .TxtLastWeek + 6 & "# And TblSOPTmp.TeamName = '" & .CmboTeam.Column(0) & "' " & _
                            "GROUP BY EmpID, EmpName, TeamName " & _
                            "ORDER BY EmpName;"

If this cannot be accomplished - Can you steer me in a direction that might work

Thank You as always for your help

RT91

**RunTime91** · 12-18-2019, 04:21 PM

So it can be done!

Below is the query I built in the Access Grid - it works perfectly!

Code:

SELECT TblSOPTmp.EmpName, TblSOPTmp.TeamName, Format(Sum([TblSOPTmp].[HandleTime])/Sum([TblSOPTmp].[CallsHandled]),'0') AS [AHT-YD], 
FORMAT(Sum([TblSOPTmp_1].[HandleTime])/Sum([TblSOPTmp_1].[CallsHandled]),'0') AS [AHT-LW],
FORMAT(Sum([TblSOPTmp].[CallWorkTime])/Sum([TblSOPTmp].[CallsHandled]),'0') AS [ACW-YD],
FORMAT(Sum([TblSOPTmp_1].[CallWorkTime])/Sum([TblSOPTmp_1].[CallsHandled]),'0') AS [ACW-LW],
FORMAT(Sum([TblSOPTmp].[SchCompliance])/Count(1),"Percent") AS [SC-YD],
FORMAT(Sum([TblSOPTmp_1].[SchCompliance])/Count(1),"Percent") AS [SC-LW],
FORMAT(Sum([TblSOPTmp_1].[QualityScore])/Count(1),"Percent") AS [QA-LM]
FROM TblSOPTmp, TblSOPTmp AS TblSOPTmp_1
WHERE (((TblSOPTmp.CallDate)=#12/10/2019#) AND ((TblSOPTmp.EmpName)='Anna Joseph') AND ((TblSOPTmp.TeamName)='TC_MillerR')
AND ((TblSOPTmp_1.Calldate) Between #12/2/2019# And #12/8/2019#) AND ((TblSOPTmp_1.TeamName)='TC_MillerR')
AND ((TblSOPTmp_1.EmpName)='Anna Joseph') AND ((TblSOPTmp_1.SchCompliance)>0))
GROUP BY TblSOPTmp.EmpName, TblSOPTmp.TeamName;

Below is the same query modified to work in Access VBE - But of course, it doesn't work - It doesn't throw an error but I get nothing as a result...

Code:

.List23.RowSource = "SELECT TblSOPTmp.EmpName, TblSOPTmp.TeamName, " & _
                    "FORMAT(Sum([TblSOPTmp].[HandleTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [AHT-YD], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[HandleTime])/Sum([TblSOPTmp_1].[CallsHandled]),'###0') AS [AHT-LW], " & _
                    "FORMAT(Sum([TblSOPTmp].[CallWorkTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [ACW-YD], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[CallWorkTime])/Sum([TblSOPTmp_1].[CallsHandled]),'###0') AS [ACW-LW], " & _
                    "FORMAT(Sum([TblSOPTmp].[SchCompliance])/Count(1),'Percent') AS [SC-YD], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[SchCompliance])/Count(1),''Percent') AS [SC-LW], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[QualityScore])/Count(1),'Percent') AS [QA-LM] " & _
                    "FROM TblSOPTmp, TblSOPTmp AS TblSOPTmp_1 " & _
                    "WHERE (((TblSOPTmp.CallDate)= #12/10/2019#) AND ((TblSOPTmp.EmpName)='Anna Joseph') " & _
                    "AND ((TblSOPTmp.TeamName)='TC_MillerR') AND ((TblSOPTmp_1.Calldate) Between #12/2/2019# AND #12/8/2019#) " & _
                    "AND ((TblSOPTmp_1.TeamName)='TC_MillerR') AND ((TblSOPTmp_1.EmpName)='Anna Joseph') AND ((TblSOPTmp_1.SchCompliance)>0)) " & _
                    "GROUP BY TblSOPTmp.EmpName, TblSOPTmp.TeamName;"

I'm sure I'm just missing some of the mods - but I can't see which one(s)

Help?

**RunTime91** · 12-18-2019, 05:02 PM

Found the issue with the silent error - two ' ' on the second 'percent' - So that is solved

Now, just when i thought I had this one licked - Up creeps another issue

The Query above is for a single agent - Once that was working I thought adapting it to pull team data would be simple - UGH!!!!!

The code below is my attempt to get the single agent code to work with a team of agents

And, once again the code doesn't throw an error but for all the LW (LastWeek) data pulls, it pulls the same value (it is clearly not calculating for each agent on the team).

All of the YD (Yesterday) data pulls are accurate - Can anyone see anything glaring that I have missed.

I believe I'm close - but just can't see the finish line -

Code:

.List23.RowSource = "SELECT TblSOPTmp.CallDate, TblSOPTmp.EmpName, TblSOPTmp.TeamName, " & _
                    "FORMAT(Sum([TblSOPTmp].[HandleTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [AHT-YD], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[HandleTime])/Sum([TblSOPTmp_1].[CallsHandled]),'###0') AS [AHT-LW], " & _
                    "FORMAT(Sum([TblSOPTmp].[CallWorkTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [ACW-YD], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[CallWorkTime])/Sum([TblSOPTmp_1].[CallsHandled]),'###0') AS [ACW-LW], " & _
                    "FORMAT(Sum([TblSOPTmp].[SchCompliance])/Count(1),'Percent') AS [SC-YD], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[SchCompliance])/Count(1),'Percent') AS [SC-LW], " & _
                    "FORMAT(Sum([TblSOPTmp_1].[QualityScore])/Count(1),'Percent') AS [QA-LM] " & _
                    "FROM TblSOPTmp, TblSOPTmp AS TblSOPTmp_1 " & _
                    "WHERE (((TblSOPTmp.CallDate)= #12/10/2019#) " & _
                    "AND ((TblSOPTmp.TeamName)='TC_MillerR') AND ((TblSOPTmp_1.Calldate) Between #12/2/2019# AND #12/8/2019#) " & _
                    "AND ((TblSOPTmp_1.TeamName)='TC_MillerR') AND ((TblSOPTmp_1.SchCompliance)> 0)) " & _
                    "GROUP BY TblSOPTmp.CallDate, TblSOPTmp.EmpName, TblSOPTmp.TeamName " & _
                    "ORDER BY TblSOPTmp.EmpName;"

Thank you so much for any help

**ssanfu** · 12-18-2019, 05:34 PM

There are two single quotes in this line in front of PERCENT.

Code:

"FORMAT(Sum([TblSOPTmp_1].[SchCompliance])/Count(1),''Percent') AS [SC-LW], " & _

This is how I would write the VBA/SQL (why the SQL is set to " .List23.RowSource " is a mystery to me.
Also, to test this SQL, I duplicated table "TblACD" (renamed to TblSOPTmp - but there are missing fields.

)

Code:

Private Sub CmboTeam_AfterUpdate()
    Dim sSQL As String

    With Me

        sSQL = "SELECT TblSOPTmp.EmpName, TblSOPTmp.TeamName,"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp].[HandleTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [AHT-YD],"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp_1].[HandleTime])/Sum([TblSOPTmp_1].[CallsHandled]),'###0') AS [AHT-LW],"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp].[CallWorkTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [ACW-YD],"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp_1].[CallWorkTime])/Sum([TblSOPTmp_1].[CallsHandled]),'###0') AS [ACW-LW],"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp].[SchCompliance])/Count(1),'Percent') AS [SC-YD],"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp_1].[SchCompliance])/Count(1),'Percent') AS [SC-LW],"
        sSQL = sSQL & " FORMAT(Sum([TblSOPTmp_1].[QualityScore])/Count(1),'Percent') AS [QA-LM]"
        sSQL = sSQL & " FROM TblSOPTmp, TblSOPTmp AS TblSOPTmp_1"
        sSQL = sSQL & " WHERE (((TblSOPTmp.CallDate)= #12/10/2019#) AND ((TblSOPTmp.EmpName)='Anna Joseph')"
        sSQL = sSQL & " AND ((TblSOPTmp.TeamName)='TC_MillerR') AND ((TblSOPTmp_1.Calldate) Between #12/2/2019# AND #12/8/2019#)"
        sSQL = sSQL & " AND ((TblSOPTmp_1.TeamName)='TC_MillerR') AND ((TblSOPTmp_1.EmpName)='Anna Joseph') AND ((TblSOPTmp_1.SchCompliance)>0))"
        sSQL = sSQL & " GROUP BY TblSOPTmp.EmpName, TblSOPTmp.TeamName;"
        Debug.Print sSQL  '<<-- comment out after testing complete
        .List23.RowSource = sSQL

Well, I was pulled away too long.

**RunTime91** · 12-18-2019, 07:28 PM

Hey Ssnafu...

Here is a new copy of the Db - I have trimmed out everything but the table and the UI

I have hard coded the query parameters so there are no dates to enter

You can simply select any Agent from the Agent dropdown and any Team from the Team Dropdown

When you select an Agent the data loaded is acurate for Anna Joseph

When you select a Team the data is accurate for the YD (Yesterday) fields for each of the agents on the team

but not accurate for the LW (LastWeek). All the data in the QA field is/should be 0.00%

I think I'm close...but...

Thanks Ssnafu for your help -

RT91

**Micron** · 12-18-2019, 08:32 PM

ssnafu, you must be thinking of changing your user name here by now.

**RunTime91** · 12-19-2019, 12:37 PM

Can anyone help with this?

How to Populate ListBoxes Based on the Column Value of Another Listbox

Thread Tools

How to Populate ListBoxes Based on the Column Value of Another Listbox

How to Populate ListBoxes Based on the Column Value of Another Listbox

Similar Threads

Populate column based on data in another column

populate data in listbox based on textbox value chang

Populate listbox based on another listbox selection without VBA

Unable to populate textbox based on listbox

ListBox Populate Directory Based On User Input

Posting Permissions