Combo Box Result to Filter other combo boxes

[spectraflame]

Currently I have a combo box that list the different departments of our company on a form. I also have other combo boxes on that same form that list things like project number and employee name(s).

I would like to filter out the project number and employee name combo boxes based on the selection of the department combo box.

Ex: I select "Water" from the department combo box. Now the only selections in the project number combo box relate to the water deparment and the employees in the various employee combo boxes are only employees from the water department.

I have a separate table for project numbers that lists the project number, project description, and project department. I also have a separate table for employees that list employee ID, employee name, and employee department. Not sure if this information will be useful or not, but wanted to include it.

Matthew

 

[bat17]

see if this post will point you in the right direction

http://www.mrexcel.com/board2/viewtopic.php?t=36085&highlight=combobox+requery

Peter

 

[spectraflame]

bat17,

After reading that post it made a lot of sense. However in the mean time I found an example, but I wanted to make sure this was a logical way of achieving the same basic result.

I created a Combo box called WOType which points to my employee table and pulls the selections from the Department field of the employee table.

I then created another combo box called Personnel01 and pointed it to the employee table and pulling the employee name and department. For the criteria of the query for the department i keyed in "Forms![WorkOrderfrm]![WOType]" with WorkOrderfrm being the current form name and WOType being the combo box on that form. I then placed the following statement in the On Enter event to refresh the query each time the WOType combo box is changed:

Private Sub Personnel01_Enter()
Me.Personnel01.Requery
End Sub

This seems to do the trick for me, but I wanted to make sure if this was a logical approach or if this solution is going to cause me problems down the road.

Is there anyway to a combo box choose a default value if the value is constantly changing? When a department is selected, a range of employee names will appear in the Personnel01 combo box. Since that value changes based on department, is there any way to have Access choose the first value no matter what it is? The reason for asking is that each service order can have up to 10 employee assigned to it. I was wanting to populate all of the Personnel01 - Personnel10 combox boxes with a selection from that list so I would not have to manually choose the employee each time.

Thanks,
Matthew

 

[bat17]

Glad you have it working

I would use the AfterUpdate event of you WOType so that it only runs when it is updated not just when someone tabs through the records.

The format to set a combox's vale is :-
Me.Personnel01.DefaultValue = "'" & Me.Personnel01.ItemData(0) & "'"
The wrapping in quotes is only necessary if it is a text field.

So you will end up with something like :-
Sub WOType_AfterUpdate()

Me.Personnel01.Requery
Me.Personnel01.DefaultValue = "'" & Me.Personnel01.ItemData(0) & "'"

Me.Personnel02.Requery
Me.Personnel02.DefaultValue = "'" & Me.Personnel01.ItemData(1) & "'"

Me.Personnel03.Requery
Me.Personnel03.DefaultValue = "'" & Me.Personnel01.ItemData(2) & "'"


.
.
.
.


End sub

HTH

Peter

 

[spectraflame]

Peter,

I have been trying to get this to work now for 2 days. It works great if the Personnel box are unbound or not tied to a specific field in a table, but as soon as I try to populate a table with the results, the auto population of the combo boxes stop working.

Any thoughts?
Matthew

 

[spectraflame]

I think I figured out my problem.

I changed the code to the following and it is now working correctly.

Private Sub WOType_AfterUpdate()
Me.Personnel01.Requery
Me.Personnel01.Value = Me.Personnel01.ItemData(0)

Me.Personnel02.Requery
Me.Personnel02.Value = Me.Personnel01.ItemData(1)
End Sub

Thanks again,
Matthew

 

[bat17]

It should populate them but only when you are on a new empty record.

Peter

 

[spectraflame]

That is what I am seeing.

Thanks again.
Matthew

 

[bat17]

just had one thought, if you are using text fields then they will need wraping in quotes

Me.Personnel02.Value = "'" & Me.Personnel01.ItemData(1) & "'"


edit: oops, I mean dont wrap in quotes for numeric data!

Peter

 

[spectraflame]

Is there a reason for the ' Name Here ' ?

It works without them. The field in the table is set to text. I did noticed that when I placed the quotes in the code, the name appeared on the form and the table with the ' Name Here ' visible.

Just wondering if this is something that is needed for further manipulation or reporting on the employee name field.

Thanks,
Matthew