Relational Combo Boxes in Microsoft Access - Get the Values from Another Table or Query

[@vickiclark7293]

Thank you so much! I haven't worked with Access since I retired in 2011 and lost so much! You make it very easy!!

[@599CD]

Glad to help!

[@captainkeyboard1007]

Working with Lookup Wizards, Combo Boxes and List Boxes are my favorite tasks to do in Access. Not only I feel like I have gained knowledge. As a fan and user of Microsoft Office 2019, I am entertained, thus using Access at my own leisure.

[@599CD]

That's the best way to learn... when it's FUN and not something for work. When I was first learning how to program, I was a big D&D player (back in the 80s) and I wrote a character generator. :)

[@captainkeyboard1007]

@@599CD You and other Microsoft Office experts teach Access with clarity and with style. The main thing I do with computer that I call my "keyboard," as my typewriter is to manage and organize information. Thus, Access gets the job one. I do not know many people who use Microsoft Access. It is one reason that I have been spending lots of time on KZbin. Each Microsoft Office application has the power to perform operations to complete a task that would be impossible to accomplish by other methods. Throughout the years, Microsoft Office has tremendously improved.

[@1000Zebedee]

Hi Richard. Sincere thanks for sharing your wealth of DB knowledge. This is now making sense to me having listened to your wonderful narration. Thanks again 😀 (BH from UK)

[@599CD]

You're very welcome (from Florida). :)

[@MelodieLicht]

It is the little things that count! I've come back to this video now a few times ... had to fully understand this and what I was missing when it didn't work! Thank you so much for sharing these tutorials... little by little, inch by inch. I have picked up so much from you so want you to know how much I really appreciate you and what you've shared. I do this strictly for myself or I would jump right into your program!

[@599CD]

Glad it helps.

[@rexyooper1]

“Ate a tribble”. Love the video and the comedy.

[@599CD]

[burp]

[@f.d.harbecke8660]

This video, and the others in the sidebar, are superb. A lot of detail, well-explained, very engaging! And this comes from an instructional designer whose lot in life is to do precisely what you're doing, but for any topic under the sun. LOL I do have a question for you: What if you're trying to reference a combo box that's already a combo box? In other words, I want my source to reflect the table records, but I need to interpret them in terms of the original combo box categories. When I create the field in the table or the form, I just get the numbers but not the text it refers to. I hope this question makes sense, and I hope this is the right place to ask the question--I do consider it part of "get the values from another table or query." Have a great day, and thanks again for the excellent videos. =D

[@Jojosmith342]

Richard Rost - the GREATEST Access teacher of all time. Thank you so much Richard for your time & kindness to educate the world. What if the customer list is long with > 100,000 customers? would there be another option other combo box? thanks a lot Richard

[@599CD]

599cd.com/QQ

[@ihabemran7994]

Superb!! Great value, great benefit. Thank you!!

[@599CD]

Welcome

[@michaeltrew8827]

learnt a lot here in a relatively short space of time, thanks!

[@599CD]

YW

[@Psychopatz]

Thank you sir! These tutorials are super cool and helpful. This definitely flames up my passion to learn ms access.

[@599CD]

Great to hear!

[@julioflorentino3321]

Hi, You are a good instructor, Best regards to you.

[@599CD]

thanks

[@harrisonmiller404]

Hey Richard, newbie here. I have learned most of what I know from just a few of your Vids, GREAT STUFF! I just have a question relating to the combo box! The combo Box I have created is entering into my InsandOutsT the ID number instead of "In Stock", "Assigned" or "Installed" which is hard to read from the table view, whereas when using a list value it inserted the text instead of the ID. How do I solve this? Best Regards!

[@geoffjacobs267]

OK, so the video created a system for connecting customer names with service call tickets. But what about connecting customer names and their id's to customer accounts in the database? Would a good way of doing that be to create a customer account table with a primary key of CustomerID and a foreign key of NameID (which would be the primary key in the CustomerID table)? And then the ServiceCall table would have a foreign key of nameID, connecting it to the CustomerID table?

[@ChristopherDanello]

Richard Rost, I love your videos! They are enormously helpful. I saw a video in which you commented on how to fix reports when using a combo box (where is shows the field value and not the ID number). I have not been able to find the video again since. Do you recall this video?

[@599CD]

Not sure. Generally you just make a query, join the fields you need, and then use that query for the report. 599cd.com/QQ

[@gonefishing2006]

Saved again - kind of. Have you done a video regarding comboboxes and save for later use. I was stumbling about on that for a little. Finally figured it out but.

[@ledahbernardi1070]

Hi there! Finally I´m a Silver Member, here in KZbin, but I´m having some trouble finding the extended cuts, for the members here on you tube, can you help me please? Just love your classes, and I want to learn a lot more. Tks

[@599CD]

