Fitness 42
By Richard Rost
7 months ago
Solve Duplicate ID Problem in Meals/Foods Combo Box In this Microsoft Access tutorial I will show you how to fix the issue with duplicate IDs in a combo box that appears after merging two tables with a union query in a fitness database. You will learn why duplicate IDs cause problems and how to create unique identifiers to distinguish food items from meals, along with updating your VBA code to handle selections properly. This is part 42. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, fitness database, duplicate IDs, combo box, union query, merge tables, unique identifier, prefix IDs, add food item to log, meal vs food item, parsing string ID, distinguishing record types, subroutine, conditional logic, recordset loop
Subscribe to Fitness 42
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access fitness database in part 42 by tackling the problem of duplicate IDs appearing in a combo box after merging two tables with a union query. I'll show you how to identify the issue, demonstrate a common mistake that causes incorrect selections, and walk you through the process of prefixing IDs to ensure uniqueness for each item. We'll then update the combo box and related VBA code to handle these new string IDs, allowing the system to properly process food and meal items based on their identifiers. This is part 42.Transcript Today's part 42 of my fitness database. And again, it's a database with lots of cool stuff in it. It doesn't matter if you care about fitness or not. Today we're going to solve the problem of having duplicate IDs in our combo box because we merged two other tables together. I'll show you how to fix that. That's what's covered today.
If you haven't watched parts 1 through 41, you're missing out on all kinds of cool stuff. Go watch those first, then come on back.
And we're back. Did you miss me? I missed you guys.
So in the last video, we built our union query. We have our food items and our meals in the same query, so we can feed the same combo box.
Now the next task is to get this into that combo box. I'm going to admit, I'm going to show you the wrong way to do this first because this is a mistake that I see a lot of people make when it comes to union queries. Some of you who have already been through a bunch of union query hell might already recognize what the problem is going to be.
I'm not going to spoil it though. I'm going to show you what happens so I can teach you the right way to deal with it. You get a better appreciation for it. Some people are like, well, no, just get to it and show me the right way. No, I'm going to show you the mistake first because then you'll appreciate it.
Now this comes together. This is meal union food queue. Remember that. In fact, I'm going to put that on my clipboard. I'm going to right-click on this, go to rename, and just copy it to my clipboard. That's a trick I do once in a while.
So in the log, now this guy gets its row source from code when the form opens. So we're just going to get it out of here. This doesn't even have to be here. It's going to get rewritten when the form loads.
So we're going to go in here and we're going to find just like the filter in the order by that gets rewritten. This stuff is kind of sticky though. It keeps coming back. That's okay.
Let's find the on load event. Bring this over here. We don't need you for now. Sorry. Just got to do a little resizing here.
So it's the update food combo row source, this guy. We just need to update this. Let's see. This becomes description, the fields we changed last time. This becomes ID and description from paste and then we're going to order by description. Save it.
Debug compile. Everything should compile fine.
Let's close it. Close it. Open her up. Now if we drop this down, there we go. Perfect. My fruits. There's my fruits and our meals and everything looks great.
Let's pick a fruit. Pick a fruit. Pick a banana. That works fabulous. I can add it. Let's pick a barebells. I can add that.
Let's pick a meal. Let's see. Where's a meal in here? Let's pick this one. Rick's standard breakfast meal. That worked.
Let's pick Rick's coffee. Oh, wait a minute. I went to banana. Let's try. Wait. That's a bug. Let's try that again. Let's pick Rick's coffee again. C-O-F-F, 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. I'll tell you the problem in a minute and then what the solution is.
Did you figure it out? Let's take a look at what's in the union query here. If we look, you can see the IDs over here. We've got some duplicate IDs. There's a one and a one. 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. The first three is banana. See what's happening? So it switches.
We need to distinguish the meals from the food items here. Previously I just picked fruits as an example. We need to distinguish which ones are 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. M1, F1, M3, F3, M4, and so on. Because it's only being used here and it's only being used to select a unique value for this combo box. After that, we're not keeping it anymore. That's a perfectly valid way to indicate which one is which and it gives them unique values.
So let's go back to the union query. I hope you appreciated it. I showed you the wrong way first and why that happened.
Let's say meal ID is going to be M and meal ID. The food ID is going to be F and the food ID. Now I want to take a look at the values in here. I have unique rows. All the Fs are there, all the Ms are there, and now each one's got its own identifier.
If you want to be a purist, if you want, you can take this instead and 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. When we add a food item, it's just going to add that one item using that add food item subroutine 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 meal. So we have to handle it differently too.
Now we can just look at that letter and decide which one is which. You could look at this description and pick the ones that have the word meal in them, 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.
Save changes there. 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. If I add it, nothing happens because we have to fix that part of the code now too - add food item to log.
So now we've got past the combo box not working problem. Now this add food item to log is perfect just the way it is. I'm not going to mess with it. If you look at it, right, it takes an ID and then it adds on. I mean, we're going to make some minor modifications, but basically this works just fine.
So what we're going to do is we're going to add another subroutine first that's going to look at the item in the box. It's going to say, are you a meal or a food item? If you're a food item, I'm just going to send you right to add food item to log. If you're a meal item, now I've got to go to a different subroutine and loop through them and send each of those individual items to add food item to log - works the same way. One just has to loop through the record set.
So let's go back up to here, the add food button click. Instead of add food item to log here, let's just change this to add, oops, someone's beaming in, add to log. So we're going to add to log food combo.
Now what's add to log? Well, we have to write it. So let's come up here.
Private sub or public, whatever you want. I don't think I'll ever be calling this from another form, but in case you decide to, that's okay too.
Add to log. Now, this is weird, but it's going to take an ID as a string. That's okay. Your ID can be whatever you want it to be. Usually it's an autonumber, but in this case, it's a string.
Now I'm going to pull two bits of information out of that ID. I want the item type, whether it's an M or an F. Then I want the native ID, whatever that ID was for either the food item or meal item.
So: dim item type as a string. This will be either an M or an F. Then we're going to dim the native ID as a long. That's going to be either the meal ID or the food ID.
Now, it's easy to get. This is one of those cases where we're building this ourselves, so there's no possibility of the user sending some weird stuff in there. We don't have to worry about them breaking it.
The item type is going to be the left of the ID, 1 - that left 1 character. The native ID is everything else. It's going to be the right of the ID, the length of the ID minus one. So if it's five characters, it's going to be five minus one, the right four characters. Then Access should stuff that properly into a long, but I'm going to force it with a CLNG convert to long.
Access is really good about automatic type conversions. If something is in a string and you're trying to stuff it into a long, if it is numeric, you can use is numeric to test that. Again, this is a situation where we're providing it the data - the user doesn't have the ability to type this in. So I'm not too worried about it.
Now we're going to say if item type is an F, then add food item to log that native ID. That's a food item. Otherwise, it's a meal item. For now, we're just going to message box "meal." You could put in here "food item." And you could put in here "meal item."
As of right now, food should still be working the same way it was before. If they do pick a meal, it's going to say, oh, we got a meal. That's all we've done so far. We haven't written the meal part of it yet.
Save this. Debug compile. I want to make sure it - whoops, add to log. I don't know what I want to do. And do long. Wrong. See, that's why we debug compile once in a while. Catch the stupid errors. It catches the stupid errors. It doesn't catch the logic errors, which are the ones you really tear your hair out on.
Come back here. Close it. Open it. Add a food item. Still working. Works great. And protein marks.
Let's add a meal item. Let's add a Rick's coffee. And we got a meal. Everything is working perfectly.
Now we just need to write what happens if we do this. And guess what? We'll talk about that in tomorrow's video. So tune in tomorrow. Same bat time. Same bat channel.
That's going to do it for part 42. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. See you next time.
TOPICS:
Identifying duplicate IDs in a union query
Understanding the combo box ID binding issue
Debugging incorrect combo box selections
Distinguishing data sources in a combo box
Modifying union queries to create unique IDs
Prefixing IDs with letters for uniqueness
Updating combo box row source to use new IDs
Adjusting code to handle new string IDs
Parsing prefixed string IDs in VBA
Determining item type based on ID prefix
Conditionally calling subroutines based on item type
Testing the solution for correct selection and logging
COMMERCIAL:
In today's video, we're continuing with Part 42 of the fitness database project. We'll learn how to fix the issue of duplicate IDs showing up in a combo box after merging two tables with a union query. First, I'll show you the common mistake people make with union queries and why it causes problems, then I'll walk you through the correct way to create unique IDs by adding an identifier to each record. We'll also update the VBA code to handle these new unique IDs and set up logic to process food and meal items separately. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why did the combo box start having issues after merging two tables using a union query?
A. The fields in both tables did not match.
B. The combo box could not display text from both tables.
C. The IDs from both tables could be duplicated, causing ambiguous selections.
D. The union query cannot be used as a row source for combo boxes.
Q2. What was the specific problem observed in the combo box after using the union query?
A. The combo box failed to display any values.
B. Selecting one item caused a different item, with the same ID, to be selected.
C. The combo box showed all records as blank.
D. The combo box duplicated every entry.
Q3. What approach was suggested to provide unique identifiers for each combo box entry from the union query?
A. Adding a random number to each ID.
B. Prefixing IDs with a distinguishing letter (M for meal, F for food).
C. Changing all IDs to globally unique identifiers (GUIDs).
D. Sorting the IDs alphabetically.
Q4. Why is prefixing each ID with an M or F an acceptable solution in this context?
A. The prefix allows sorting of meals and foods together.
B. The combo box only uses the ID for selection and does not need to store it permanently.
C. The prefix is required by Access combo boxes.
D. The approach replaces the need for an autonumber field.
Q5. After assigning prefixed IDs, what coding strategy was proposed for handling combo box selections?
A. Ignore the prefix and treat all IDs the same.
B. Use the prefix to decide whether to add a single food item or process a meal's items in a loop.
C. Automatically generate a new table for processed meals.
D. Sort combo box entries by prefix.
Q6. When processing a combo box selection, how does the code distinguish between a meal and a food item?
A. By the length of the item description.
B. By reading the prefix character in the ID (M or F).
C. By the value of a hidden field in the form.
D. By using Access system tables.
Q7. What is the initial step in breaking down the prefixed ID string in the VBA code?
A. Use the Right function to get all characters.
B. Use the Left function to get the prefix and the Right function to get the numeric part.
C. Use the Mid function to extract the entire ID.
D. Use the Len function to find the prefix.
Q8. Why is it safe to extract the prefix and numeric part directly from the ID string in this context?
A. Because the prefix is always three characters.
B. The user cannot manually input the data, so the data format is controlled by the code.
C. Because errors in conversion will be ignored.
D. Because Access always validates user input.
Q9. What would happen if, after separating the union query, IDs were still not unique in the combo box?
A. The combo box would display an error and not open.
B. Selecting an entry could result in adding or displaying the wrong item.
C. The combo box would simply hide duplicate entries.
D. Items with duplicate IDs would merge their descriptions.
Q10. Besides using a prefix, what is an alternative suggested to ensure unique IDs if you want to be a purist?
A. Add 100,000 to one set of IDs to separate them.
B. Use complex lookup fields.
C. Only show one table in the combo box.
D. Use zero as the prefix for all IDs.
Q11. What should happen when a meal is selected from the combo box, after implementing the ID prefix strategy?
A. Add the meal itself as a food item to the log.
B. Loop through all items in the meal and add each food item to the log.
C. Display a message that meals cannot be added.
D. Prevent the user from selecting meals.
Q12. Why is using the description field (to check for 'meal') considered a less future-proof solution than using a prefix?
A. Descriptions are case sensitive.
B. Descriptions may change over time, while the prefix remains consistent.
C. Description fields are not available in combo boxes.
D. Prefixes are required by database standards.
Answers: 1-C; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-A; 11-B; 12-B
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 TechHelp tutorial from Access Learning Zone addresses a common challenge that occurs when merging data from multiple tables into a single combo box—specifically, handling duplicate IDs after creating a union query.
If you have not watched parts 1 through 41 in this series, I strongly recommend viewing them first to get the full background and understand all the concepts leading up to this point.
Previously, I created a union query that combined both food items and meals so that they could be fed into the same combo box. The next step was to set this query as the row source for our combo box when the form loads. I want to point out a mistake that many developers make at this stage, especially when working with union queries. Rather than jumping straight to the solution, I believe it's valuable to see what happens if you overlook this step—this way, you will gain a greater appreciation for why the solution works.
After connecting the union query to the combo box, everything initially appeared to function as expected: the food items and meals were listed together, and selecting an item allowed me to add it to the log. However, an issue quickly surfaced when I noticed that selecting certain meals in the combo box would cause unexpected results, such as switching to a completely different item. At first glance, it seemed like a bug.
To diagnose the problem, I looked more closely at the underlying data in the union query and spotted duplicated IDs—for example, both banana and coffee shared the same ID value of 1. The combo box stores the selected value using the first column, which was ID. So, if two items (a meal and a food item) shared the same ID, selecting one could inadvertently reference the other—clearly not what we want.
To solve this, I needed a way to distinguish between food and meal IDs so that each entry in the combo box would be unique. In this case, I chose to prefix each food ID with an 'F' and each meal ID with an 'M', resulting in IDs such as F1, M1, F3, M3, and so on. This approach works well here because these values are only used for selection purposes within this combo box; they are not stored in the database for any other use.
With this change, each combo box value is now distinct, and the display works as expected. When you select Rick's coffee, for example, the combo box holds 'M3', which is unique compared to 'F3' for a different item.
This distinction also makes it easy to programmatically decide what to do when the user selects an item. If the item is a food, we simply add it using an existing subroutine. If it's a meal, we need to handle it differently by looping through the meal's items and adding each one individually.
In the code, I created a new procedure that checks the selected combo box value, extracts whether the entry is a meal or a food item by looking at the prefix, and then determines which ID it is. If the prefix is 'F', the code proceeds with the regular function to add the food item to the log. If it's an 'M', for now, I just have it display a message indicating it's a meal—this is a placeholder for the next step, where we'll write the part of the code that handles logging all items in the meal.
This technique of prefixing IDs is a simple and effective way to manage merged table data in a union query combo box where duplicate IDs could otherwise cause conflicts. It is also forward-compatible and future-proof, since it doesn't rely on item names or descriptions remaining unchanged to distinguish items.
At this stage, adding food items functions as it always has, and selecting a meal now correctly identifies it as such. Writing the actual logic for adding an entire meal's items is a task I will cover in the next part of the series.
As always, 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 Identifying duplicate IDs in a union query
Understanding the combo box ID binding issue
Debugging incorrect combo box selections
Distinguishing data sources in a combo box
Modifying union queries to create unique IDs
Prefixing IDs with letters for uniqueness
Updating combo box row source to use new IDs
Adjusting code to handle new string IDs
Parsing prefixed string IDs in VBA
Determining item type based on ID prefix
Conditionally calling subroutines based on item type
Testing the solution for correct selection and loggingArticle In Microsoft Access, when you combine tables using a UNION query to provide data for a combo box, you may encounter a common problem: duplicate IDs. This usually happens after merging two tables that both use autonumber IDs starting from the same value. For example, you might have a Foods table and a Meals table with IDs 1, 2, 3, etc. If you merge these, both tables could have a Banana with ID 1 and a Coffee meal with ID 1, creating confusion in your combo box because Access will see bananas and coffee as having the same key.
Let's walk through how to resolve this issue and ensure your combo box functions correctly, even after combining tables with overlapping IDs.
First, let's look at the typical setup. If your combo box's RowSource is set to your merged UNION query, you might write something like:
SELECT ID, Description FROM [MealUnionFoodQ] ORDER BY Description;
Initially, this may seem to work fine. Your combo box will show all food items and meals combined. However, when you pick an item and Access processes the selection, it uses the bound column, in this case, the ID. If multiple items have the same ID, Access cannot distinguish between them. For example, if both Banana (in foods) and Coffee (in meals) are ID 1, and you pick Coffee, Access might actually select Banana or switch selections unexpectedly.
To fix this, each entry in the combo box must have a truly unique identifier. One practical way is to prefix each ID with a letter representing its source table: for example, "M1" for a meal with ID 1 and "F1" for a food with ID 1. Here's how you modify your UNION query:
SELECT
"F" & [FoodID] AS UID, [Description]
FROM
Foods
UNION
SELECT
"M" & [MealID] AS UID, [Description]
FROM
Meals
ORDER BY Description;
Now, your combo box will have unique values like F1, F2, M1, M2, and so on. Whenever you make a selection, the value will unmistakably refer to exactly one record, avoiding any ambiguity.
When handling the user's selection in VBA, you will need to parse out the prefix to determine whether the item is a meal or a food. Here's how you might write that subroutine:
Private Sub AddToLog(strID As String)
Dim itemType As String
Dim nativeID As Long
itemType = Left(strID, 1) ' First character: "F" or "M"
nativeID = CLng(Mid(strID, 2)) ' Remaining characters: the number part
If itemType = "F" Then
' This is a food item, call your existing code:
AddFoodItemToLog nativeID
ElseIf itemType = "M" Then
' This is a meal; for now just a placeholder:
MsgBox "Meal item selected"
' Later: expand this to add all food items within the selected meal
End If
End Sub
With this setup, when the user picks something from the combo box, the AddToLog routine will look at the identifier. If it starts with an "F," it treats it like a food item and processes it as before. If it is an "M," it recognizes that a meal was selected. Eventually, you would expand the meal branch to look up all component food items in that meal and process each in turn.
To connect this handler to your combo box command button, you would change your click event (or whichever event you use to process the selection) to call AddToLog and pass it the combo box value:
Call AddToLog(Me.cboFood.Value)
Be sure to match cboFood to your combo box's actual name.
Let's review why this method works. By creating unique IDs in your UNION query, you ensure the bound values in your combo box remain unique, regardless of overlapping autonumbers in the source tables. When processing, you can easily and reliably distinguish between the types by reading the ID's prefix. This method keeps your database robust and prevents accidental record confusion when users switch between food and meal entries.
In summary, if you are populating a combo box from a UNION of two tables with their own autonumber IDs, always create a unique identifier for each source by adding a distinguishing prefix or offset, and adjust your VBA routines to interpret that identifier. With these changes, your combo box will always track the correct selection, and your form will handle both types seamlessly. |
