Microsoft Access pt 9 (Cascade Combo Box)
ฝัง
- เผยแพร่เมื่อ 23 ก.ค. 2024
- In this video learn to create Cascading Combo Box by which when you choose a Country, it will only list the State/Province from that Country. And upon choosing the State/Province the Cities from that state only. To Download sample file go to sites.google.com/view/simplet...
In nearly 4 years of having responsibilities for new databases, no one has ever made this so understandable, I followed you with my own database fields-district and job title. It works great. Wow, simple is super! Thanks Amir
Your teaching style is so clear and easily understandable. Thank you so much
Amir I would like to thank you for this demonstration. I have been trying to find a solution for this for almost 2 days (I'm new to Access) and after many failed tutorials using VBA (odd because I can program in VBA and many other languages) I finally found your video and got everything in my Database up and running the way I wanted. Your solution was very elegant and I very much enjoyed your tutorial. Thank you very much.
hands down the best explanation for the Cascade Combo Box method. Thanks a lot!
I spend two days to find a solution to this problem. This very clear video helped me out finally. Thanks Amir.
Thanks for the amazing tutorial. Totally straight forward and concise.
Thankyou for the quick response Amir. I would like you to describe it in a video because you always do it in a way which is both easy to understand and convenient to do. Please take these compliments from me on behalf of everyone who loves your videos. Also please consider to do a video on this issue it is very difficult to find good resources on the net which show us how to do it. Thanks in advance.
Maneesh Massey Thanks! Will test it out.
Excellent tutorial! This is the very solution that I needed, and you explained the procedure quite nicely. Thank you so much!
Thanx Amir - I looked at several different ways of doing this and this is by far the simplest!
Great video! Thank you for taking the time to explain every step, very helpful to Access beginners.
Thank You so much! You finally made me understand this... I have looked many tutorials and this is the only one easy enough to make sense to me... Thanks again! Great Tutorial! :)
Thank you so much for this - so much easier to adapt than learning VBA for something so simple!
Thanks, I spent hours trying to figure this out. Part of it was my fault but once I seen what you did, I was able to spot my mistake. Like someone else said your teaching is so clear.
Many thanks for this explanation, very clear and easy to follow. It was just the solution I was looking for.
It worked perfect for me in access 2007. Thank you.
This is very simple video and very helpful. steps are simple and easy. thanks you are a life saver. i tried to get it for last 2 days but due to other good for nothing videos available in youtube i am unable to get. Thank you very much. keep it up.
Great video! Clear, detailed steps, easy to follow.
you are genius bro, very generous of you to teach much valuable things on ms access 2010. good ! keep it up! learn more and teach more,
Thank you, thank you, thank you. I have been struggling with this concept for a long time.
Super helpful. I'm recommending you to everyone who needs Access help. Thank you!
THANK YOU SO MUCH. THIS VIDEO REALLY HELPED ME A LOT. I'LL HIGHLY RECOMMEND THIS VIDEO.
This helped solve a major problem for me. Thank you so much!
Thank you so much for this video! Its been such a great help!!!
Very Clear to understand.Keep it up....Rajkumar
Thank you for your videos, they are really great and more than helpful!
Great video, made it much simpler than some others that described more VB
I love it... this has solved my problem for days thinking for ways to do this.... thank you for this tutorial.
If I change the Country, the VBA code should null the State and City first before requerying. If the State is changed, it should null the City. All in all, excellent video. There are code-based ways of doing this, but this is simple and effective. Great job.
So would you add a me.combo1.value = NULL in the afterupdate before the requery or? Can you give me example code
THIS GUY IS GOOOD!!!!HELPED ME A LOT.THANK YOU
Thanks Amir. Your video helped me with something I was stuck with for a while.
This is wonderful. Now finally I get the point :)
Excellent explanation and useful, Thank you Amir.
Awesome! Was doing cascading combo boxes but was displaying the Autokey number in the table i was writing to.....this helps me out significantly. Thank you!
Thanks Amir this has been incredibly helpful for a non-coder.
bashfulcreature I am a non-coder too, currently trying to learn programming basics, before trying to learn/teach sql.
THANKSMAN, OKAY I GOT IT!! just missing something recently but now everything is works!! again many thanks
A very useful video, well explained, thank you.
Thanks you Amir. I tried following shorter tutorial by another, but it didn't work. The other tutorial seemed to follow the same process, but for some reason it only worked following your steps. Thanks.
That's interesting.
Amir Parmar Good Day Amir. I spent some time last night comparing your steps with the steps from the other video. It seems your comment at 5:42 of this tutorial is key (no pun) as you begin creating the various combo boxes for City & State via the wizard.
- In the first instance, the "store that value in this field" list does not show the field you are looking for.
- So you drag the City, State and Country fields from the available fields window, into the form.
- Then you immediately delete them - making the proper field now available for that wizard step.
The creator of the tutorial (by another) probably did that too, but that step was edited out. So when I got to that same wizard step, the field I was looking for was not on the list of available fields. I tried to force it, but got an error message.
I then tried to select the best "alternative" from the list. Of course, that was not correct - making the entire cascade process incorrect.
That is my long winded way of saying your comments and steps between 5:42 & 6:00 seem to be small in nature, but huge in effect.
Thanks again.
Great video and very easy to follow. Thanks!
I just went over all 10 Access videos in the last couple days. Outstanding job in explaining the tools. I've worked with access for several years but did not have a clear picture of the forms and ability to store "variables" in the forms. This helps solve a number of items I haven't taken time to figure out. Thank you.
Most of the applications I've built relate to extract, transform and load data. After the data is loaded, I then run specific a macro with a series of queries to generate the information required from the data via reports. An example would be to pull in a file with employee info and payroll data. Add some fields to allow me to analyse the data in accordance with some contract terms, and generate the access reports needed for review. Then I edit selected records manually based on the reports created to update some of the fields I created for analysis, and run the final reports.
I've used all of the query types to accomplish this, and then placed the queries in a series of macros in the proper sequence.
I have not seen any examples on how to better automate this type of process on the web. Almost all the examples I've seen deal with creating something for a person to enter information one by one. The examples I deal with are specific to the particular contract terms.
I'd be interested in your views or possible a small example of how to put something like this together.
Thanks for your excellent access video series.
Regards
John
Hi John, I will think about your question. can you provide a bit more detail? I think many of the things you want to get done can be accomplished via VBA. I have never bothered with programming, but it is something I am thinking about learning and than create some videos. I don't consider myself to be very good at Access, but if you ever get stumped by something; here is guy who will try to answer your questions for $5. fiverr.com/geoffreyg/answer-your-microsoft-access-2010-question
Amir
i said before you are great teacher but also you are great human
i am really enjoying learning from your clips thank you very much
Many thanks for your time. Have an awesome Day Mr. Parmar!!
Thank you so much! I am looked at a few videos on this, but, this method was clear and works!
Awesome explanations Amir !
Thanks a lot from France !
These videos are quite educative...keep it up Amir Parmar
Thank you! This is VERY helpful!
Thank you for the great videos - very helpful.
You really are a great teacher .
finally..the answer to my design problem..thanks!!
Thanks for making this video. It was very helpful.
This was perfect. Thank you!
Good tutorial! Thanks for the help!
Amir...thank you for this video. It was a nice and simple. Just one note to share. Remind everyone to have the trust settings to run VBA. I jumped in at video 9...spent a stupid amount of time trying to figure out what was wrong. My idiot moment for the day.
Thanks for the tip, will try to put a note somewhere. were you trying this on a file that had other VBA codes? Because no one else has any issues. Even I did it, i tried it on a brand new file which had not VBA codes. I will leave a note and tell people to run on Trust settings or close and open file and "enable content."
Nothing else that I have used the "Code Builder" for.
Added a note with your tip in the video
Thank you Amir. Great Video!!
Very clear explanation.. it works
Thank you, this helped understand how to do this!
Great Demo! Thanks!
I'd thank you so much. It's the easiest way I've ever seen
thanks so much for this !!! i will try to apply this to my app! big respect and i'll subscribe you! :D
Simply the best.
Thanks for the video!
It was really helpful for me.. thx alot
Thank you so much. The only thing I would change was to include the "Lookup" step near the very beginning. I had to go to another user's video to learn how to do that. I know you say it is referenced in one of your other videos, but I didn't know which one to look for.
Thanks a lot dear it really helped me
You are the best
Excellent!
Thank you so much :)
Ahh! Thank you so much!!!
Good job!
Thanks for the vid
Tnku...for this useful video...
Say now you haven't add a certain city, when you would type out that city in the form after you've selected a state and country, would it be automatically added to the other cities? Or would you have to type it out as a new record in the tblCity every time?
Thnx Amir. This video of yours helped a lot for my database. Only thing I am hung up on now is that when I select my selection in city or state it it only selects first selection in combo box. I am using access 2013
THANK YOU!
Thank you.
Amir - Thank you for the clear instructions. I'm running into an issue when trying to use this on a Datasheet form. It appears to work for the first item in the list, but the dropdown for record 2 stays the same even if it's source does. Any advice or is this only possible for single record forms?
Never used it in Datasheet form format. I am guessing this will cause a problem, as more than one record is visible, which will cause confusion.
Question: let's say that the country combo box is in a different form from the city and state combo boxes, how would you reference them in VBA?
thank you!
Great tutorial and it works perfectly in my form in datasheet view. However, when I use this form as a subform, it no longer works. I solved a parameter error by changing the form name in the query to the main form. Now, there are just blanks in my dependent combo boxes. Any ideas?
Amir,
Do you have example or similar solution with a single combo box to filter on a form so as to return a single or group of records? For example, in a payment form, I'd like to filter out all the records where payment occurred in a given month. Or an address form, where one may want to use a combo box to filter out all the addresses with TX as the state?
bjhogans I don't have an example for that but you could create a Form where people enter dates and the Form will show those reports. So you create a query and run the query via form. I made a video in which I talked about this idea, it might help you with your situation Microsoft Access 2007 2010 2013 pt 7 (Parameter query with Form; Macro & Append/Update/Delete Query)
very helpful
Thanks a lot
Amir - thank you so much for sharing! I have encountered an issue though. I followed each and every step of your tutorial - and I am noticing that the third combo box (city) is not populating, nor does it show any selections to choose from. Whereas, the first and second comboboxes are working perfectly. Do you know what could be the problem?
Tashrayt there has to be something that went wrong with the 3rd box. See the first two and compare it to the 3rd, you must have made some minor spelling mistake in the query criteria with the name of the form or field. Let me know. you can send me an email from my website or send me a private message, I will give you my email address so you can send me a sample file.
Amir - thank you for your response! I will double check the issue and let you know what I find. Thanks again ;-)
good teacher
Thank you
Great video, thanks alot. One question though, what if you have Texas in both Canada and US - for example, and Atlanta in GA and CA.
Anthony kaigwa You will need to create a Field with number or autonumber which will be set to Primary key.
Amir Parmar Thank you Amir for your awesome videos, and your quick feedback to my question. My table database works ! GREAT! So , if I want to extend this further, how would I go about about Combo boxes for COUNTY within a STATE and WARD within a CITY.- I will really appreciate if you point me to the right direction
Anthony kaigwa So it going to be State - County - City - Ward? Or State - City - County - Ward? You can add the others in the same way, the way we did State - City.
I have a table with FamilyID & FamilyName and another table with ClientID, ClientName, and FamilyName (linked by FamilyID). I set up the combobox to filter the ClientName after a FamilyName is chosen from another combobox by following your steps. However, the second ClientName combobox was showing no results. I fiddled with it a little and discovered that when I changed the bound column on the first FamilyName combobox to "2" then the ClientName combobox worked fine. Can you explain why this is happening and whether setting the bound column to "2" is a problem?
Anish Bedi I wonder if it has something to do with how you created the query, whether you chose both field or not.
14:02 I try me.refresh and it is easier. By the way thank for your great video
Usefull
Sir, please make me understand how to creat both 'Save and Next' in one button in access form . For example, this button has in ms access's local contact template.
Beautiful!!! thank you, thank you, thank you, just a thing: when I change to next record or previous the combos are not been refreshed, so I just add the me.cbostate.requery into the current event of the form.
The explanation is perfect, but just one question...the relations between the tables is one to many? so if you click on + in the country table you can see all the state?
Thank you so much
Yes when you click on the +sign you get All the states associated with that Country. On this playlist, watch video number 4 for Relationship
th-cam.com/play/PLcmYWY91gQNcB-9D2him9oNl86FpCxCHc.html
Amir, your approach of achieving the Cascading Drop down Menus is quite simple and easy to do, however i tried it several times, and it always stop to work at the second level. the cities does not show for the selected state, however the states show just fine. Do you mind sharing the access file you used for this presentation so I can examine to find out what am i doing wrong. I also can share my access file with you if you like.
Mr. Parmer, this has been a very informative video, and I want to thank you so much for your help in the creation of my own database.
However, I've run into a problem. Despite opening the drop down box in all of it's glory, the subcategory box (the state in your example) only allows me to type in numeric values on the form. These numeric values correspond with the primary key numeric ids for each subcategory choice. Is there any way to fix this so that the box accepts the drop down box options instead of the numeric ids?
Please let me know,
Casey Alcoser
+Casey Alcoser Hi Casey, in my example I had avoided using ID or number as a Primary Key. But it some cases you don't get a choice. Can you go to the query and hide the ID part and see what happens?
Amir Parmar
I didn't include the Primary Key in the inquiry. And to further provide details, I recreated the inquiry and the tables it pulled from, and modified the data in the Subcategory one so it used the subcategories as the primary key instead (I had to sacrifice a few subcategories, but I thought I should try it), but the problem persists. I can only put in a number value into the subcategories box on the form, otherwise "The Value you entered isn't valid for this field."
+Casey Alcoser Someone else had a similar issue, not sure if I was able to find a solution, let me check. see if you find any recent comments on this.
+Casey Alcoser Hi, I've had a similar problem with by database. All I did was, close all open tabs except for my main table. Then go to design view and change the data types of the fields. If this doesn't work, you could try to severe the relationships first, then change the data types and reconnect all the relationships again.
+Casey Alcoser Hi Are you able to sort out the problem? I've run into similar situation
I love this video (all your videos) - but now what do you do if there are two cities with the same name? Like Springfield, IL, USA and Springfield, MO, USA. The "City" is the Primary Key. There can not be an identical city in the Primary Key Column. What do we do? Thank you.
+Amy B
Amy, did you ever find the best way to handle the primary key issue. I have a similar issue, but it would be with the "State" selection.
Thanks can you explain how to make dependent drop list in continuous form ?
Hello, Im making database where i need to chose 1st level (car manufacturer) and 2nd (car model) but now i would like to make option at first level: "ALL Manufacturers"
Any idea?
Thank you Amir for the excellent tutorial on Combo Boxes. I was wondering if you could show us how to create a cascading combo boxes within SUBFORMS in the MAIN form.I'm not talking about CONTINUOUS forms Subform, only a single SUBFORM within a MAIN form. For example, please show us the same example of Country, State and City cascading combo boxes if they were within a Subform in a main form scenario. How would things be different then ? I do not know how to do this and find very, very few examples of anyone showing you how to do it on YouTUBE ! PLEASE HELP AMIR! Thanks a lot!
Maneesh Massey will look into it. I think you will need to add the name & Field of the SubForm in the query criteria. Try this, it might work.
Amir Parmar Did you ever end up doing a video on this? I completed the cascade form perfectly from your instructions, though when I try to insert it into the main form as a sub-form I get a " Enter Parameter Value" dialog box. Can you assist me?
You will need to change the name of the Form in the Query. I never heard from the person, I asked them to try the instructions and get back to me. Try making changes to the name.field of the Form in the query.
Chris Logan I'm having the same issue on a project at work and I this form post is the key: bytes.com/topic/access/answers/810627-help-linking-combo-boxes-subform
Thanks Allen, this will help many people. So the idea is to use a version of [forms]![NameOfMainForm]![nameofsubformcontrolonmainform].form![cbostate]
Mr. Parmar, I found this demo quite easy compared to many others I've looked at.
However, I'm in need of an equally easy solution for using cascading combo boxes to filter the same data records I've created using your method, for a report. Do you have such a demo available or can you otherwise assist me.
+Ben Pieters Hi Ben, I am not sure if I understand your question. You could create a combination of a query and form and print a Report. So the idea is, on a form you have a text field and whatever is in the box, becomes the criteria for your Query. And you have created a Report based on the query. On the form you add a Button to open this Report.
Dear can I but a condition or a constrain on combo box to show specific rows like upgrading a student degree
e.g. if he is BA then the only choices are shown MA , PHD, or proff.
and if he is MA then the only choices are shown PHD, or Proff
yes you can. Never done it, but is sure if possible with an IIF statement. But could you not use the Cascading Combox to do that also.
Hi Amir. This function works perfectly on my form, so thank you. However, when I open my form through a Navigation Form (eg: as a separate tab on the navigation form) then this function no longer works. Why is this? When I click on the 2nd Combo Box a Parameter window appears with the following message "Forms!frmpurchaseorders!cbocommodity". Thanks for your help.
Hi, try to remove the Form and add it again to the Navigation. i will have to test it to see if that happens on my end.