Solving MS Access Form Design Issues: Displaying Employee Names Instead of IDs

Learn how to create MS Access forms that display `Employee Names` while saving the `Employee IDs` for accurate data entry and reporting.
---
This video is based on the question https://stackoverflow.com/q/69060015/ asked by the user 'jbarr' ( https://stackoverflow.com/u/13616282/ ) and on the answer https://stackoverflow.com/a/69060556/ provided by the user 'June7' ( https://stackoverflow.com/u/7607190/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: MS Access Table Query Form Design Issue

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving MS Access Form Design Issues: Displaying Employee Names Instead of IDs

When working with MS Access, many users encounter challenges when it comes to designing forms that integrate with queries and tables effectively. A common issue arises when attempting to display user-friendly information while still capturing the correct underlying data. This post will delve into a specific problem: how to create a form that allows users to select from employee names while saving the corresponding employee IDs.

Understanding the Problem

In our case, we have two tables:

Employees (Table 1)

Employee ID (Key)

Employee Name

Status

Attendance (Table 2)

Attendance ID (Key)

Employee

Date

Note

We also have a query that returns active employees based on their status. The goal is to design a form for entering attendance records that:

Displays a dropdown list of active employee names

Saves the corresponding Employee ID to the database

Shows user-friendly names rather than IDs on the form

However, users often find that the form defaults to displaying Employee IDs, which is not user-friendly and poses challenges during data entry.

The Solution

Step 1: Evaluate Naming Conventions

Before diving into the technical aspects, it’s advisable to avoid using spaces in naming conventions. Using clear, consistent names helps in avoiding confusion and makes referencing objects easier.

Step 2: Setting Up the ComboBox Properties

To make your form behave as desired, you will need to adjust the properties of the ComboBox used to select Employees. Here are the key settings you will need:

ControlSource: Set this to the field in Table 2 where you want to save the Employee ID. In our case, this would be the Employee field.

ColumnCount: Set to 2 to include both the Employee ID and Employee Name.

ColumnWidths: Set to 0";1" which means hide the Employee ID by allocating 0 space for it while allowing the Employee Name to take the remaining space.

BoundColumn: Set to 1 so that the first column (Employee ID) is what gets sent to the database.

RowSource: Use the following SQL query to populate the ComboBox:

[[See Video to Reveal this Text or Code Snippet]]

This SQL statement retrieves both the Employee ID and Name for active employees, allowing you to display just the names while keeping the functionality of saving the IDs.

Step 3: Enhance Employee Name Display

For better presentation, you might want to split Employee Names into separate fields: FirstName, MiddleName, and LastName. This way, you can customize how names appear. The RowSource would then look like this:

[[See Video to Reveal this Text or Code Snippet]]

This gives you a nicely formatted Full Name (e.g., "Doe, John A") while still storing the identifier you need for reporting.

Conclusion

By following these steps and integrating them into your MS Access form design, you will enhance the usability of your data entry system significantly. Users will appreciate being able to work with recognizable names while you maintain the necessary data integrity by storing the proper Employee IDs.

Implement these strategies to not only streamline your data entry process but also to improve overall user experience within your MS Access applications.

Видео Solving MS Access Form Design Issues: Displaying Employee Names Instead of IDs канала vlogize