Access 2010 - Updating a drop-down list of sub-categories when the parent category is changed.

Hi Experts! I have a timekeeping database that I modified in Access that has a category and a subcategory. The goal is to automatically update the subcategory dropdown list when a user updates the category list to only display the subcategory contents that apply to the parent category value. An example is when I put in 'Admin' in the category, I only want to see things like Sick Leave or Out of Office.

I found a way to do this, but when it does it also hides the values in the subcategory field for other categories that are on the list. It looks like this:
Subcat-problem.jpg
Here's the VB code:

Private Sub Work_Code_AfterUpdate()
On Error Resume Next
Me.[Effort Type].RowSource = "Select [Effort Type].ID,[Effort Type].Description " & _
    "FROM [Effort Type] " & _
    "Where [Effort Type].Parent = " & [Work Code].Value

End Sub

so in this case, Work Code is the parent called Category and Effort Type is subcategory.

So my question is, how do I make it so that it only updates the one row?

Your help is greatly appreciated!
LVL 1
billg7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

billg7Author Commented:
Also, this uses the AfterUpdate() on the category/work code box. Not sure if that matters or not... Thanks again!
0
hnasrCommented:
"So my question is, how do I make it so that it only updates the one row?"

If I understand correctly, it wont update the row. Your code looks OK to populate the subcategory combo. Then you need to manually select the required subcategory, because there are many subcategory values for each category.
0
billg7Author Commented:
Yes, when you're adding or modifying a row that is true and it works find for that. I wasn't really clear in my description, but the problem is not so much that as displaying the other rows.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

billg7Author Commented:
Attached is the actual DB. I had to strip the data first, but I left a month in for myself. If you select the "Clear Filter" item from Filter Favorites (top center of the main menu) the data will show and you will see what I mean.
Timekeeping.accdb
0
hnasrCommented:
Filter is set to ID:4766 and Work Week: 7/11/2014. Hence one record is displayed.

Toggle Filter, shows all records.

You may redefine the filter for required fields and Toggle Filter to see relevant records.

Try and comment.
0
billg7Author Commented:
I don't think this is related to what I am asking. The filter is set to the current month and user when the DB is opened, that's the default. I'm aware of the filters and that they can be cleared and re-defined.

I created the VB code to select the value in the sub-category drop-down menu when the category is changed. The problem is that when the category is changed on one record, the sub-categories disappear for all other categories for all other records, regardless of the filters. So if I change the record to 'admin' category, only admin sub-categories are displayed in the full list.
0
billg7Author Commented:
I'm trying to make it so that only the row where the category is changed has the new query run to create the list for the sub-category. So the other records that are displayed still display the proper values for their respective categories. Hopefully that makes sense... :)
0
billg7Author Commented:
I found the problem! There was a 'where' clause in the SQL lookup on the sub-category. I'm not sure how it got there, but when I removed it, the thing worked properly. I don't remember putting it in there and I hope it doesn't randomly re-appear... At any rate. Issue solved!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EirmanChief Operations ManagerCommented:
Hi billg7.  You may want to consider ....

If you close the question by accepting your own comment at the solution,
the question will not be deleted but will be left here for future reference by others.

(Just a thought)
0
billg7Author Commented:
I found the issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.