Absolutely the best. Thank you 10 times over. This may be be overly simple for others but it your explains are thoughtful, complete and and easy to follow. Thanks
It's taken me a couple weeks to finally formulate the right question that could then help Google to find useful help suggestions. Your tutorial is straightforward and clear. Thank you for putting in the time needed to provide such thorough and accessible help!
Hello, and thank you for the video. I've stumbled on the following issue. My form is in datasheet view, and the field that contains the query filtered on the other field in the record only shows up for the record I am currently in (and all other records where the parameters are the same). But all the other records show this field as blank. Then, when I switch to another record, the correct value shows up only in the newly selected record and disappears from the previous one I was in. Would you happen to know how to fix it so the field shows its respective result in all records simultaeneously, as in your example?
Thanks a bunch 4 this amazing tutorial it was really helpful. I have 1 question when we are basing our new tables off of a query can we do it based off another table like a table which would be holding Laptop, PC, mobiles as in that case all the values would be covered. In our e.g. if all types of electronics were not in our data set the combobox list would be incomplete for for e.g. 'smart watch' is a part of data but a record has not been created yet.(Actually i do recall u mention using a table instead too wen we get to that part). So your insight on this would be helpful. So each combo boxes can be a predefined table with each value as a record & as we add a new record this list gets updated too in case of a new entry to the dropdown values.
How do you annotate your screen with the pink arrows? Is it separate software or built into Windows? (I assume you are using Windows Magnifier to zoom in and out.) Your technique using zoom and annotation is very clear---along with your content and word choice. Thank you for the excellent content.
Thanks for the comment I use the ZoomIT 3rd party software to annotate. I have a video on how to use this in conjunction with Teams. czcams.com/video/iKBXR0GP0rs/video.html
This is great and just what i have been looking for! It looks like it is working and when i tested it in a db that i had set up just to test it worked great! Now when i try to incorporate it into an existing db that i am add features too, it still looks like this part is working but i get an error after selecting. "The current field must match the join key'?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table." my db has multiple tables, "job", "line items", now i am trying to add a table to track time on the line items. creating a time sheet form with a drop down for the job number and another drop down for the line items that only show the line items that are a part of that job. Please let me know if you have any idea on why i am getting this error.
You need to ensure that whatever field you're using in the dropdown list is the same as the one you're using to add to the parent table. So in the example above if I was going to relate the device type to a customer I would need to ensure that the customer record already existed before I added the device type. In the tutorial the primary keys of the parent tables are also the ones that I use in the list. Hope this makes sense. I have a tutorial on relationships: czcams.com/video/Q6D8W13mFmM/video.html
@@computertutoring I am sorry i have reviewed my relationships and i don't think that is the issue i am having. Is it possible i could email you for assistance?
@@godfish411 Hi sorry we only offer support for customers under our 3 month support; details on our website. However, do double check your relationships and in particular "how data is being entered" into your tables. Data "Must" be entered into the parent table first. Try putting your data in manually making sure the relationships work then start entering data using forms. Hope this helps, we do offer Access courses - www.computertutoring.co.uk/microsoft-access-2016-training-course-beginners/343/
How can we make drop down list from Table field rather than Forms, for example if you make relationship by search wizard you can find query from there, can we make it from there better than Forms. Because if you delete the form you will lost everything but if you build it from table it will remain forever. If you like my idea plz make a video on how to filter query by another query in the relationship its selfies
Hi I tend to just use tables for storing rather than entering data. So then I only add the drop down lists on the forms when I need them and make sure I don’t delete the form. However, if loads like your suggestion I will create a tutorial showing how to add the filter query to the properties on the table instead of a form.
It worked great . . . until I added the form as a subform in within a tab on my navigational control. Then it didn't work. [many hours later . . . ] I just learned, if you want to make your form a subform, make sure you go back into the builder and modify it accordingly. For my scenario, instead of starting with [forms], once I browsed to where my navigational control form was and found my subform, it input the language as follows: [Forms]![InputForm]![NavigationSubform]. (then the rest after that point was the same)
This is very informative. I have a question and I hope you can address it. Query into table is executed only once making it pretty static, is there a way to make this dynamic?
Not sure what you mean. You could place the execution on Different events? I think one of the answers in this I gave before shows how. Sorry if that's not what you're looking for.
Thank you for the files for exercise. I have two ways to update the database depending on the circumstances and need. Updating by clicking on the combobox or when external massive data that we need to import where has countries name and clients but does not provide the nationality or race-group. So in that case we don't want to click on the combo box hundred times to input the race-group name for each related country. How we can resolve this. I did the dlookup after update and works wonderful when updating inside the table one row at a time when clicking on the country, the blank racegroup column populates the blank racegroup column. But I need to import massive external data, it says that can't import because it will violate this and that or this and that. There is no point to create dlookup and afterupdate if we can't import. The whole idea and purpose to import is to avoid clicks and typing. Appreciate your solution.
Sir please tell mi how create class table table value like class name one two three etc make home page combo box and we chabge combo box value then also,change class student form meanse we create student databased when we change combo box value also change form value like one two form
Hi the video really helped. I’ve seen another comment about doing this within tables instead of forms - is that possible as that would be a lot easier for the database I am using. Any advice on how to do it - I’ve gotten part of the way however in the table it asks for a parameter value and then shows the filtered list in a second drop down menu
Hey, I have an issue where my dropdown box works when I do not bind the first box to it. After I add the expression to look for the (Filter Input) from the first box, the second one just show me an empty field. My first box on my form is successfully pulling the Item names from table: Items. First table look something like this: and saving it to Item_name on the form. Table: Item Info Item_1_name category More info Item_2_name category more info The second table looks like this ingredient_1 compatible_item_1 more info ingredient_2 compatible_item_2 more info ingredient_3 compatible_item_1 more info I use a query to filter the items from second crate as follow Ingredient_Name Compatible_Item I have two combo boxes on my form Combo1 - Item_Name (Successful) Combo 2 - Ingredient Now it works if there is no criteria on the query. When i open combo 2, it shows all the ingredients when I add [Forms]![Item Info]![Combo1] to the criteria, it returns an empty field. to filter by item name, I get an empty field. Just as a heads up, these are made up information and my database looks very different from this as well as naming, This is just purely demonstration to give you an idea what Im trying to do. Any advice?
Looks like you've done everything correct and the you've followed the above tutorial precisely, sometimes I find when there are double square brackets around the first object type Forms the query can't find the box. I noticed in mine at timestamp 18:45 the first Forms doesn't have the square brackets try Forms![Item Info]![Combo1] Hope this helps if you haven't already solved it already. Let me know if you find a solution
Great tutorial, very useful. One question though. I've got a form that pulls in data from three tables - Products, Components and Component Types. Both Products and Components have a Name column and a Component Type column, linking back to Component Types. But Products has an additional ID column, which is the primary key. What's odd is when I use the method in this tutorial and select a Component Type from the form dropdown, both the Components and Products dropdowns filter accordingly - but the datasheet shows blanks for any previously entered Product that doesn't belong to the currently selected Component Type. The values seem to be stored correctly in the underlying table, but it makes the form very difficult to audit. Any ideas? Something to do with having a primary key column perhaps?
My best guess is that it would be an issue with the event. Once you've created your 3rd drop down you could add mcrRefresh to it's OnClick event. That way when you click on the dropdown list it should update regards of whether is a current or previous record. I've added a sample database at the bottom of this webpage: www.computertutoring.co.uk/filter-dropdown-ms-access.asp
similar situation. 3 tables related with foreign keys. randomly it's only making a horrible mess with data on the related tables, filtering nothing. or it's filtering. with just one value, regardless what you choose in previous combo. if refresh is on the first combo the filtering is done by first value. if refresh is put on click at second combo the filter value is the second (in my case i have 2 values resulted from first combo). if needed more bs result: even when obtaining the desired filtered values - they are listed but not selectable.
Also is it possible to automatically update new devices, so that if you in the form add "Console" "PS4" or delete "iPad" then it would automatically add or delete them from the device types and subtype tables?
Yes although the preferred way would be to create a device table and then form a relationship, you could also run an update query. czcams.com/video/Q6D8W13mFmM/video.html
Thanks for the video. It is helpful and I successfully could do a practice. Here I am facing a challenge when I tried to use the form as a sub-form inside another form. The second combobox couldn't find the value of previous combobox and asking for a parameter value. Please help. Thanks,
It didn't work for me... :( My guess is that I used vinculated tables instead of native access tables. And it is not possible to set referencial integrity in vinculated tables. So sad...
One problem though, if I select laptop at one row and then select sub-type at a different row, it will still think it's a laptop even though the type is something else, say mobile phone. It remembers the last updated type, but not necessarily the type of the selected row.
Thank you for the reply. I did create a refresh macro and toggled it to the after update and it works as such. However it seems to only remember the updated device and not the specific row. So when I update a device to laptop, perhaps Lenovo and then want to change the subtype of a mobile phone from iPhone to eg motorola, it still thinks it's a laptop, because that's the latest update. I would have to refresh the device type to mobile phone. Is there a way to update the table so whenever I click it knows which row to belong to? I tried with changing the event to onclick, but that did not help.
@@Detteermiig sorry not sure why it's not working should update with the refresh. either clicking the button or running the macro. It needs to refresh to update the query. You could try different events but other than that not sure what to suggest.
@@Detteermiig mcrRefresh bound to the listbox device_subtype.onEnter event (and only there) will do the job. However, 'Refresh' will probably refresh everything on the form which could become time expensive on much larger and complex forms. So I'd prefer only refreshing the listbox via VBA event handler.
Thank you for the video and tutorial. Much appreciated. Here's what I am trying to do. Maybe you can help. (small snippet of Tables below) I need to assign print requests for a county gov't. for a budget. I think I can take what I've learned and apply it to another layer of filtered selections (ie. select DIVISION, have a choice of SUB-DIVISIONS, and then choose the CONTACT person. However, I don't see how to apply the CONTACT selection to the work order. Can you point me in the right direction Thank you. ID Department Division Sub Division Contact 41 Planning RCE FHS Doe, Jane 39 Planning RCE Administration Smith, Mike 38 Planning RCE Administration Vande, Cathy 28 Finance Tax Board Administration Schenk, Veronica
Solution for 3 cascading combo boxes. Once you've created your 3rd drop down you could add mcrRefresh to it's OnClick event. That way when you click on the dropdown list it should update regards of whether is a current or previous record. I've added a sample database at the bottom of this webpage: www.computertutoring.co.uk/filter-dropdown-ms-access.asp
You are a very thorough teacher, and this Video really is amazing. I hope you will continue to help the needed. Thanks
Thanks so much - means a lot!
I've looked forward this for so long. Your amazing class shortened my journey half way to complete my pros. Thank you so much from Vietnam
Thanks so much for saying
Absolutely the best. Thank you 10 times over. This may be be overly simple for others but it your explains are thoughtful, complete and and easy to follow. Thanks
Really appreciate your comment on this
I can't tell you how long I've wanted to know how to do this. Great step by step instructions!
Thanks! Been a long time since I've worked with Access. Glad to hear some are still using 😃
Amazing tutorial, a bit slow, but super thorough. Worth every second of it!
Thanks wanted to take it step by step so it was clear
It's taken me a couple weeks to finally formulate the right question that could then help Google to find useful help suggestions. Your tutorial is straightforward and clear. Thank you for putting in the time needed to provide such thorough and accessible help!
Glad you found me in the end. 🔎
Thank you so much! I watched several videos trying to do this and only yours worked for me. Thank you for saving me weeks of work!
So pleased!
Thank you so much for this video, so helpful.
Glad it was so helpful 😍
Excellent instruction!!!!!! Thanks
Thanks for the video. It had precisely what I was looking for!
No problem!
Sir very helpful, thanks for guide.
Glad the tutorial helped 😊
very good teacher, thanks
Thanks a lot for this very helpful tutorial.
Thank you for the comment
Start at minute 15:00 if you are familiar with access and you just need to know the execution for implementation. - Thanks for sharing.
Good tip
Very helpful thank you!
Hello, and thank you for the video. I've stumbled on the following issue. My form is in datasheet view, and the field that contains the query filtered on the other field in the record only shows up for the record I am currently in (and all other records where the parameters are the same). But all the other records show this field as blank. Then, when I switch to another record, the correct value shows up only in the newly selected record and disappears from the previous one I was in. Would you happen to know how to fix it so the field shows its respective result in all records simultaeneously, as in your example?
Very thanks for your advice...
Thanks a bunch 4 this amazing tutorial it was really helpful. I have 1 question when we are basing our new tables off of a query can we do it based off another table like a table which would be holding Laptop, PC, mobiles as in that case all the values would be covered. In our e.g. if all types of electronics were not in our data set the combobox list would be incomplete for for e.g. 'smart watch' is a part of data but a record has not been created yet.(Actually i do recall u mention using a table instead too wen we get to that part). So your insight on this would be helpful. So each combo boxes can be a predefined table with each value as a record & as we add a new record this list gets updated too in case of a new entry to the dropdown values.
How do you annotate your screen with the pink arrows? Is it separate software or built into Windows? (I assume you are using Windows Magnifier to zoom in and out.) Your technique using zoom and annotation is very clear---along with your content and word choice. Thank you for the excellent content.
Thanks for the comment I use the ZoomIT 3rd party software to annotate. I have a video on how to use this in conjunction with Teams. czcams.com/video/iKBXR0GP0rs/video.html
Thanks Tom Hanks!
😅
This is great and just what i have been looking for! It looks like it is working and when i tested it in a db that i had set up just to test it worked great! Now when i try to incorporate it into an existing db that i am add features too, it still looks like this part is working but i get an error after selecting. "The current field must match the join key'?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table."
my db has multiple tables, "job", "line items", now i am trying to add a table to track time on the line items. creating a time sheet form with a drop down for the job number and another drop down for the line items that only show the line items that are a part of that job. Please let me know if you have any idea on why i am getting this error.
You need to ensure that whatever field you're using in the dropdown list is the same as the one you're using to add to the parent table. So in the example above if I was going to relate the device type to a customer I would need to ensure that the customer record already existed before I added the device type. In the tutorial the primary keys of the parent tables are also the ones that I use in the list. Hope this makes sense. I have a tutorial on relationships: czcams.com/video/Q6D8W13mFmM/video.html
@@computertutoring I am sorry i have reviewed my relationships and i don't think that is the issue i am having. Is it possible i could email you for assistance?
@@godfish411 Hi sorry we only offer support for customers under our 3 month support; details on our website. However, do double check your relationships and in particular "how data is being entered" into your tables. Data "Must" be entered into the parent table first. Try putting your data in manually making sure the relationships work then start entering data using forms. Hope this helps, we do offer Access courses - www.computertutoring.co.uk/microsoft-access-2016-training-course-beginners/343/
How can we make drop down list from Table field rather than Forms, for example if you make relationship by search wizard you can find query from there, can we make it from there better than Forms. Because if you delete the form you will lost everything but if you build it from table it will remain forever. If you like my idea plz make a video on how to filter query by another query in the relationship its selfies
Hi I tend to just use tables for storing rather than entering data. So then I only add the drop down lists on the forms when I need them and make sure I don’t delete the form. However, if loads like your suggestion I will create a tutorial showing how to add the filter query to the properties on the table instead of a form.
@@computertutoring please show me how. Thanks indeed
It worked great . . . until I added the form as a subform in within a tab on my navigational control. Then it didn't work. [many hours later . . . ] I just learned, if you want to make your form a subform, make sure you go back into the builder and modify it accordingly. For my scenario, instead of starting with [forms], once I browsed to where my navigational control form was and found my subform, it input the language as follows: [Forms]![InputForm]![NavigationSubform]. (then the rest after that point was the same)
Well done on working that out. Thanks for posting. Sure this will help loads.
When I do this is filters the list by the first record, not the current record. What could I be doing wrong?
This is very informative. I have a question and I hope you can address it. Query into table is executed only once making it pretty static, is there a way to make this dynamic?
Not sure what you mean. You could place the execution on Different events? I think one of the answers in this I gave before shows how. Sorry if that's not what you're looking for.
Thank you for the files for exercise. I have two ways to update the database depending on the circumstances and need. Updating by clicking on the combobox or when external massive data that we need to import where has countries name and clients but does not provide the nationality or race-group. So in that case we don't want to click on the combo box hundred times to input the race-group name for each related country. How we can resolve this. I did the dlookup after update and works wonderful when updating inside the table one row at a time when clicking on the country, the blank racegroup column populates the blank racegroup column. But I need to import massive external data, it says that can't import because it will violate this and that or this and that. There is no point to create dlookup and afterupdate if we can't import. The whole idea and purpose to import is to avoid clicks and typing. Appreciate your solution.
Sir
please
tell mi
how create class table table value like class name one two three etc
make home page combo box
and we chabge combo box value then also,change class student form
meanse
we create student databased
when we change combo box value also change form value like one two form
We're currently running an Excel series. Might look at Access again next year
Hi the video really helped. I’ve seen another comment about doing this within tables instead of forms - is that possible as that would be a lot easier for the database I am using. Any advice on how to do it - I’ve gotten part of the way however in the table it asks for a parameter value and then shows the filtered list in a second drop down menu
Good question and I'm afraid no I don't know of a way to do it with tables. I always do my interactions with my tables through forms.
Hey, I have an issue where my dropdown box works when I do not bind the first box to it. After I add the expression to look for the (Filter Input) from the first box, the second one just show me an empty field.
My first box on my form is successfully pulling the Item names from table: Items.
First table look something like this: and saving it to Item_name on the form.
Table: Item Info
Item_1_name category More info
Item_2_name category more info
The second table looks like this
ingredient_1 compatible_item_1 more info
ingredient_2 compatible_item_2 more info
ingredient_3 compatible_item_1 more info
I use a query to filter the items from second crate as follow
Ingredient_Name Compatible_Item
I have two combo boxes on my form
Combo1 - Item_Name (Successful)
Combo 2 - Ingredient
Now it works if there is no criteria on the query. When i open combo 2, it shows all the ingredients
when I add
[Forms]![Item Info]![Combo1] to the criteria, it returns an empty field. to filter by item name, I get an empty field.
Just as a heads up, these are made up information and my database looks very different from this as well as naming, This is just purely demonstration to give you an idea what Im trying to do.
Any advice?
Looks like you've done everything correct and the you've followed the above tutorial precisely, sometimes I find when there are double square brackets around the first object type Forms the query can't find the box. I noticed in mine at timestamp 18:45 the first Forms doesn't have the square brackets try Forms![Item Info]![Combo1]
Hope this helps if you haven't already solved it already. Let me know if you find a solution
Hello, this procedure is ok for Access 2019?
Although I used Access 2016 this Access tutorial should work for all versions of Microsoft Access. Let me know how it goes.
It works perfectly! Thank you!
So glad 😊. Thanks for letting me know.
Great tutorial, very useful. One question though. I've got a form that pulls in data from three tables - Products, Components and Component Types. Both Products and Components have a Name column and a Component Type column, linking back to Component Types. But Products has an additional ID column, which is the primary key. What's odd is when I use the method in this tutorial and select a Component Type from the form dropdown, both the Components and Products dropdowns filter accordingly - but the datasheet shows blanks for any previously entered Product that doesn't belong to the currently selected Component Type. The values seem to be stored correctly in the underlying table, but it makes the form very difficult to audit.
Any ideas? Something to do with having a primary key column perhaps?
My best guess is that it would be an issue with the event. Once you've created your 3rd drop down you could add mcrRefresh to it's OnClick event. That way when you click on the dropdown list it should update regards of whether is a current or previous record. I've added a sample database at the bottom of this webpage: www.computertutoring.co.uk/filter-dropdown-ms-access.asp
similar situation. 3 tables related with foreign keys.
randomly it's only making a horrible mess with data on the related tables, filtering nothing.
or it's filtering. with just one value, regardless what you choose in previous combo. if refresh is on the first combo the filtering is done by first value. if refresh is put on click at second combo the filter value is the second (in my case i have 2 values resulted from first combo).
if needed more bs result: even when obtaining the desired filtered values - they are listed but not selectable.
Also is it possible to automatically update new devices, so that if you in the form add "Console" "PS4" or delete "iPad" then it would automatically add or delete them from the device types and subtype tables?
Yes although the preferred way would be to create a device table and then form a relationship, you could also run an update query. czcams.com/video/Q6D8W13mFmM/video.html
I tried fiddling with the relationships, but I don't think I fully understand it. I will definitely see the video you linked to 🙂
Thanks for the video. It is helpful and I successfully could do a practice. Here I am facing a challenge when I tried to use the form as a sub-form inside another form. The second combobox couldn't find the value of previous combobox and asking for a parameter value. Please help. Thanks,
Should work inside another form. However, if you have linking from the external form to the subform you'll have issues.
It didn't work for me... :(
My guess is that I used vinculated tables instead of native access tables. And it is not possible to set referencial integrity in vinculated tables. So sad...
Wow vinculated tables that's a word I had to look up. 😅 you've completely lost me, but sad it didn't work for you 😔
One problem though, if I select laptop at one row and then select sub-type at a different row, it will still think it's a laptop even though the type is something else, say mobile phone. It remembers the last updated type, but not necessarily the type of the selected row.
The refresh macro should take take of that timestamp 20:20 of the above tutorial. 🤔
Thank you for the reply. I did create a refresh macro and toggled it to the after update and it works as such. However it seems to only remember the updated device and not the specific row. So when I update a device to laptop, perhaps Lenovo and then want to change the subtype of a mobile phone from iPhone to eg motorola, it still thinks it's a laptop, because that's the latest update. I would have to refresh the device type to mobile phone.
Is there a way to update the table so whenever I click it knows which row to belong to? I tried with changing the event to onclick, but that did not help.
@@Detteermiig sorry not sure why it's not working should update with the refresh. either clicking the button or running the macro. It needs to refresh to update the query. You could try different events but other than that not sure what to suggest.
@@Detteermiig mcrRefresh bound to the listbox device_subtype.onEnter event (and only there) will do the job.
However, 'Refresh' will probably refresh everything on the form which could become time expensive on much larger and complex forms.
So I'd prefer only refreshing the listbox via VBA event handler.
Thank you for the video and tutorial. Much appreciated.
Here's what I am trying to do. Maybe you can help. (small snippet of Tables below)
I need to assign print requests for a county gov't. for a budget. I think I can take what I've learned and apply it to another layer of filtered selections (ie. select DIVISION, have a choice of SUB-DIVISIONS, and then choose the CONTACT person. However, I don't see how to apply the CONTACT selection to the work order.
Can you point me in the right direction Thank you.
ID Department Division Sub Division Contact
41 Planning RCE FHS Doe, Jane
39 Planning RCE Administration Smith, Mike
38 Planning RCE Administration Vande, Cathy
28 Finance Tax Board Administration Schenk, Veronica
Might wanna look at dynamic filtered drop down lists. Usually accomplished with the indirect function and named ranges
I need 3 cascading combo boxes. Do you have a video on that
Not at moment but it's been a year since I got back to you sorry but will be start some more Access Vids next year
Please, Did you find a solution for 3 drop-down lists?
@@belkessasaid315 Not yet sorry been busy preparing courses for a client
Solution for 3 cascading combo boxes. Once you've created your 3rd drop down you could add mcrRefresh to it's OnClick event. That way when you click on the dropdown list it should update regards of whether is a current or previous record. I've added a sample database at the bottom of this webpage: www.computertutoring.co.uk/filter-dropdown-ms-access.asp
this does not work
Yes it does