Dynamic Data Entry Form - Filtering Lookup Field

[UNC_Access]

Hi! Really need some help here - I think this is pretty basic, but I'm spinning my wheels!


Ultimate Goal

• I want end users to open a data entry form, 1st select a "category" value from a combo box, and 2nd select a "goal" value from a combo box. Immediately after the "category" and "goal" values are selected in the form, they are automatically stored in an underlying table (tbl_goals)

My Problem

• I can't figure out how to have the "goal" combo box only list the goals relevant to the category chosen

Objects I Am Working With

• form_a
• query_a
• table_category (a list of categories)
• table_goal (a list of goals, each associated with a category)

Objects Specifics:

• form_a contains fields from query_a
• query_a (which contains no calculated fields and is updateable) contains fields from table_goal
• form_a, table_goal and query_a have a "category" field and a "goal" field
• In form_a, the "category" field's Control Source is "category"
• In form_a, the "goal" field's Control Source is "goal"
• The "category" field in table_goal is a lookup field, which references a record in the "category" field in table_category
• There is a one-to-many relationship between "category" and "goal" (one category can have multiple goals)

Thanks!

 

[bob fitz]

My Problem
I can't figure out how to have the "goal" combo box only list the goals relevant to the category chosen

Search google or this forum for cascading combo boxes

 

[bob fitz]

Here a link worth viewing: http://www.baldyweb.com/CascadingCombo.htm

 

[UNC_Access]

Thanks for the info!

I have come across cascading combo boxes via Google, but I can't seem to translate the syntax correctly. My combo box for "goals" is "goals", more specifically: [Forms]![form_a]![goals]

How would I write that in the VBE window?

Dim strSource As String

strSource = "SELECT goals" & _
                    "FROM table_goals" & _
                    "WHERE table_goals.category= '" & table_category.ID
Me.[Forms]![form_a]![goals].RowSource = strSource
Me.[Forms]![form_a]![goals] = vbNullString

This is probably way off! :/

 

[shafiq-yasin]

Thanks for the info!

I have come across cascading combo boxes via Google, but I can't seem to translate the syntax correctly. My combo box for "goals" is "goals", more specifically: [Forms]![form_a]![goals]

How would I write that in the VBE window?

Dim strSource As String

strSource = "SELECT goals" & _
                    "FROM table_goals" & _
                    "WHERE table_goals.category= '" & table_category.ID
Me.[Forms]![form_a]![goals].RowSource = strSource
Me.[Forms]![form_a]![goals] = vbNullString

This is probably way off! :/

Very simple one. First create your query in a datasheet form. After that You just create an event procedure on combo category at [after update]. Here chose macro and in macro select open form. But form mode should b hidden means no need to open but just for only action. Save that macro and try it on your form. Here on form select category and after update it will filter your data accordingly then you can see the only goals in combo goal which are related to your selected category.

 

[jdraw]

UNC_Access,

Off topic but relevant to your set up:

When you code SQL within vba, you should place a space at the beginning or end of any string.
So, this

strSource = "SELECT goals" & _
                    "FROM table_goals" & _
                    "WHERE table_goals.category= '" & table_category.ID

should have lead or trailing space like this

strSource = "SELECT goals " & _
                    "FROM table_goals " & _
                    "WHERE table_goals.category= '" & table_category.ID

or this

strSource = " SELECT goals" & _
                    " FROM table_goals " & _
                    " WHERE table_goals.category= '" & table_category.ID

If you test your vba/code with

Debug.print strSource

you will see how "Access" has interpreted your code.

Here is a link to all kinds of info on cascading combos

Good luck.

 

[UNC_Access]

Thanks everyone for the help!

FYI I think I found a super easy solution without having to use VBA:

1. In the RowSource query of the "goal" field in form_a (which is in the Data tab in the Property Sheet of the "goal" combo box), I included the following criteria under the "category" field: [Forms]![form_a]![category]
2. In form_a, in the AfterUpdate event (in the Properties menu for the "category" combo box), I simply embedded a macro: Action: Requery, Control Name: "goal" (the short reference, not the long-form reference of [Forms]![form_a]![goal], as I was encountering issues with the long-form reference)
3. I also embedded the exact same macro to the form OnCurrent event (in the Properties menu for the Form selection type), so that, when I switch records, the "goal" field drop-down will dynamically change (in the event that a "category" was already selected for that record)