Display Multiple Fields in a Closed Combo Box in Microsoft Access

welcome to another fast tip video brought to you by accesslearningzone.com i am your instructor richard ross in this video i'm going to show you how you can display multiple fields in your combo boxes even when they're closed in microsoft access okay this is a question i get asked all the time so it's time for a fast tip video i've got 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 so i go to design view i pick combo box from this list put it right there i'm going to look up the values from another table or query and if you've never done this before go watch my video on relational combo boxes i'll put a link down in the link section below the video we'll get our data from customer t next i'll bring over the customer id because we need that for the id for the bound column i'll bring over let's bring over last name and then first name because i want him in that order next drop this down i'm going to sort by last name then first name that's 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 okay 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 but when i pick a customer and close it you only see one field in the combo box that's by design unfortunately so 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'll use a query to do that now i got a whole separate video on string concatenation go watch that again i'll put a link down in the link section watch that video if you want to learn more i'm going to close this form now i've already built this query before here it is right here customer lfq if you open that up you can see there's a field called lf it's got last name comma first name in it how did i do that let's go to design view alright i came over here into this blank column i created a new column and i'm going to zoom in so you can see that it's 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 okay so there we got a new column now called lf a new field in the query a calculated query field and it looks like that now we can use this guy in the combo box all right so let's go back into here design view i'm going to delete the one i created a minute ago and let's create a new one ready combo box drop it there look up the values in the table or query this time go to queries pick customer lfq next bring over the customer id and the lf ignore last name we don't need that all right sort it by lf next now since this is based on a query you don't see that little checkbox 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 and as you can see now that's showing up in one field hit next select the bound column that's 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 and one more time i'll grab the format from up top here and paste that over that one use a little format painter right so it looks nice and then i'll resize this guy like this save it close it open it back up again and 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 now the second option is to use something called dlookup it's a function you can use it to look up a value from a table or query now let's say we've already got this and i want to see one more thing over here maybe i want to see the customer's credit limit all right have that displayed right here after i picked the customer all right we can use dlookup for that so how do we do that design view grab a text box drop it over here i'll just delete the label and slide this over here and i like to make things that you can't edit gray because if you use dlookup you can't edit this value it's just for display only if you want to edit this value you'd 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're going to grab the credit limit field which is a field over here credit limit and we're gonna 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's combo 18 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 okay that's just me all right all right so let's open this up we can call this guy credit limit and then in the control source i'm going to zoom in shift f2 i'm going to say equals d lookup and then in parentheses inside of quotes what are we looking up the credit limit comma from what table customer t or you can look it up from queries to comma what's our criteria where the customer id that's the field from the table equals and then ampersand customer combo that's going to use string concatenation there and it's going to actually put the value that's in the customer combo box inside this string so it'll be customer id equals one again string concatenation go watch that video it's a very good video and also i have another video just on the dlookup function you can go watch that too okay i'll put links down below in the links section all right hit okay close that save it close it now when i open this up i get an error message that error message is because there's no value in here right i've got no order for this i changed the order for myself earlier so let me go back in the order table here and make an order for me and now i should get the value there it is if i change this to somebody else you'll see the value in their updates okay and 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 alright save it and then when you come back in there you go there's the currency value now on a new order okay if you want to avoid seeing error there because there's 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 n z and that'll say if the customer combo is null if there's no value in it put a 0 here all right comma zero again i got videos on nz i'll put a link down below that says null to zero okay hit okay close that close that save changes and now open it up go to a blank new one and at least it'll see blank in there okay and now as soon as you create a new order for somebody you'll see their credit limit pops in there you can do that with any field you want dlookup is a very powerful function it's got cousins d look up the max the sum d count there's all kinds of different domain aggregate functions and i cover them all in my lessons but definitely go watch my dlookup lesson you'll see there's lots of different ways to use it down here watch my string concatenation video that's good got a video on the nz function these are all free videos on my website by the way folks and of course if you're new to access come to my website check out my free four hour long access beginner level one course i know it says level one if you've got some access experience under your belt a lot of people are like i don't need it i don't need the basics no i cover a lot of good fundamental stuff in this class that's why it's 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 haven't already and on behalf of access learning zone this is richard ross i'll see you again soon you

Channel: Computer Learning Zone

Views: 2,488

Rating: 5 out of 5

Keywords: microsoft 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, display two fields in combo box, see two fields in combo box, combo box display second column

Id: ViPcqPzkN4w

Length: 9min 12sec (552 seconds)

Published: Sun Sep 19 2021