Creating a combo box in Microsoft Access that retrieves its values from another table or query allows you to select a customer from a list when filling out service forms or orders.
To begin, it's essential to understand the difference between a value list combo box and a relational combo box. A value list combo box contains a small list of options directly within the form, such as titles like Mr., Mrs., Miss, etc. However, the downside is that you must update it on every form where it exists. A relational combo box, on the other hand, retrieves its list of options from a table or query, ensuring that updates are reflected across all forms automatically.
First, create the Titles Table. Open table design mode and create a new table. Add a field for TitleID (auto number) and TitleName (short text). Save the table as TitleT. Enter some sample data like Mr., Miss, Mrs., and Doctor.
Next, modify the Customer Table. Open the CustomerT table in design view. Add a new field TitleID of type number (long integer) to store the title. Save and close the table.
Then, add the Combo Box to the Customer Form. Open the CustomerF form in design view. Insert a combo box and select the option to get values from another table or query. Choose TitleT as the source. Include TitleID and TitleName fields in the combo box. Set the combo box to store TitleID in the customer table.
After that, design the Service Table and Form. Create a new table ServiceT with fields: ServiceID (auto number), ServiceDate (date/time), CustomerID (number), Description (short text), and Notes (long text). Save and close the table. Create a new form ServiceF based on ServiceT. Add a combo box to select a customer, using a query that concatenates the customer's first and last name for display.
When concatenating names, create a query that combines first and last names to display in the combo box. Use the query to source your combo box, ensuring both names appear together in the list.
Updating the combo box across forms is a significant advantage of using a relational combo box. If you add a new title in the TitleT table, it will reflect in all forms using the combo box.
Handling non-listed items can be done using the OnNotInList event to allow users to add new items directly from the combo box. This involves a bit of programming but is straightforward to implement.
Creating a relational combo box in Access not only simplifies data entry but also ensures consistency across your database. By following the steps outlined above, you can efficiently manage and update your lists without the need for repetitive updates across multiple forms. This method adheres to the principles of relational database design, ensuring data integrity and ease of use.
Here's a video tutorial that explains how to create a "relational" combo box that gets its list of values from a table or query: https://599cd.com/Relational.
