Form combo box is displaying the wrong information

Question

I have an unbound combo box that allows the user to select an employee name(requester). When that employee is selected, it updates the next box to show that particular employee's manager's name(owner).

I have the ManagerID as the bound column in the lookup query and the employee's name is supposed to show in the form when it is selected. Instead, after the user selects the employee, the box shows the name of the first employee assigned to that manager. For instance, I click on the combo box and I see a list of employees (that part shows correctly). I chose Sally Sue. As soon as I pick Sally Sue, the text in the box shows Bob Bath, which is the first employee that Sally's manager has in his list in the database.

Here is the biggest kicker... When I check the table that this form updates, Sally Sue is in the correct place. All of my data is in the correct place. The only thing that is wrong is the employee is showing wrong on the form after the user selects the employee. It doesn't affect the accuracy of my data, it's just annoying.

I have my bound column as 1 but I've tried moving it to 2 and making the column width 0 and moving my employee name to the first spot on the query.

Here is the SQL code that shows for my query:

SELECT tbl_Owner.Owner_ID, 
    tbl_Requester.RequesterName, 
    tbl_Owner.ownerName 
    AS RequestOwner
FROM tbl_Owner INNER JOIN tbl_Requester ON 
    tbl_Owner.Owner_ID = 
    tbl_Requester.RequestOwner
ORDER BY tbl_Requester.RequesterName;

When the employee name is selected, the form should display the selected employees' name. Instead, it is showing the manager's first assigned employee name.

you should have a me.repaint in the "after update event" so that it knows to repaint the form with new data. Try it out and let me know if that works. — Doug Coats
That query does not even show ManagerID nor EmployeeID field. How is that query relevant to issue? If BoundColumn is ManagerID then you are filtering records by that value, not EmployeeID. So of course you see the first record associated with that manager. Edit question to show code that applies filter. — June7
How and what do you pass to the other box when an employee is selected? — Wolfgang Kais

mazoula mazoula · Accepted Answer · 2019-02-17T07:16:17

assuming a table structure like:

So I've assumed each employee can have only one manager. Create a form with the employees table as the record source (since you wanted to show employee details.)
add two comboboxes to the form header (cboEmployees, cboManagers). for each combobox leave the control source blank. cboEmployee bound column = EmployeeId, cboManagers bound column = ManagerID.

cboEmployees.recordsource = SELECT Employees.EmployeeID, Employees.EmployeeName, Employees.ManagerFK
FROM Employees;
cboManagers.recordsource = SELECT Managers.ManagerID, Managers.ManagerName
FROM Managers;

Hide combobox columns you don't want to see then set the cboEmployees afterupdate event to:

Private Sub cmbEmployee_AfterUpdate()
cmbManager.Value = cmbEmployee.Column(2) 'set cmbManager to employees manager
Me.Filter = "EmployeeID = " & cmbEmployee.Value 'filter form to just the employees data
Me.FilterOn = True
End Sub

now when you change the employee name in the top left combobox, the top right combobox displays the manager name. only the employee name is included in the details on this form, but that changes as well.

Before:

After:

Form combo box is displaying the wrong information

1 Answers