Listbox question

[TesterA]

Hello all,

I just tried to do a search here for my particular problem and didn't turn anything up, so I'm posting a new thread. It's been about five years since I have programmed in VBA so my Kung Fu is a little weak.

My situation:

I am working on a database that is tracking employee audits. My form is used at the supervisory level, and the form has three options that drive how employee data is organized (through group option buttons). The first is at their own team level (the default), the next is if they are covering for another supervisor's team (option two) and the third is all agency employees.

Initially I wanted to create just one Recordset and then filter based upon whichever option they selected. This would then populate a list box with the names (concatonated first and last names) only, but their employee ID and other associated data would still exist in the query. Well, that didn't appear to work quite as intended because the listbox would display both columns. I did discover how to hide the first column (set in the listbox properties the length of column 1 to 0), but now I want to store the record in tblMain by the employee number rather than the value being displayed in lstEmpName. I gave up trying to set the .rowsource to a Recordset since it didn't seem to want to work (though I kind of feel that using one Recordset and then filtering based upon whichever case was selected would have been far more elegant than what I'm doing now).

Below is an example of my code for reference. Any suggestions? Really appreciate this

Private Sub grpEmps_Click()
On Error GoTo Err_Handler
   
Dim strSql1 As String
Dim strSQL2 As String
Dim strSQL3 As String
 
 
'This Sub determines the employee listing based upon the Option Group for Employees.  Case 1 = "My Employees", Case 2 = "Team Number",
'and Case 3 = All Employees.  These queries are static and should not be changed as all they do is look at tblEmployees and
'retrieves the names of the appropriate employees.
 
Select Case Me.grpEmps
 
    Case 1
    Me.lstEmpName = ""
   
    
    strSql1 = "SELECT tblEmployees.eEmpID,[tblEmployees.eEmpFName]+' '+[tblEmployees.eEmpLName] AS FullName " _
       & "FROM tblEmployees " _
       & "WHERE tblEmployees.[eSuperID]= '" & Form_frmMain.objuser.UserEmpID & "' ORDER BY tblEmployees.eEmpLName ASC"
 
        With Me.lstEmpName
            .RowSource = strSql1
            .ColumnCount = 2
            .BoundColumn = 2
           
        End With
       
        Me.txtEmpID = Me.lstEmpName.Column(0)
                
               
    Case 2
    Me.lstEmpName = ""
    Me.lstTeamNo = ""
       
    strSQL2 = "SELECT tblEmployees.eEmpID, (tblEmployees.eEmpFName+' '+tblEmployees.eEmpLName) AS FullName " _
       & "FROM tblEmployees INNER JOIN tblManagement ON tblEmployees.eSuperID = tblManagement.eEmpID " _
       & "WHERE tblManagement.TeamNo =" & Me.lstTeamNo.Value
      
       Me.lstEmpName.RowSource = strSQL2
       Me.lstEmpName.Requery
   
    Case 3
    Me.lstEmpName = ""
   
    strSQL3 = "Select tblEmployees.eEmpID, [tblEmployees.eEmpFName]+' '+[tblEmployees.eEmpLName] As FullName " _
        & "FROM tblEmployees ORDER BY tblEmployees.eEmpLName ASC"
   
        Me.lstEmpName.RowSource = strSQL3
  
 
End Select
 
    If Me.grpEmps.Value = 2 Then
        Me.lstTeamNo.Visible = True
        Else: Me.lstTeamNo.Visible = False
    End If
   
    
    
Exit_Here:
    Exit Sub
Err_Handler:
    MsgBox Err.Description & ":Error Occured in grpEmployees()", vbOKOnly, "Error occured in Form_Open"
End Sub
 
Private Sub lstTeamNo_AfterUpdate()
   
    Dim strSQL2 As String
   
    Me.lstEmpName = ""
   
    strSQL2 = "SELECT tblEmployees.eEmpID, (tblEmployees.eEmpFName+' '+tblEmployees.eEmpLName) AS FullName " _
       & "FROM tblEmployees INNER JOIN tblManagement ON tblEmployees.eSuperID = tblManagement.eEmpID " _
       & "WHERE tblManagement.TeamNo =" & Me.lstTeamNo.Value
      
       Me.lstEmpName.RowSource = strSQL2
       Me.lstEmpName.Requery
       Me.txtEmpID.Requery
 
End Sub

 

[TesterA]

For the record, the extension is .Accdb and is MS Access 2010.

 

[TesterA]

I came up with a solution to my problem.

Thanks everyone!