|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Last Name, First By Richard Rost 
5 years ago
Display LastName, FirstName in Combo Boxes Do you know how combo boxes will only show ONE field when the box is closed. If you have first name and last name for a customer and you want to see them both in the box when it's closed, you have to know this trick. Olivia from Newark, New Jersey (a Gold Member) asks: I want to make a combo box to select a customer, but the box will only show first name OR last name when it's closed, not both. How can I see them both together? Also, I've got a bunch of Smiths and even a few John Smiths. How can I tell them apart when selecting? MembersI'll show you how to add a Company Name to the mix. This way you'll see Company (Last, First) and we'll deal with all of the combinations of missing data.
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! LinksString Concatenation: https://599cd.com/Concatenation
IntroIn this video, I will show you how to set up a combo box in Microsoft Access that displays last name and first name together in the format "Last Name, First Name" when the box is closed. We will use string concatenation in a query to combine the fields, add helpful extra information like phone number or state for better identification, and configure the combo box so you can easily distinguish between customers with similar names.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to make your combo boxes show last name and first name - last name, comma, first name. In the box, when the box is closed, a lot of people say that they can only show one field at a time in that box. I am going to show you how to put them together. Today's question comes from Olivia from Newark, New Jersey, one of my gold members. This says: I want to make a combo box to select a customer, but the box will only show first name or last name when it's closed, not both. How can I see them both together? Also, I've got a bunch of Smiths and even a few John Smiths. How can I tell them apart when selecting? By default, the way that a combo box works is you put two fields in the combo box. It's usually hidden and that's your ID. The second one is the value that will be visible when the combo box is closed. You can easily have multiple fields in the combo box, like first name and last name, but you'll only see them when the box is open, like a list box. Let me show you first how to put them together with a little string concatenation, and then I'll show you how to also display the ID as well, even if you don't want to see it when the box is closed. This lesson has two prerequisites: relational combo boxes (that's making a combo box off of the values in a different table or query) and string concatenation (that's putting two different strings together). If you're not familiar with either of these terms, go watch both of those videos first. I'll put links down below in the link section. Here I am in the TechHelp Free Template. This is a free download from my website. Again, I'll put a link down below. You'll find in this template I've already done what you're asking for. I did it in my invoicing lesson. If you open up a customer, for example, and go to his orders, you'll see right there is a combo box that does exactly what you want. It's got first name and last name together with a little bit of string concatenation. But just so you don't have to sit through an hour of video for this tip, let me show you exactly how I did it. The key to making it so that both of those fields are together in the combo box is to concatenate them into one field in a query. Go to Create, Query Design, and then in your query, bring in the Customer table. You can close the field list there. Inside the Customer table, bring in the customer ID. We'll need that as the first field - that's going to be for our relationships. Down below here, I'm going to create a calculated field and put it right here. Let's make a field called LF, which will be last name and first name, colon, last name, and quote, comma, space, quote, and first name. Just like that. I'll zoom in so you can get a good look at it. I'm going to save this as my customer Q, my Customer Query. If I run this query now, there are all my customers - last name first. That's one field. I can't edit this, but I can use this to display it in places like on reports or different forms or combo boxes. In fact, you may even want the reverse, too. You may want to make a second one over here called FL, like that. Then you can say first name and a space and last name. You might want to put those together in other places, like in reports. You don't want the gap there between the first and the last name if you put together an address label, for example. The key is to use this in your combo box. If you have other information that might help you - you mentioned that you have multiple John Smiths - maybe throw their address in there or their email address, or even use the customer ID if you want to. Whatever other bit of information in here, like phone number, that you want to use when you open up that box to let you pick which one it is - maybe phone number and state. Whatever fields you feel are good for identifying this person or their address. Save that. Now let's go back over to that order form. You can do this on any form you want. Go to Forms, Orders over here. I took the combo box that I had before off of here. Let's put it back on. Design View. Go to combo boxes up here. There's combo boxes. Drop it there. The wizard is going to start up. I want the combo box to get the value from another table or query. Queries. You're going to pick Queries - that customer Q that we just created - next. These are the fields in that query. Customer ID should go first. That's what's bound to the field in the order table. When I pick a customer, it's going to save the customer ID in whatever field you want in the form below. The second thing you put in there is what do I see when the box is closed. That will be, in this case, last name first. I want to see Kirk, comma Jim, for example. Whatever other helper fields you want can go after that. We don't need FL. Phone and state we can put next. We'll see those when we open the box up. Hit next. How do you want to sort it? Sort it by last name first. You can sort it by other fields if you want to put all the states together, for example. Next. Now this is what the fields are going to look like. If you base your combo box off of a table, you'll get a little check box here that says "Hide the key column." But since this is based on a query, we don't see that. We have to hide it manually. How do you hide it manually? Just grab the box right here and make its width zero like that. Just make it so you can't even see it anymore. Then resize these however you want. Hit next. Now which is the bound field? Which field are we storing in the table below? This form happens to be my order form, and I'm picking a customer. So, customer ID. That's going to get stored in the customer ID on the order form. That's why I wanted you to watch the relational combo boxes video first, so you understand that I'm picking a customer from the list of customers in the customer table and I'm storing that customer ID in the order table using the order form. That's an important concept. Next. What label do you want? "Customer" is fine. And then finish. There's my box. Of course, Access doesn't bring it in and make it look like the other ones automatically, so we have to change it up here. I'm just going to use the format painter. Format paint, drop it on there. Resize this guy. Resize it like that. Maybe select these guys, right-click, size to grid. Get them all lined up on the grid nicely. Let's close that form, save changes, yes. Let's open it back up again. There we go. There's my new combo box - last name first. You can see there's the name in the first column. The second column's got the phone number in it. The third column's got the state. You'll only see these when the box is open, but that will let you decide which one of those John Smiths to pick. There you go. That's it. That's how you do it. Very simple. Want to learn more? In the extended cut for members, I show you how to add company name as well. We'll make the query understand that you might have a company name with only a person's last name, a company name with no first or last name, a company name with just the first name, company name with first and last name, just the first name, just the last name, or a last name, first name with no company name. Those are all the different combinations. We'll cover that in the extended cut for members. How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. All of our members will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the main purpose of the video tutorial?A. To show how to create reports in Access B. To demonstrate how to concatenate last and first names in a combo box C. To explain how to filter queries in Access D. To design forms from scratch Q2. By default, what is usually the visible value in a closed combo box? A. The concatenated full name B. The ID field C. One visible field such as first name or last name D. The phone number Q3. What technique is used to display both last name and first name together in a combo box? A. Data validation B. String concatenation C. Macro programming D. Indexing Q4. In the example provided, what did Richard name the concatenated field with last name and first name? A. FL B. FullName C. LF D. NameConcat Q5. How can you handle situations where multiple customers have the same name? A. Ignore the duplicates B. Only display the last name C. Add other identifiers like address or phone number to the combo box D. Display a warning message Q6. Where should the concatenation of the fields occur to display them together in the combo box? A. Directly in the form's control source B. In a table C. In a query D. In a macro Q7. What is the purpose of bringing in the Customer ID as the first field when designing the query for the combo box? A. To display it in the reports B. To use it as a unique record identifier for relationships C. To show it to the end user D. To use it as a sorting field Q8. What do you need to do to hide the Customer ID column in the combo box when the box is based on a query? A. Delete the column B. Set its width to zero manually C. Change the bound field D. Lock the column Q9. What is the 'bound field' in the context of combo boxes? A. The field that displays when the box is open B. The field that stores the value in the underlying table C. The column used for sorting D. The primary key of the database Q10. Why might you want to create both LF (last name, first name) and FL (first name, last name) fields in your query? A. To allow for both display formats depending on where the value is used B. To save time during data entry C. For added security D. To simplify table design Q11. Why does Richard recommend watching the relational combo boxes video first? A. It explains how to concatenate strings B. It covers the process of picking values from one table to store in another C. It teaches advanced VBA programming D. It explains table normalization Q12. When designing the combo box, how can you make the appearance match the other controls on the form? A. Use the format painter tool B. Change its data source C. Select a different combo box type D. Set the bound column to zero Q13. What feature do you lose when basing a combo box on a query rather than a table according to the video? A. The combo box wizard B. Automatic resizing of columns C. The option to 'Hide the key column' D. The ability to use multiple columns Q14. What is one benefit for Gold members mentioned in the video? A. Access to the beginner courses only B. Free merchandise C. Access to the download folder and Code Vault D. Free consultation calls Q15. What is the prerequisite knowledge for this lesson? A. Using update queries and macros B. Relational combo boxes and string concatenation C. Report wizard and naming conventions D. Table splitting Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-A; 11-B; 12-A; 13-C; 14-C; 15-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. SummaryToday's video from Access Learning Zone focuses on how to configure a combo box in Microsoft Access so that it displays both the last name and first name of a customer in the format: last name, comma, first name. Many people have noticed that by default, when a combo box is closed, it can only show a single field, which is often limiting if you have multiple customers with the same name or want to see more than one detail at a time.Olivia, one of my students, asked how to create a combo box that shows both the first and last names together when the box is closed, not just when it is open. She also mentioned having several customers with the same name and wanted to know how to distinguish them at a glance. Usually, a combo box in Access has one hidden field - often the primary key or ID - and another visible field. While it is possible to display multiple columns when the combo box is open, only one can be shown when it is closed. To solve this, what you can do is combine, or concatenate, the two fields into one. This way, the combo box can display both the last name and the first name together, separated by a comma, when closed. I will walk you through the process of string concatenation in a query, and also discuss how to display the ID or other details if you need to. Before starting, you should be comfortable with two concepts: creating relational combo boxes and using string concatenation in Access queries. If you are not familiar with these, I recommend watching my videos on these topics first. Links are available on my website. In the TechHelp Free Template, which you can download from my site, I have already set up a combo box like this in the invoicing lesson. If you open a customer's details and view their orders, you will find a combo box that shows first and last names together for easier identification. To build this yourself, you will need to create a query that combines the last and first name fields. In Query Design, add your Customer table and pull in the customer ID - this field is important for maintaining relationships between tables. Next, create a calculated field to concatenate the last name, a comma, a space, and the first name. This gives you a single field formatted as "LastName, FirstName." Save this query as CustomerQ or something similar. If you want, you can also set up another field with the opposite order ("FirstName LastName") for use in different parts of your database, such as address labels or reports. When you have customers with the same name, it can be helpful to add extra information in the combo box, such as address, phone number, or state. Anything that helps you identify individuals more easily can be included in your query as additional fields. Once your query is ready, open up the form where you want the combo box. Enter Design View, add a combo box, and when the wizard opens, choose to look up values from the query you just created. Select customer ID as the first column, followed by your concatenated name field and any other fields you want. The combo box will store the customer ID in the underlying table, which maintains your database's relational integrity. You may need to manually hide the key column by resizing its width to zero, since Access does not always do this automatically when you base your combo box on a query. Adjust the sizes and layout of your columns as needed. Complete the wizard, label your combo box, and apply any formatting you want so that it fits with the rest of your form. After saving and reopening the form, you should now see a combo box that displays "LastName, FirstName" when closed. When opened, the combo box can include other details like phone number or state to help you select the correct customer, even if you have multiple people with similar names. If you want to explore this topic further, in the extended cut available to members, I demonstrate how to include the company name in your combo box, and handle different scenarios such as when you have customers with or without company names, or other combinations of information. To access the extended cut and other exclusive content, you can become a member of my channel. Members receive access to extended TechHelp videos, live sessions, sample databases, and my Code Vault of useful functions. Platinum members also gain entry to my full beginner and some expert courses, not just for Access but also for other Microsoft Office products and more. Remember, even if you do not become a member, all of my standard TechHelp videos remain free as long as you continue to watch them. If you found this lesson helpful, please give it a thumb up and share your thoughts in the comments - I do read all feedback. Make sure to subscribe to my channel, which is free, and enable notifications to stay up to date with new content. 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 query to concatenate last and first namesAdding a calculated field for LastName, FirstName format Including additional identifying fields in the query Setting up a combo box based on a query Configuring visible fields in the combo box Manually hiding the key column in a combo box Sorting combo box entries by last name Binding the combo box to store CustomerID Formatting and resizing combo box columns in a form |
||||
|
| |||
| Keywords: TechHelp Access first name, last name, last first, display of combo box, multiple values, columns shown, full name, combine first and last, concatenate, together, concatenation PermaLink Last Name, First in Microsoft Access |
