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

แชร์
ฝัง
  • เผยแพร่เมื่อ 24 ธ.ค. 2024

ความคิดเห็น • 94

  • @vickiclark7293
    @vickiclark7293 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

      Glad to help!

  • @MelodieLicht
    @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
      @599CD  ปีที่แล้ว

      Glad it helps.

  • @1000Zebedee
    @1000Zebedee 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

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

  • @captainkeyboard1007
    @captainkeyboard1007 3 ปีที่แล้ว

    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
      @599CD  3 ปีที่แล้ว

      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
      @captainkeyboard1007 3 ปีที่แล้ว +1

      @@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 TH-cam. 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.

  • @ihabemran7994
    @ihabemran7994 2 ปีที่แล้ว +1

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

    • @599CD
      @599CD  2 ปีที่แล้ว +1

      Welcome

  • @rexyooper1
    @rexyooper1 2 ปีที่แล้ว

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

    • @599CD
      @599CD  2 ปีที่แล้ว

      [burp]

  • @Jojosmith342
    @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  ปีที่แล้ว

      599cd.com/QQ

  • @f.d.harbecke8660
    @f.d.harbecke8660 6 หลายเดือนก่อน

    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

  • @michaeltrew8827
    @michaeltrew8827 2 ปีที่แล้ว

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

    • @599CD
      @599CD  2 ปีที่แล้ว

      YW

  • @julioflorentino3321
    @julioflorentino3321 ปีที่แล้ว

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

    • @599CD
      @599CD  ปีที่แล้ว

      thanks

  • @harrisonmiller404
    @harrisonmiller404 ปีที่แล้ว +1

    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!

  • @gonefishing2006
    @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.

  • @mehranbarahouie
    @mehranbarahouie 2 หลายเดือนก่อน

    Great Job

  • @geoffjacobs267
    @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?

    • @599CD
      @599CD  28 วันที่ผ่านมา

      Why do you have them in separate tables? For followups: 599cd.com/Ask

  • @bharatkhatri1987
    @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
      @599CD  ปีที่แล้ว

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

    • @bharatkhatri1987
      @bharatkhatri1987 ปีที่แล้ว

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

  • @KevinRackliff
    @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
      @599CD  ปีที่แล้ว

      welcome

  • @Psychopatz
    @Psychopatz 2 ปีที่แล้ว

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

    • @599CD
      @599CD  2 ปีที่แล้ว

      Great to hear!

  • @Valencia-1307
    @Valencia-1307 4 หลายเดือนก่อน

    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
      @Valencia-1307 4 หลายเดือนก่อน

      or the option to store that value in this field

  • @ChristopherDanello
    @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
      @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

  • @benmoore8547
    @benmoore8547 ปีที่แล้ว

    brilliant video, super helpful loved it!!

    • @599CD
      @599CD  ปีที่แล้ว

      Thanks

  • @HelenWagner-cw5gw
    @HelenWagner-cw5gw ปีที่แล้ว

    You are amazing. It is what I need!!

    • @599CD
      @599CD  ปีที่แล้ว

      Sweet

  • @alexk7467
    @alexk7467 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

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

  • @PeterPapadimitriou
    @PeterPapadimitriou 4 หลายเดือนก่อน

    Great video. Thank you

    • @599CD
      @599CD  4 หลายเดือนก่อน

      Glad you liked it!

  • @ledahbernardi1070
    @ledahbernardi1070 9 หลายเดือนก่อน

    Hi there! Finally I´m a Silver Member, here in TH-cam, 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
      @599CD  9 หลายเดือนก่อน +2

      There is an Extended Cut Playlist: th-cam.com/play/PLhKFRV3-UgpeedUEMU1vJN-2u1yIKF0Zs.html
      But generally you're going to find the link to the Ext Cut video on the free video it follows.

  • @williamstucke5445
    @williamstucke5445 3 ปีที่แล้ว

    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
      @599CD  3 ปีที่แล้ว

      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.

  • @gregoryjeffries8475
    @gregoryjeffries8475 2 ปีที่แล้ว

    Love your vids. Great targeted info.

    • @599CD
      @599CD  2 ปีที่แล้ว

      Glad you like them!

  • @ash7324
    @ash7324 ปีที่แล้ว +1

    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
      @599CD  ปีที่แล้ว

      See previous reply

  • @macfivo
    @macfivo ปีที่แล้ว

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

  • @shawnbuettner6073
    @shawnbuettner6073 4 หลายเดือนก่อน

    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
      @599CD  4 หลายเดือนก่อน

      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

  • @burnts0x
    @burnts0x 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

      Might be easier to.

  • @stephanhoffmann2981
    @stephanhoffmann2981 3 ปีที่แล้ว

    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
      @599CD  3 ปีที่แล้ว +1

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

  • @nurzamarul6047
    @nurzamarul6047 11 หลายเดือนก่อน

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

    • @599CD
      @599CD  11 หลายเดือนก่อน

      Huh?

  • @merrymeri4700
    @merrymeri4700 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

      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

  • @sebastiaangovers9547
    @sebastiaangovers9547 3 ปีที่แล้ว

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

    • @599CD
      @599CD  3 ปีที่แล้ว

      Sure is. 599cd.com/Continuous

  • @beholderer
    @beholderer 3 ปีที่แล้ว

    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
      @599CD  3 ปีที่แล้ว +1

      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
      @beholderer 3 ปีที่แล้ว

      @@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
      @599CD  3 ปีที่แล้ว +1

      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.

  • @waelfathy2199
    @waelfathy2199 3 ปีที่แล้ว

    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  3 ปีที่แล้ว +1

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

  • @tomservo75
    @tomservo75 3 ปีที่แล้ว

    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
      @599CD  3 ปีที่แล้ว +1

      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
      @tomservo75 3 ปีที่แล้ว

      @@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.

  • @MuhammadImran-oo2qp
    @MuhammadImran-oo2qp 3 ปีที่แล้ว

    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
      @599CD  3 ปีที่แล้ว

      Please clarify.

  • @tiffanyjanish332
    @tiffanyjanish332 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

      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

  • @sebastiansndergaard5828
    @sebastiansndergaard5828 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

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

    • @sebastiansndergaard5828
      @sebastiansndergaard5828 2 ปีที่แล้ว

      @@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

  • @alializadeh8195
    @alializadeh8195 ปีที่แล้ว

    Thanks

  • @nisarahmed5869
    @nisarahmed5869 3 ปีที่แล้ว

    Very nice

    • @599CD
      @599CD  3 ปีที่แล้ว

      Thanks

  • @abdulhamidalhaddadi2255
    @abdulhamidalhaddadi2255 3 ปีที่แล้ว

    Thanks Alot

    • @599CD
      @599CD  3 ปีที่แล้ว

      You're welcome

  • @nemo9396
    @nemo9396 2 ปีที่แล้ว

    Checked out your lessons but the pricing is really prohibitive

    • @599CD
      @599CD  2 ปีที่แล้ว

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

  • @manuelgiraldo3465
    @manuelgiraldo3465 2 ปีที่แล้ว

    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
      @599CD  2 ปีที่แล้ว

      You can select whatever field you want.

  • @michaelrstahl4195
    @michaelrstahl4195 3 ปีที่แล้ว

    a+ video

    • @599CD
      @599CD  3 ปีที่แล้ว

      Thanks!