Basing One Combo Box on Another - How to add an All Category?

[Guest]

I have 2 combo boxes.
Categories:
A
B
C
D ALL

Jobs
1
2
3

I figured out how to make only the jobs associated with a specific category
come up. Now I need to add to show all the jobs when I select category D. All
JObs. I see how each job is connected to each category thru its own category
code, but if I add another category code for "All Jobs" I will have to add
that to the existing codes that are all ready pulling the correct jobs.

Basically, If I select category D - I want to see all the jobs.

Any suggestions would be greatly appreciated.
Yula

 

[Jeff Boyce]

One approach to this would be to use the AfterUpdate event of your first
combobox to (re-)set the ControlSource property of your second combobox.

In cboFirst you could add a test for whether your "D ALL" was chosen. If it
was, you'd use a SQL statement/query that had no selection criteria.
Otherwise, you'd use a query that relied on the value in the first combobox
to fill the second.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I think I have to do something with the query in the Job Combo box.
This is what I have:

Field: JobCategoryID JobID JobName JesID
Criteria: [forms]![frmJob]![category]

 

[Guest]

Jeff,

Thank you so much for responding, but I am completly not an Access expert. I
am learning by making trial. If you could, please elaborate on the second
paragrapg below. I am not sure how to do this.

Thanks Again,
Yula

 

[Ken Snell \(MVP\)]

Another way would be to use a criterion statement like this in your second
combo box's Row Source query:

WHERE YourFieldName = Forms!FormName!FirstComboBoxName
OR Forms!FormName!FirstComboBoxName = "D ALL"

 

[Jeff Boyce]

You will need to gain some familiarity (?and comfort?) with using VBA
procedures.

First add an Event Procedure to the cboFirst's AfterUpdate event. To test
for "D ALL", you'd need to use an If...Then function, something like:

If Me!cboFirst = "D ALL" Then
Me!cboSecond.RowSource = "The name of your query that gets ALL rows"
Else
Me!cboSecond.RowSource = "The name of your query that selects by
cboFirst"
End If

Regards

Jeff Boyce
Microsoft Office/Access MVP