Wrong Record
By Richard Rost
3 days ago
Combo Box Selects Wrong Record After UNION Query In this lesson, we will examine why a Microsoft Access combo box can display the wrong record when a union query combines tables with matching numeric IDs. I will show you how duplicate AutoNumber values from separate tables create collisions and how adding a source prefix, such as E15 or C15, creates a unique selector value so Access can identify the correct record. Lara from Saskatoon, Saskatchewan (a Silver) asks: I'm combining my Employees and Customers tables into one combo box using a UNION query. The list looks fine, but sometimes when I choose a customer, the combo box ends up showing an employee instead. I noticed that one employee has EmployeeID 15 and one customer has CustomerID 15. Could those matching numbers be causing the problem? How do I make sure Access knows which record I selected? PrerequisitesLinks
Keywords TechHelp Access, Access combo box wrong item selected, union query duplicate IDs, combo box bound column, duplicate AutoNumber IDs, unique combo box value, prefix ID values, combined tables combo box, employee customer union query, combo box ID collision
Subscribe to Wrong Record
Get notifications when this page is updated
More InformationTranscript Ever combine two lists in Microsoft Access, pick something from a combo box, and have the wrong item show up? It can happen when the lists come from separate tables and the combo box uses the same numeric ID for two different records.
Let's look at why this happens, and I'll show you how to fix it.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to look at a sneaky little problem that Microsoft Access combo boxes have. When you're combining records from different tables using a union query, the list looks fine. When you select a record, Access sometimes displays the wrong one. And as you're about to see, there's a perfectly logical reason why this happens.
So let's start with the question from one of my students. Today's question comes from Lara in Saskatoon, Saskatchewan, one of my silver members. He says, "I'm combining my employees and customer tables into one combo box using a union query. The list looks fine, but sometimes when I choose a customer, the combo box ends up showing an employee instead. I noticed that one employee has employee ID 15 and one customer has customer ID 15. Could those matching numbers be causing the problem? How do I make sure Access knows which record I selected?"
Well, Lara, I can see why that would be confusing. Let's take a closer look at what's happening.
All right, we have two separate tables. Each one has its own numbering system. Employee 15 and customer 15 aren't necessarily the same person. But when the combo box is only looking at the number 15, because remember that first column, that first value, usually that ID, is the value of the combo box, then you've got a collision when you've got two number 15s.
I ran into the same problem in my fitness database series, which we'll talk about in just a minute.
Here's what the problem looks like in Access. On the left, employee E.T. has Alex Morgan with employee ID 15. And on the right, customer T has Sam Rivera with customer ID 15. Two different people, each table has its own numbering. So having a 15 in both tables is perfectly fine and normal.
The problem is when we combine those people into one selector and expect that number by itself to tell us which person we picked. Now, an AutoNumber gives you a unique value inside its own table, but it doesn't promise that same number won't appear in some other table.
A union query combines two sets of records into one list, but it doesn't magically create a new shared identity system. So if the combo box binds to that plain numeric ID, two different choices can look identical as far as the value in the combo box is concerned.
So here's the combined list. Alex and Sam are both in the list, and they both have ID 15. The names are different, so the union query keeps both rows. Now, I've included a person type column so we can see where each row came from, but that label doesn't fix a combo box that's still only using the ID column as its value.
You choose Sam, and you've got the number 15 in the box. Choose Alex, and you also have the number 15 in the box. So we need a different value for each choice in the combo box.
The trick is to make a temporary selector value that's unique across the combined list. For example, you can put an E in front of an employee ID or a C in front of the customer ID. Now E15 and C15 are different values.
So the original employee ID and customer ID fields don't change. You're just giving the combo box a reliable way to tell the two sources apart. Now, if you use that selection later in something like VBA code, you'll also need to interpret the prefix and work with the correct table. But that's a separate step from fixing the display and selection problem that we're covering here.
Now look at the first column. Alex is E15 and Sam is C15. Same people, same original numbers, but different selector values. The E tells us employee, and the C tells us customer. Now that's the column that the combo box needs to use as its bound value.
You can still display a person's name to the user. We're changing the temporary query value, not converting the AutoNumber fields in either table. I'm going to show you the same trick in a minute with the fitness database using foods and meals.
Now, before we get into it, you should know what a union query is. It's taking multiple data sources and putting them together in a single query. If not, go watch this video. You should understand how relational combo boxes display a name using an ID. And you should understand how concatenation works, putting two things together.
If any of those are new to you, go watch these videos first. They're free. They're on my YouTube channel. Go on my website, go watch those first, and then come on back.
And while you're at it, make sure you hit that like and subscribe. It helps me out. It helps the channel out. It helps more people find my videos, and I appreciate it.
Now, before we jump to the demonstration, this clip comes from my fitness database series, and some people skip it because they think it's about fitness. The point of the series isn't about fitness. Fitness is just the backdrop while we build a real Microsoft Access database and work through all kinds of useful stuff. Forms, queries, VBA, design problems. There's lots of practical techniques in that series you can use in your own databases, even if you've never tracked a workout in your life and you don't care what a gallery is.
The clip you're going to see is from part 42 of the fitness database series. In this example, I combined foods and meals into a single combo box. Watch what happens when I try to pick an item and how I track the problem down to duplicate numeric IDs and how I fix the selector so Access can tell the two kinds of records apart.
See, it's Rick's coffee. Oh, wait a minute. I went to banana. Oh, I'll try. Wait, that's a bug. Let's try that again. Let's pick Rick's coffee again. Coffee, Rick's coffee, click, banana. Why is that happening?
Pause the video now and see if you can figure out on your own why that's happening, and then I'll tell you the problem in a minute and what the solution is.
Did you figure it out?
All right, let's take a look at what's in the union query here. And if we look, you can see the IDs over here. We've got some duplicate IDs. There's a one and a one. There's a banana and coffee. They both have the same ID.
What's happening is this combo box is bound to an ID, whatever's in that zero column, that hidden column. That's the actual value of that box. So when we pick coffee, it's putting three in that box, but the first three is banana. See what's happening? So it's switching.
So we need to distinguish the meals from the fruits, or from the food items here from the fruits. I just picked fruits before as an example. We need to distinguish which one's which.
Normally, I'm all about using AutoNumbers and storing numbers as IDs. In this particular case, I'm going to put either an M or an F in front of that ID. So I'll have M1, F1, M3, F3, M4, and so on, because it's only being used here and it's only being used to select the unique value for this combo box. And after that, we're not keeping it anymore.
And that's a perfectly valid way to indicate which one is which, and it gives them unique values. Let's go back to the union query.
I hope you appreciated that I showed you the wrong way first and why that happened. And let's say meal ID is going to be M and meal ID, and the food ID is going to be F and the food ID.
And now I want to take a look at the values in here. I have unique rows. Same. All the Fs are there. All the Ms are there. And now each one's got its own identifier.
And if you want to be a purist, if you want, you can make, you know, you can take this instead. You can add a million to it. Whatever you want to do. This is fine for me. I think this works perfectly fine.
Plus, we also need a way to distinguish what happens when we pick the value in the combo box because when we add a food item, it's just going to add that one item using that add food item subroutine that we wrote before. If it's a meal item, now we've got to loop through all of those and add the food items from the meals.
And then we handle it differently too. And now we can just look at that letter and decide which one is which. Yeah, you could look at this description and find, pick the ones that have the word meal in. And that's another way you could do it. But then you have to make sure you never change that. This is a little more future-proof here.
All right. So now save changes there. And now if you go back into here and you pick Rick's coffee, it should stay Rick's coffee because the value in there is M3 or whatever it was.
All right. So as you just saw, the AutoNumbers weren't wrong. They were perfectly valid inside their own tables. The trouble started when we combined both lists and treated those numbers as though they were unique across the entire result.
Give each source its own prefix in the selector, and the combo box can tell those choices apart. This works with employees and customers, foods and meals, whatever. If you're building something similar, just make sure the code that uses the selected value also knows which table it belongs to. And we cover that in the fitness series.
All right. So there you go. That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time. Intro In this lesson, we will examine why a Microsoft Access combo box can display the wrong record when a union query combines tables with matching numeric IDs. I will show you how duplicate AutoNumber values from separate tables create collisions and how adding a source prefix, such as E15 or C15, creates a unique selector value so Access can identify the correct record. Quiz Q1. Why can a combo box display the wrong record when its row source is a union query from two different tables?
A. Two different tables can contain the same numeric ID, and the combo box cannot distinguish them if it is bound only to that ID
B. Union queries automatically renumber all records incorrectly
C. Combo boxes cannot display records from more than one table
D. AutoNumber fields always create duplicate values within the same table
Q2. What does an AutoNumber field guarantee?
A. The number is unique across every table in the database
B. The number is unique only within its own table
C. The number is unique only in forms
D. The number identifies the record type automatically
Q3. In a combo box, what is usually the actual stored value when the first column contains an ID?
A. The visible description or name
B. The value from the bound column, often the hidden ID column
C. The table name
D. The query name
Q4. Why does adding a PersonType column, such as Employee or Customer, not by itself fix the problem?
A. Access ignores text columns in union queries
B. The combo box still has duplicate values if it remains bound only to the numeric ID
C. PersonType fields cannot be displayed in a combo box
D. The PersonType column changes AutoNumber values
Q5. What is a practical solution for making IDs unique in a combined combo box list?
A. Delete duplicate records from one of the source tables
B. Change both tables to use the same AutoNumber sequence
C. Prefix each ID with a source identifier, such as E15 for employees and C15 for customers
D. Store the person's name as the bound value
Q6. When creating selector values such as E15 and C15, what happens to the original EmployeeID and CustomerID fields?
A. They are converted from numbers to text
B. They are permanently changed to include prefixes
C. They are deleted and replaced with the selector values
D. They remain unchanged in their original tables
Q7. If VBA code uses a selected prefixed value such as M3 or F3, what should the code do?
A. Ignore the prefix and use only the number
B. Read the prefix to determine which table or process applies
C. Convert every value to an AutoNumber
D. Search all tables until it finds a matching name
Q8. In the foods and meals example, why did selecting Rick's coffee initially result in banana being shown?
A. The combo box was sorted alphabetically
B. Coffee and banana had the same numeric ID, and Access matched the first occurrence of that bound value
C. The food table was missing a primary key
D. The union query removed the coffee record
Q9. Why is a prefixed selector value useful beyond fixing the combo box display?
A. It can help later code determine whether the selection represents one food item or a meal containing multiple food items
B. It automatically creates relationships between all tables
C. It eliminates the need for primary keys
D. It allows Access to store records without a table
Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-D; 7-B; 8-B; 9-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone explains a common problem that can occur when you combine records from two different tables in a Microsoft Access combo box.
Suppose you have an Employees table and a Customers table. Each table has its own AutoNumber primary key field. That is perfectly normal. An employee might have EmployeeID 15, while a customer might have CustomerID 15. Those are two separate records in two separate tables, and there is nothing wrong with having the same number in both places.
The problem begins when you combine those tables with a UNION query and use the numeric ID by itself as the bound value of a combo box.
For example, imagine that EmployeeID 15 belongs to Alex Morgan, while CustomerID 15 belongs to Sam Rivera. When both records appear in a combined combo box list, Access can display both names correctly because the text values are different. However, if the bound column contains only the number 15, both selections have the same stored value.
That means Access cannot reliably tell which record you selected. If you choose Sam Rivera, the combo box stores 15. If you choose Alex Morgan, it also stores 15. When Access tries to display the selected value again, it may find the first matching record with ID 15 in the combo box row source. As a result, you may select a customer but see an employee displayed instead.
This is not a problem with AutoNumbers. AutoNumbers are unique within their own table. They are not intended to be globally unique across every table in your database. A UNION query combines records into one result set, but it does not create a shared key system between the source tables.
The solution is to create a temporary selector value that is unique across the combined list.
For example, I can place a prefix in front of each ID. Employee records can use an E prefix, such as E15, while customer records can use a C prefix, such as C15. Now Access sees E15 and C15 as two different values, even though both records originally used the number 15.
This does not change the EmployeeID or CustomerID fields in their original tables. Those fields can remain AutoNumbers, and they should remain numeric IDs. The prefixed value exists only in the UNION query and is used as the combo box selector value.
The combo box can still display a friendly name to the user. The hidden bound column contains the unique selector value, while visible columns can show the person's name, type, description, or any other useful information.
I ran into this same issue in my Fitness Database series when I combined foods and meals into one combo box. A food item and a meal could both have the same numeric ID. For example, a banana might have FoodID 3, and Rick's Coffee might have MealID 3. When the combo box was bound only to the number 3, selecting Rick's Coffee could cause Access to display Banana instead.
The reason was that the combo box stored the value 3 for both records. Since Banana appeared first in the list for that ID, Access displayed Banana when it found the value 3.
To fix that problem, I created unique selector values for each source. Food records used a prefix such as F3, and meal records used a prefix such as M3. Once each row had a unique bound value, the combo box could correctly distinguish foods from meals.
Using prefixes also provides an additional benefit when you need to process the selection later in VBA. If the selected value begins with F, I know it represents a food record. If it begins with M, I know it represents a meal record. My code can then handle each type appropriately.
For example, selecting a food may add a single food item, while selecting a meal may require the code to locate all of the foods associated with that meal and add each one. The prefix gives me a reliable way to determine which table and which process applies to the selected value.
You could use other methods to make the IDs unique, such as adding a large number to one set of IDs. However, a text prefix is usually easy to read, easy to debug, and useful later in VBA because it tells you where the record came from.
The important point is that when you combine multiple tables in a UNION query, you cannot assume that the primary key values from those tables will remain unique in the combined result. If the combo box binds to those IDs, you must create a unique value for every row in the list.
Use a source prefix such as E15 and C15, F3 and M3, or another naming convention that makes sense for your database. Then make sure any VBA code that uses the selected value recognizes the prefix and works with the correct table.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic List Union queries combining records from tables
Duplicate IDs in combined combo box lists
Why combo boxes display the wrong selection
Using prefixes to create unique combo box values
Binding a combo box to a prefixed selector value
Distinguishing source tables from selected values Article When you combine records from two separate tables into one combo box in Microsoft Access, you can run into a confusing problem: you select one item, but the combo box displays another. This usually happens when both tables use their own AutoNumber ID fields and happen to contain the same numeric value.
For example, suppose you have an Employees table and a Customers table. Employee ID 15 might belong to Alex Morgan, while Customer ID 15 belongs to Sam Rivera. There is nothing wrong with this. AutoNumber values only need to be unique within their own table. Access does not guarantee that Employee ID 15 and Customer ID 15 refer to different values, because they come from different ID systems.
The problem appears when you create a union query that combines employees and customers into one list. The query may display both Alex Morgan and Sam Rivera correctly because their names are different. However, if the combo box is bound to the numeric ID alone, both records have the same stored value: 15.
A combo box normally has a hidden bound column that stores the selected value. If that value is only the number 15, Access cannot tell whether the user selected Employee 15 or Customer 15. When Access refreshes the combo box, it may locate the first row with ID 15 and display that row instead of the one the user originally chose.
The solution is to create a selector value that is unique across the entire union query. Do not change the actual EmployeeID or CustomerID fields in their tables. Instead, create a calculated field in the union query specifically for the combo box.
For employee records, prefix the ID with a letter such as E. Employee ID 15 becomes E15. For customer records, prefix the ID with C. Customer ID 15 becomes C15.
Now the combo box has two distinct values. E15 identifies the employee record, and C15 identifies the customer record. Even though both records still have the original numeric ID of 15, the combo box can reliably distinguish between them.
Your union query should return a first field that contains this unique selector value, followed by the fields you want the user to see, such as the person's name and a description of the record type. The combo box should use the selector field as its bound column. You can hide that column from the user and display only the name or description.
For example, the visible list might show Alex Morgan and Sam Rivera, while the hidden stored values are E15 and C15. When the user selects Sam Rivera, the combo box stores C15. When the user selects Alex Morgan, it stores E15. Because the values are no longer duplicates, Access will display the correct selection.
This technique is useful any time you combine records from different tables into one selector. You might combine foods and meals, products and services, contacts and vendors, employees and contractors, or any other records that use separate ID fields.
If you later need to process the selected value, your logic should examine the prefix first. The prefix tells you which table the record came from, and the remaining numeric portion tells you which record to retrieve. For example, a value beginning with E should be handled as an employee, while a value beginning with C should be handled as a customer.
The important point is that AutoNumber fields are not the problem. They work correctly within their respective tables. The issue is treating IDs from separate tables as though they belong to one shared numbering system. By adding a simple source prefix for the combo box selector value, you give every row in the combined list a unique identity and prevent Access from selecting or displaying the wrong record. Primary Topics Access combo boxes, union queries, duplicate IDs across tables, bound column behavior, unique selector values, concatenated prefixes Secondary Topics AutoNumber scope, hidden combo box columns, relational lookup values, VBA handling of selected prefixes |
