?
Solved

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

Posted on 2014-07-11
10
Medium Priority
?
530 Views
Last Modified: 2014-07-22
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!
0
Comment
Question by:billg7
  • 7
  • 2
10 Comments
 
LVL 1

Author Comment

by:billg7
ID: 40191049
Also, this uses the AfterUpdate() on the category/work code box. Not sure if that matters or not... Thanks again!
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40191232
"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
 
LVL 1

Author Comment

by:billg7
ID: 40191440
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:billg7
ID: 40191571
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
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 750 total points
ID: 40191675
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
 
LVL 1

Author Comment

by:billg7
ID: 40199912
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
 
LVL 1

Author Comment

by:billg7
ID: 40199921
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
 
LVL 1

Accepted Solution

by:
billg7 earned 0 total points
ID: 40202741
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
 
LVL 24

Expert Comment

by:Eirman
ID: 40202884
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
 
LVL 1

Author Closing Comment

by:billg7
ID: 40211072
I found the issue.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question