Relational Combo Boxes in Microsoft Access - Get the Values from Another Table or Query
HTML-код
- Опубликовано: 30 июл 2024
- In this video, I will show you how to pick a customer from a list of customers when filling out service forms.
Marie from Chandler, Arizona (a Platinum Member) asks: I have a simple service call form where I enter the customer's name, the service date, and a description of what needs to be done. Is there any way that I can pick the customer from a list instead of having to enter in their info each time I make a service ticket?
Silver Members and up get access to an Extended Cut of this video. Members will learn how to create a List Items Edit Form so that you can add to or modify the existing list of customers directly from in the combo box. We will see how to use the VBA On Not In List event to add the user's suggestion directly from code.
MEMBERS VIDEO:
• Relational Combo Boxes...
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/RelationalCombo
LINKS:
Value List Combo Boxes: 599cd.com/ComboValueList
Blank Template: 599cd.com/Blank
Concatenation: 599cd.com/Concatenation
Relationships: 599cd.com/Relationships
Reserved Words: 599cd.com/ReservedWords
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, combo box, add to combo box, list items edit form, limit to list, on not in list event, notinlist, acDataErrDisplay, acDataErrContinue, acDataErrAdded, NewData, edit drop down list, control source, add entry not in list, drop down list based on another field, drop down list with values from another field
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
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!
Glad it helps.
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)
You're very welcome (from Florida). :)
Thank you so much! I haven't worked with Access since I retired in 2011 and lost so much! You make it very easy!!
Glad to help!
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.
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. :)
@@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 RUclips. 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.
Thank you sir! These tutorials are super cool and helpful. This definitely flames up my passion to learn ms access.
Great to hear!
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.com/QQ
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
learnt a lot here in a relatively short space of time, thanks!
YW
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.com/ErrorMessages - the extended cut goes into a lot of advanced stuff like that.
“Ate a tribble”. Love the video and the comedy.
[burp]
Superb!! Great value, great benefit. Thank you!!
Welcome
Love your vids. Great targeted info.
Glad you like them!
brilliant video, super helpful loved it!!
Thanks
Hi, You are a good instructor, Best regards to you.
thanks
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!
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
You are amazing. It is what I need!!
Sweet
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!
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?
Not sure. Generally you just make a query, join the fields you need, and then use that query for the report. 599cd.com/QQ
Thanks
Thanks Alot
You're welcome
Very nice
Thanks
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!
I don't generally define global relationships for the database, no. Therefore this is technically called an "ad hoc" relationship.
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.
Can a combo box be used to add text selections to another combo box?
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
welcome
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?
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.
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.
Do you have the field required? Do you have global relationships with referential integrity set up?
@@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
Hi there! Finally I´m a Silver Member, here in RUclips, 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
There is an Extended Cut Playlist: ruclips.net/p/PLhKFRV3-UgpeedUEMU1vJN-2u1yIKF0Zs
But generally you're going to find the link to the Ext Cut video on the free video it follows.
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?
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. :)
@@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.
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.
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.
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
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?
Might be easier to.
a+ video
Thanks!
Is it also possible to create a form in which you either select an existing customer or create a new customer?
Sure is. 599cd.com/Continuous
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?
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
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.
Sure can. See the extended cut for 599cd.com/Invoicing for an example.
@@599CD thanks, will try and update you of outcome.
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
Please clarify.
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
See previous reply
13:27-to change from id to name.from relational tables
Huh?
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.
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.
@@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.
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
You can select whatever field you want.
Checked out your lessons but the pricing is really prohibitive
Sorry you feel that way. 599cd.com/Cheaper
Excellent
Thanks