There is an Extended Cut Playlist: kzbin.info/aero/PLhKFRV3-UgpeedUEMU1vJN-2u1yIKF0Zs But generally you're going to find the link to the Ext Cut video on the free video it follows.

[@HelenWagner-cw5gw]

You are amazing. It is what I need!!

[@599CD]

Sweet

[@benmoore8547]

brilliant video, super helpful loved it!!

[@599CD]

Thanks

[@waelfathy2199]

Hi Richard You create amazing access videos you increase my knowledge about access I appreciate that. Could you please Create another one on handling common errors that may occur when working with split databases like on network is not available or a record is used by another user or sub or when a portion of code tries to edit a bunch of records that may one of them are blocked by another user Thanks in advance

[@599CD]

599cd.com/ErrorMessages - the extended cut goes into a lot of advanced stuff like that.

[@KevinRackliff]

Hi Richard, great videos and extremely useful, thanks. Do you have a video that deals with the following situation: I have a combo box on my form that has a pull down list of 3 columns from a table, the user only sees columns 2 & 3. I want them to pick from column 3 and that's the value to be saved to the table the form is bound to. Thanks again for your great videos

[@599CD]

welcome

[@ash7324]

I cannot select any of the values in the combo box. Form, Detail & Query allow edits. Followed the tutorial perfectly and nothing works. Close to giving up this is driving me insane

[@599CD]

See previous reply

[@mehranbarahouie]

Great Job

[@PeterPapadimitriou]

Great video. Thank you

[@599CD]

Glad you liked it!

[@gregoryjeffries8475]

Love your vids. Great targeted info.

[@599CD]

Glad you like them!

[@tiffanyjanish332]

Hey Richard! BTW, I am one of your biggest fans! I think you are brilliant in your methods of teaching these concepts - very easy to follow and understand, so thank you! Here's my question for you related to Relational Combo Boxes: I've created a form based on a query, then added a combo box to select from a value from one of the tables used in that query. The query contains a couple of columns from the EntityT. When I add the combo box EXACTLY as you have shown in your video, the drop down is populated as it should. But, when selecting a value, it makes an error sound and won't allow me to select a value, thus won't allow the "Entity" value on the form to update per my selection. Is this because I am using the wrong type of form or something? I am out of ideas - BUT I'm thinking it is likely going to be something super simple! LOL But, I have properties set to allow edit, data entry, no locked, etc. Please HELP! :) Thanks in advance for your assistance!

[@599CD]

Sounds like the query under your form isn't updateable. Try using JUST a table as the record source for the form. See 599cd.com/NotUpdateable

[@Valencia-1307]

I am following along with the video but after I add the combo box, I get all the screens but don't get the one where I it asks "Remember the value for later use". How can I tie it to the correct table and field?

[@Valencia-1307]

or the option to store that value in this field

[@williamstucke5445]

Hi Richard. In one of your videos you delete a relationship before creating a combobox. I can't find which one! Specific question: If you use an inner join in your SQL query, does that obviate the need for a 1:n relationship between the tables?

[@599CD]

Deleting the relationship: you got me. I don't remember that one. Question: nope. Inner join just says you have to have equal records on both sides of the join.

[@macfivo]

Can a combo box be used to add text selections to another combo box?

[@alexk7467]

Hi Richard, I have a question relating to combo boxes. I currently have a access database with a few tables which are setup with relationships to other tables in the database, a form with multiple textboxes to search for records in one table and a query to display the results. What I would like to know is (which is complicated to explain), is there a way to add a combo box in a search form which has a relationship to another table and if you don't select a record in the combo box, the query treats it as a wildcard? I have watched your video on how to create forms and learnt how to create a form to search for records using textboxes and combo boxes, but I can't figure out how to do this.

[@599CD]

I get a ton of questions every day, and I don't have time to answer them all here on KZbin. Feel free to submit your question on my website at: 599cd.com/AskYT

[@bharatkhatri1987]

Hi Richard, Can this be done/used in continuous subforms too? Select one field and automatically the values from corresponding fields of that record will be picked/displayed/saved. Specially in invoicing module. Pls advise.

[@599CD]

Sure can. See the extended cut for 599cd.com/Invoicing for an example.

[@bharatkhatri1987]

@@599CD thanks, will try and update you of outcome.

[@burnts0x]

Alas - I had it for a second, but I've screwed up the relationships between tables. Do I have to start over again if I deleted a foreign key that was linked to a primary key in a different table?

[@599CD]

Might be easier to.

[@shawnbuettner6073]

When I look at my tables, the ID number is stored there in the title field, but not the actual title. It looks good on the form, but the table isn't correct.

[@599CD]

Well there's no way that I can help you from here without a lot more information so if you need help post here: 599cd.com/Ask

[@merrymeri4700]

Hi, I have multiple combo boxes and I want them to automatically add up to a new field. What do I do to get that?

[@599CD]

