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
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.
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.
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.
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.
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! :)
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.
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.
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.
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
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!
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."
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.
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.
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
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 ua-cam.com/play/PLcmYWY91gQNcB-9D2him9oNl86FpCxCHc.html
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, 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.
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!
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
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.
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?
I followed the video and it was very helpful. For an existing record where a user has already set all three fields: Country, State and City...if the user changes the Country without touching the other combo boxes, then the data for Country is stored and the previous values for State and city are as they were which is not correct. Example: record has "USA", "Georgia", "Atlanta". User change Country to "Canada" and saves the record, the database now has "Canada", "Georgia" "Atlanta". How do you prevent this?
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?
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.
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 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.
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)
Hi Amir, I tried your example in this video, its a good approach, I'm also novice, after preparing this form, in the testing phase I noticed, when you select country then state then city, every thing goes well, after selecting city if you change the state the city will remain, similarly if then you change the country the state and city will remain same, and it records wrong information. The form should change the state, at least the value should be TRUE, if not wrong, means if I change country from USA to Canada, and operator forget to enter state again, the form should select at least a Canadian state, not the USA. Please give your suggestion to over come this fault.thanks in advance.
waleed ahmed Hi Waleed, even I am a novice, and learn things from people asking questions. I thought I had looked into the matter but not sure if did find a solution. Will see if I can pull something up. A kind of IIF function could surely do the job, IIF cboBox1 is changed clear cbobox2 and cbobox3.
waleed ahmed I think this link has your answer. answers.microsoft.com/en-us/office/forum/officeversion_other-access/validate-combo-box/716ae67a-3c24-4ec0-b47a-03e52a70e57f?auth=1
Amir! This video helped me tons! I made cascading combo boxes (which I am using to filter down data from a single table). I want to print reports based on the filtering the combo boxes do. It works perfectly if all Combo Boxes are filled, but I want to be able to print a report if only the first combo box if filled and the lower ones are empty. I believe I need to use an if then statement, could you explain? Or show me where I could get more info on this.
Matt Schumann Here is a link that explains the Nz function along with IIF maybe this should assist you support.office.com/en-nz/article/Nz-Function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c
Thanks!! New question! How can I modify my Combo Boxes to be able to chose a city if the country and stat combo boxes are null? I have one table with all my data, my boxes filter them down, but if the first combo box is null the following boxes don't display(due to our cascading effect). Can I do an If then within the query criteria to show if cbo#1 is null, not to use the criteria? thanks! I.E. I want to see a report of a city name (having the country and state cbo=null). I have one table as a database.
Matt Schumann will need to think about this. Because if first box is Null, what will it filter in the 2nd box? I suppose you could have a record in the table for 1st box, which will be empty, but than primary key cannot be empty :) You could write a VB code to make it happen, but I have not clue about it at this point. In response to you i.e. you could create a query, and in the criteria of the field that you want empty, enter expressions IsNull. Now create a Report based on this query. If you want to use the Form fields with the query, now modify the query and Point it to the field on the form. And create a button to open the Report on that 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?
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.
Sir i like your videos!!! I Have a question regarding the query, in the video you created two query which rely on cbocountry and cbostate in frmcustomer but if you create another form say frmfacility and place combo for ctry,state & city and call the query it doesnt work.as the qry is looking in form customer where it should look in form facility and cbocountry.... so how to solve this ? Also plz tell if this experssion is possible [Forms]![Current Form]![Cbocountry] ...plz reply .... thanks for the good tutorials ...god bless you
Amir Parmar what i did is in parameter of query instead of writing [forms]![frmxyz]![cbostate] i wrote [cbostate] and it works in multiple forms...... also now i am stuck with the search txt box and the button ur macro is right but its not showing the results..... anyway thanks for the reply keep up the good work
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 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.
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.
I spend two days to find a solution to this problem. This very clear video helped me out finally. Thanks Amir.
hands down the best explanation for the Cascade Combo Box method. Thanks a lot!
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.
Very Clear to understand.Keep it up....Rajkumar
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,
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.
Thanx Amir - I looked at several different ways of doing this and this is by far the simplest!
These videos are quite educative...keep it up Amir Parmar
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.
Many thanks for this explanation, very clear and easy to follow. It was just the solution I was looking for.
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! :)
I love it... this has solved my problem for days thinking for ways to do this.... thank you for this tutorial.
Thanks for the amazing tutorial. Totally straight forward and concise.
Excellent tutorial! This is the very solution that I needed, and you explained the procedure quite nicely. Thank you so much!
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.
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.
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
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
Super helpful. I'm recommending you to everyone who needs Access help. Thank you!
Thank you so much for this - so much easier to adapt than learning VBA for something so simple!
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
Great video, made it much simpler than some others that described more VB
Great video! Clear, detailed steps, easy to follow.
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!
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 SO MUCH. THIS VIDEO REALLY HELPED ME A LOT. I'LL HIGHLY RECOMMEND THIS VIDEO.
It worked perfect for me in access 2007. Thank you.
Thank you, thank you, thank you. I have been struggling with this concept for a long time.
Thank you for your videos, they are really great and more than helpful!
This helped solve a major problem for me. Thank you so much!
THIS GUY IS GOOOD!!!!HELPED ME A LOT.THANK YOU
Thank you so much for this video! Its been such a great help!!!
You really are a great teacher .
Excellent explanation and useful, Thank you Amir.
Thanks Amir. Your video helped me with something I was stuck with for a while.
Great video and very easy to follow. Thanks!
A very useful video, well explained, thank you.
Thank you so much! I am looked at a few videos on this, but, this method was clear and works!
Many thanks for your time. Have an awesome Day Mr. Parmar!!
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.
This is wonderful. Now finally I get the point :)
finally..the answer to my design problem..thanks!!
THANKSMAN, OKAY I GOT IT!! just missing something recently but now everything is works!! again many thanks
Thank you so much :)
Very clear explanation.. it works
Thanks for making this video. It was very helpful.
Awesome explanations Amir !
Thanks a lot from France !
Thank you Amir. Great Video!!
Thank you! This is VERY helpful!
I'd thank you so much. It's the easiest way I've ever seen
very helpful
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.
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
You are the best
Thank you for the great videos - very helpful.
Simply the best.
Great Demo! Thanks!
It was really helpful for me.. thx alot
Good tutorial! Thanks for the help!
Tnku...for this useful video...
Thanks for the video!
Thanks a lot dear it really helped me
Excellent!
good teacher
Thank you.
Thank you, this helped understand how to do this!
thanks so much for this !!! i will try to apply this to my app! big respect and i'll subscribe you! :D
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
ua-cam.com/play/PLcmYWY91gQNcB-9D2him9oNl86FpCxCHc.html
This was perfect. 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, 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.
Ahh! Thank you so much!!!
Thanks for the vid
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]
Good job!
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?
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.
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.
THANK YOU!
I followed the video and it was very helpful. For an existing record where a user has already set all three fields: Country, State and City...if the user changes the Country without touching the other combo boxes, then the data for Country is stored and the previous values for State and city are as they were which is not correct. Example: record has "USA", "Georgia", "Atlanta". User change Country to "Canada" and saves the record, the database now has "Canada", "Georgia" "Atlanta". How do you prevent this?
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?
14:02 I try me.refresh and it is easier. By the way thank for your great video
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.
Usefull
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 ;-)
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.
Thanks a lot
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)
Thank you
Hi Amir,
I tried your example in this video, its a good approach, I'm also novice,
after preparing this form, in the testing phase I noticed, when you select country then state then city, every thing goes well, after selecting city if you change the state the city will remain, similarly if then you change the country the state and city will remain same, and it records wrong information.
The form should change the state, at least the value should be TRUE, if not wrong, means if I change country from USA to Canada, and operator forget to enter state again, the form should select at least a Canadian state, not the USA.
Please give your suggestion to over come this fault.thanks in advance.
waleed ahmed Hi Waleed, even I am a novice, and learn things from people asking questions. I thought I had looked into the matter but not sure if did find a solution. Will see if I can pull something up. A kind of IIF function could surely do the job, IIF cboBox1 is changed clear cbobox2 and cbobox3.
waleed ahmed I think this link has your answer. answers.microsoft.com/en-us/office/forum/officeversion_other-access/validate-combo-box/716ae67a-3c24-4ec0-b47a-03e52a70e57f?auth=1
Amir Parmar Thanks Amir.
Amir! This video helped me tons! I made cascading combo boxes (which I am using to filter down data from a single table). I want to print reports based on the filtering the combo boxes do. It works perfectly if all Combo Boxes are filled, but I want to be able to print a report if only the first combo box if filled and the lower ones are empty.
I believe I need to use an if then statement, could you explain? Or show me where I could get more info on this.
Matt Schumann Here is a link that explains the Nz function along with IIF maybe this should assist you support.office.com/en-nz/article/Nz-Function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c
Thanks!! New question! How can I modify my Combo Boxes to be able to chose a city if the country and stat combo boxes are null?
I have one table with all my data, my boxes filter them down, but if the first combo box is null the following boxes don't display(due to our cascading effect). Can I do an If then within the query criteria to show if cbo#1 is null, not to use the criteria?
thanks!
I.E. I want to see a report of a city name (having the country and state cbo=null). I have one table as a database.
Matt Schumann will need to think about this. Because if first box is Null, what will it filter in the 2nd box? I suppose you could have a record in the table for 1st box, which will be empty, but than primary key cannot be empty :) You could write a VB code to make it happen, but I have not clue about it at this point.
In response to you i.e. you could create a query, and in the criteria of the field that you want empty, enter expressions IsNull. Now create a Report based on this query. If you want to use the Form fields with the query, now modify the query and Point it to the field on the form. And create a button to open the Report on that 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?
Was Country a long text? if it was that is the reason it did not show up initially.
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.
Sir i like your videos!!! I Have a question regarding the query, in the video you created two query which rely on cbocountry and cbostate in frmcustomer but if you create another form say frmfacility and place combo for ctry,state & city and call the query it doesnt work.as the qry is looking in form customer where it should look in form facility and cbocountry.... so how to solve this ? Also plz tell if this experssion is possible [Forms]![Current Form]![Cbocountry] ...plz reply .... thanks for the good tutorials ...god bless you
create a another query for that form
Amir Parmar what i did is in parameter of query instead of writing [forms]![frmxyz]![cbostate] i wrote [cbostate] and it works in multiple forms...... also now i am stuck with the search txt box and the button ur macro is right but its not showing the results..... anyway thanks for the reply keep up the good work
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