Unanswered: Using Combo box to filter options in 2nd Combo box
Using Combo box to filter options in 2nd Combo box
Hi there folks!
I have created a form for entering records. The form contains 2 Combo boxes and a text box.
The First Combo box contains a list of "Project Titles" from a table. The user selects one of the Project titles from the list to set everything in motion.
The Text box is supposed to display the corresponding Project Owner automatically (this info is stored on a separate table, linked to the "Project Title" table by a Primary Key ID field).
The Second Combo box is supposed to contain all of the Objectives tied to the Project selected in the first Combo box. The objectives are all stored together on a single table, but contain a Project Title ID field to distinguish which project they are related to.
So when the user selects a value from the 1st Combo box, the text box finds the corresponding owner from the other table using the Project Owner ID linking the two tables. And the second Combo box (becomes enabled) and contains ONLY the specific objectives related to the Project TitleID, allowing the User to select one of those.
In my (I'd like to think) relatively logical way of thinking, this SHOULD work! I think!
I guess I'm asking not only how to limit the objectives available to select in the 2nd box depending on the selection made in the first, BUT ALSO how to reference the actual INFORMATION fields stored in the tables, even though the tables are all being related and referenced by their (otherwise meaningless) ID fields. (The ways I've tried thus far end up with the actual ID number being displayed in the Combo box for the user to select instead of the meaningful info in the other field!!!).
Can anyone give me an idea of how I can accomplish this using VBA in ACCESS 2002?
I'd be very grateful! I've been looking all over for an example of this to look at, but have come up empty so far, and as a newbie I don't have a broad enough knowledge of Access or VB to know how to approach this cold.