MS Access Displayed v. Saved Value in Combo Box

Question

I have a simple database with two tables and one relationship between them:Employees, Managers, Relationship.

Each employee has exactly one manager, but each manager can manage multiple employees. For adding employees to the database, I have this form: Employee Form.

As it stands, the user must enter the ManagerID of the new employee's manager when adding an employee. What I would like is for the user to select the new employee's manager from a combo box. The drop down options should be the names of the managers (say, from the FullName column of the Managers table). However, once the user selects a name from the drop down, I would like the ManagerID corresponding to that name to be saved in ManagerID field of new employee's entry.

Can I solve this problem by basing the form off of a query, or will I need to customize the form/combo box with some VB code?

Second paragraph can be resolved if you follow the combo box wizard to build a bounded combo box (which will hide but save ManaegerID but show FullName to users). Wizard automatically pops up when you add a combobox to form in form design. — Parfait

Psychoceramics Psychoceramics · Accepted Answer · 2020-08-25T21:52:57

This will take basically just one line of VBA code; most of it is done through Access.

Point the combo box's data source to the table of managers. In the 'Row Source' property of the combo box under 'Data' in the Property Sheet, set the Row Source to a query for the first + last name of the managers. For example:

SELECT ManagerID, FirstName, LastName FROM tblManagers ORDER BY FirstName;

Then go to the ComboBox's Format tab and set the Column Count to 3. Set the column widths appropriately; to hide the first column with the ID field, set it to 0, and set the other column widths to whatever looks right. For example: 0";0.5"'0.5" will hide the first column, and set the other two to a half-inch width.

To have the TextBox automatically display the ID number of the manager in the ComboBox (which honestly, if you don't need to display it for some reason, why not just hide the ID field from the user altogether? Food for thought), you will need to add the VBA code to the ComboBox's AfterUpdate event. That way, whenever the combo box is updated, the textbox will change accordingly. You may need to first go into design mode and change the 'HasModule' property of the form to 'Yes'; after that, use the 'Events' tab of the ComboBox's Property Sheet to open up the code builder for the 'After Update' event. It should take you to the VBA builder, with an auto-generated Private Sub [combobox's name]_AfterUpdate()

Set the code to the following:

Private Sub ComboBox_AfterUpdate() 'Obviously, sub your combo boxs name for ComboBox

    Me.TextBox = Me.ComboBox 'Obviously, sub your text boxs name and your combo boxs name here

End Sub

Since the default bound column for the ComboBox is the first, this sets the value of the text box to the value of the first column of the ComboBox.

MS Access Displayed v. Saved Value in Combo Box

1 Answers