|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Multi Field Combo By Richard Rost 
5 years ago
Display Multiple Fields in a Closed Combo Box In this Fast Tips video, I'll show you how to display multiple fields in your Microsoft Access combo boxes, even when the box is closed. You can use String Concatenation or DLookup. Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, multiple fields, combo box, closed combo box, display multiple fields, first name, last name
IntroIn this video, I will show you how to display multiple fields in a combo box in Microsoft Access, even when the box is closed. We will look at using string concatenation in a query to combine first and last names, setting up the combo box to use this new query, and making sure your combo box always shows the information you want. I will also cover how to use the DLookup function to display additional related information, like a customer's credit limit, based on the current combo box selection, and how to handle null values with the NZ function.TranscriptWelcome to another Fast Tip video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this video, I am going to show you how you can display multiple fields in your combo boxes even when they are closed in Microsoft Access. This is a question I get asked all the time, so it is time for a Fast Tip video. I have first name and last name as separate fields in my customer form, as they should be. Now, on my order form, I want to put a combo box right here so I can pick the customer. I go to Design View, pick Combo Box from this list, and put it right there. I am going to look up the values from the table or query, and if you have never done this before, go watch my video on relational combo boxes. I will put a link down in the link section below the video. We will get our data from customer T. Next, I will bring over the customer ID because we need that for the ID for the bound column. I will bring over last name and then first name if I want them in that order. Next, drop this down. I am going to sort by last name, then first name. That is our sort. Next, this is what the fields will look like when the box is open. Next, store that value in my customer ID on my order. Next, give it a label, customer, and then finish. I'll use the format painter to copy the format from the guy above it like that, so I get the colors and all that right, and then I'll slide these boxes into place. Now, when I save this, close it, and then open it back up again, I only see last name. If I drop the box down, I can see last name, first name. When I pick a customer and close it, you only see one field in the combo box. That is by design, unfortunately. In order to do this, in order to show both fields in there, we have to use something called string concatenation to put those together into one field, and I will use a query to do that. Now, I have a whole separate video on string concatenation. Go watch that. Again, I will put a link down in the link section. Watch that video if you want to learn more. I am going to close this form. Now I have already built this query before. Here it is right here, customer LFQ. If you open that up, you can see there is a field called LF. It has got last name, comma, first name in it. How did I do that? Let's go to Design View. I came over here into this blank column, I created a new column, and I am going to zoom in so you can see that. It is LF colon, last name, ampersand, and then quote, comma, space, quote, and then a first name. It takes last name and first name, puts a comma space between them, and then calls that LF. So there we got a new column now called LF, a new field in the query, a calculated query field. It looks like that. We can use this guy in the combo box. So let's go back into here. Design View. I am going to delete the one I created a minute ago, and let's create it in one. Ready? Combo box? Drop it there. Look up the values in table or query. This time go to queries. Pick customer LFQ. Next, bring over the customer ID and the LF. Ignore last name, we do not need that. Sort it by LF. Next. Now, since this is based on a query, you do not see that little check box up here that says hide the key column. So we have to just take this, shrink it down to zero so the width is nothing. As you can see now, that is showing up in one field. Hit Next, select the bound column. That is the customer ID. Next, store that value in the customer ID in the order. Next, and then give it a label, customer, and then finish. One more time, I will grab the format from up top here and paste it over that one. Use the little format painter so it looks nice. Then I will resize this guy like this. Save it. Close it. Open it back up again. There we go. Now you can see both last name and first name in the combo box even when it's closed. Could be treated as one field. The second option is to use something called DLookup. It is a function you can use to look up a value from a table or query. Now let's say we have already got this and I want to see one more thing over here. Maybe I want to see the customer's credit limit, have that displayed right here after I pick the customer. We can use DLookup for that. So how do we do that? Design View. Grab a text box. Drop it over here. I will just delete the label and slide this over here. I like to make things that you can't edit gray because if you use DLookup, you can't edit this value. It is just for display only. If you want to edit this value, you would have to go back to the customer table to do it. Now we have to get the value from the customer table to put in this box. We are going to grab the credit limit field, which is a field over here, credit limit. We are going to need to look up the customer ID, but customer ID happens to be the bound column in this combo box. Let's give this combo box a good name first. Right now it is combo18. Let's call it customer combo. You can call it customer ID if you want to. I like to name my combo boxes whatever combo. So let's open this up. We can call this guy credit limit. Then in the control source, I'm going to zoom in, Shift F2. I am going to say equals DLookup. Then in parentheses, inside of quotes, what are we looking up? The credit limit. It is customer ID from what table? Customer T, or you can look it up from queries too. Comma, what is our criteria? Where the customer ID (that is the field from the table) equals, and then ampersand customer combo. That is going to use string concatenation there and it is going to actually put the value that is in the customer combo box inside this string. So it will be customer ID equals 1. Again, string concatenation. Go watch that video. It is a very good video. Also, I have another video just on the DLookup function. You can go watch that too. I will put links down below in the links section. Hit OK. Close that. Save it. Close it. Now, when I open this up, I get an error message. That error message is because there is no value in here. I have got no order for this. I changed the order for myself earlier. So let me go back on the order table here and make an order for me. Now I should get the value. There it is. If I change this to somebody else, you will see the value in there updates. You can format that as a currency if you want to. Right click, Design View. Open this up, and then under format, put currency in there. Save it. Then when you come back in, there you go. There is the currency value. Now on a new order, if you want to avoid seeing error there because there is no customer in there, you can put NZ in here. The NZ function, you can wrap DLookup inside of NZ. Watch this. You can go right here, NZ. That will say if the customer combo is null, if there is no value in it, put a zero here. Comma zero. Again, I have videos on NZ. I will put a link down below. That says null to zero. Hit OK. Close that. Close that. Save changes. Now open it up, go to a blank new one, and at least it will see blank in there. Now as soon as you create a new order for somebody, you will see their credit limit pops in there. You can do that with any field you want. DLookup is a very powerful function. It has got cousins, DMax, DSum, DCount. There are all kinds of different domain aggregate functions, and I cover them all in my lessons. Definitely go watch my DLookup lesson. There are lots of different ways to use it down here. Watch my string concatenation video; that is good. I have a video on the NZ function. These are all free videos on my website, by the way. Of course, if you are new to Access, come to my website, check out my free four-hour long Access Beginner Level 1 course. I know it says Level 1. If you have got some Access experience under your belt, a lot of people are like, I do not need it, I do not need the basics. No, I cover a lot of good, fundamental stuff in this class. That is why it is four hours long. Check it out. Be sure to like and share this video, give me a thumbs up, make sure you subscribe to my channel if you have not already. On behalf of Access Learning Zone, this is Richard Rost. I will see you again soon. QuizQ1. Why does the combo box on the order form initially only show one field when closed, even after adding first and last name fields in its setup?A. Only one field is allowed in combo boxes in Access B. This is a default setting that cannot be changed C. This is by design, and you need to use string concatenation to display both fields D. Access cannot handle more than one field per combo box under any circumstances Q2. What must you use to display both the last name and first name in the closed combo box? A. Add both fields directly in the combo box wizard B. Rearrange the field order in the wizard C. String concatenation in a query D. Change the combo box style to multi-field Q3. How do you create a new column in a query that combines last name and first name? A. Use an Update Query B. Use string concatenation with an ampersand (&) in a calculated field C. Use the Format function in the combo box D. Simply select both fields in the query Q4. When using a query for the combo box row source, why do you set the key column width to zero? A. To hide the key column so only the concatenated names display B. To make the combo box look smaller C. Because the key column is not needed anymore D. To change the sort order Q5. What function should you use in a calculated control to look up a value (such as credit limit) from a table based on a selected combo box value? A. Sum B. Format C. DLookup D. Choose Q6. Why is it suggested to use the NZ function with DLookup in the credit limit text box? A. To convert null values to zero and avoid error messages B. To automatically round the number C. To convert numbers to text D. To check for duplicates Q7. What is the benefit of using a calculated query field (like LF: [LastName] & ", " & [FirstName]) for combo box display? A. It reduces form loading time B. It allows displaying multiple fields as a single value in the closed combo box C. It sorts the records automatically D. It enables multi-select in the combo box Q8. Why does Richard recommend making the DLookup text box control gray? A. It indicates to the user that the value cannot be edited directly B. It saves ink on printing C. All controls should be gray D. It relates to formatting currency values Q9. If you want to format a numeric control to display as currency in Access, what should you do? A. Change the control source B. Set the format property to Currency C. Use the DSum function D. Sort the form by that field Q10. Which of the following is NOT a domain aggregate function similar to DLookup mentioned in the video? A. DMax B. DSum C. DCount D. DFilter Answers: 1-C; 2-C; 3-B; 4-A; 5-C; 6-A; 7-B; 8-A; 9-B; 10-D 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. SummaryToday's video from Access Learning Zone focuses on how to display multiple fields in a combo box in Microsoft Access, even when the box is closed. This is a common question I receive, so I wanted to share a quick solution.In my customer form, I keep first name and last name as separate fields, which is a good practice. However, on the order form, I want to add a combo box to select a customer. To start, I switch to design view, add a combo box to the form, and set it to look up values from my customer table. I include the customer ID in the combo box because that will be the bound column, and then I add last name and first name, in that order. I set the combo box to sort by last name and then first name. At this point, when the combo box is open, it displays last name and first name in separate columns. However, once the combo box is closed, only the first visible field—typically the last name—appears. This is intentional in Access and the default behavior. If you want to show both last name and first name together when the combo box is closed, you need to use string concatenation to combine those fields. I recommend doing this in a query. I have a separate video on string concatenation that goes into more detail if you need a refresher. In this query, I create a calculated field that joins last name and first name with a comma and a space between them. For example, the field might be called LF and contain something like "Smith, John." With this new query in place, I go back to the order form and replace the original combo box with a new one. This time, I pull the customer ID and the concatenated LF field from the query. I set the combo box to sort by LF and make sure to hide the key column by adjusting its width to zero. Now, when I use this combo box, both the last name and first name display together as one field even when the box is closed. There is another technique I want to demonstrate, which is using the DLookup function. DLookup lets you retrieve a field's value from a table or query based on some criteria. Let's say I want to display the selected customer's credit limit next to the combo box. I add a text box to my form and set its control source to use the DLookup function. The control source tells Access to look up the credit limit from the customer table where the customer ID matches the one selected in the combo box. I like to name my controls with clear names, so I rename the combo box to something like "customerCombo." Since values in the DLookup text box cannot be edited by the user, I suggest shading it gray for clarity. Once set up, changing the customer in the combo box updates the credit limit automatically. You can also format this value as currency from the property sheet. If you notice an error appearing in the box, this usually means there is no value to display, such as on a new order with no selected customer. To avoid this, you can wrap the DLookup in the NZ function. This function replaces null or missing values with zero, or any value you prefer. I also have separate videos on the DLookup and NZ functions if you want to learn about them in more detail. DLookup is a useful tool, but Access also provides related functions like DMax, DSum, and DCount for other types of aggregate calculations. All of these are covered in my lessons. If you are just getting started with Access, I recommend my free, four-hour Access Beginner Level 1 course available on my website. Even if you already know the basics, this course covers foundational concepts that are important for all Access users. 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 ListCreating a multi-field combo box in AccessAdding a combo box to a form in Design View Selecting fields for a combo box data source Sorting combo box dropdown by last name and first name Understanding combo box display limitations Using string concatenation in a query to merge fields Building a calculated field combining last name and first name Setting up a combo box based on a query with concatenated fields Adjusting column widths to hide key columns in combo boxes Using DLookup to display related values from another table Creating a read-only text box to display lookup values Writing a DLookup expression referencing a combo box value Formatting displayed values as currency in a text box Handling null values using the NZ function in Access Wrapping DLookup with NZ to avoid errors on blank records |
||||
|
| |||
| Keywords: FastTips Access multiple fields, combo box, closed combo box, display multiple fields, first name, last name display two fields in combo box, see two fields in combo box, combo box display second column PermaLink Multi Field Combo in Microsoft Access |