Multiple combo boxes on a single form? Just set the control source of your text box that's going to hold the sum to: =Combo1+Combo2+Combo3. If they're in different records in a continuous form, that's going to take more work. You'll need 599cd.com/DSum or a form footer total 599cd.com/FormFooterTotal

[@stephanhoffmann2981]

Hello Richard, many thanks for your very good videos. I'm a newbee in access, so I have a question on your Relational Combo Box Definition. Am I right, that the relation you define between the tables is a undefined relation (in the meaning that it is not a 1:n, 1:1 or m:n relation). I don't see, why you haven't defined a table of titles with an1:n relation instead (because one title can have multiple persons). This is what I've expected asba Relational Combo Box. What ist the limit/ disadvantage of your Relational Combo Box. Many thanks for your reply in advance!

[@599CD]

I don't generally define global relationships for the database, no. Therefore this is technically called an "ad hoc" relationship.

[@sebastiansndergaard5828]

Hi Richard I have learned almost everything in access through your videoes and I love them! I do have an issue with my database for keeping track of my Music records. I have created a combobox as a drop down menu for the genres, subgenres etc. The comboboxes is storing the ID-number for the genre in my parent table and sometimes I don't assign a subgenre for a specific Vinyl. When I leave the combobox empty for subgenres in a new post it won't allow me to save the new post and an error appears with the code 3201. I don't know how to fix it. The relation between the subgenretable and parenttabel is "One to Many", since one subgenre can appear on many records. I'm only having the trouble with new posts in the database and the subgenre is not the only combobox which is teasing me. I hope you can catch the problem in my entangled text here. English is not my first language. If you want I can send you a video, where I explain the problem.

[@599CD]

Do you have the field required? Do you have global relationships with referential integrity set up?

[@sebastiansndergaard5828]

@@599CD The field is not required since I don't have a subgenre for all my vinyl records. I might have to check up with the global relationships with referential integrity

[@sebastiaangovers9547]

Is it also possible to create a form in which you either select an existing customer or create a new customer?

[@599CD]

Sure is. 599cd.com/Continuous

[@MuhammadImran-oo2qp]

I creat DATA BASE but when i open this DATABASE in another PC or Laptop,So DATABASE resolution are changed and i will reset the DATABASE field again,kindly solove the problem

[@599CD]

Please clarify.

[@beholderer]

Hello, Richard. I really appreciate your content. I have an interesting question. How can you capitalize a letter after a dot (".") or period (".") in a string using VBA?

[@599CD]

That's going to be a tough one. Are you talking about taking a Long Text field with sentences in it and making sure every word following ". " is capitalized? That's going to involve some VBA and a loop. Would make for a good advanced video though. :)

[@beholderer]

@@599CD Yes, precisely. Currently the StrConv function with the Criteria vbproper case does this but it only capitalizes the character after a dot(".") and followed by a space(" "). But if the letter is inmediately after the dot(".") It will remain lowercase. I am trying to solve this because I want to capitalize the names of businesses entered by users so the table remains consistant. And some business's names have some acronyms like for example "True Metal R.L. Ltd.

[@599CD]

An interesting problem. I'll add it to my future video list... but usually complicated things like this that involve VBA generally tend to go into the Extended Cuts for members.

[@tomservo75]

Don't mean to be nitpicky here, I know this is just an example but why have a titles table? Just requires an extra query join to display someone's name with title.

[@599CD]

Proper relational design. What if you want to add/delete titles from the list, and you've got that combo box on 3 different forms? With a titles table you don't have to make that change multiple times in your database.

[@tomservo75]

@@599CD Good point. I would probably have had the titles table also, but just, as you said, to use on multiple forms to ensure consistency, I think I would still put title in the customer table, arguing that the title is related to the customer ID and only the customer ID and therefore meets 3rd normal form. I do the same thing for state because my pet peeve is data integrity. Once I was working on a client project where I had to extract data from a legacy system and put it into a new database and was horrified at what I saw - cities where the country should be, inconsistencies in the state column (IL vs. Ill.), countries spelt 2 different ways. So I ALWAYS use a dropdown for things like that and then maybe a relationship if that field is not directly related to my primary key.

[@nisarahmed5869]

Very nice

[@599CD]

Thanks

[@alializadeh8195]

Thanks

[@abdulhamidalhaddadi2255]

Thanks Alot

[@599CD]

You're welcome

[@nemo9396]

Checked out your lessons but the pricing is really prohibitive

[@599CD]

Sorry you feel that way. 599cd.com/Cheaper

[@manuelgiraldo3465]

Gratis but Just one thing, shouldnt be better select a customer from a combo box y their names instead their title? This is exactly what I need

[@599CD]

You can select whatever field you want.

[@nurzamarul6047]

13:27-to change from id to name.from relational tables

[@599CD]

Huh?

[@michaelrstahl4195]

a+ video

[@599CD]

Thanks!