First Combo box filter work but not sure of second

[LostBoy]

Hi All,

I have found a lot of info on Combo box in the archive which are very good but some are to advance for me. I tried several options but I had no luck. I was finally able to find a code that I was able to understand completely. Let me explain my situation.

On an unbound form I have created a subform [Employees subform] from tblEmployee. This table contains
EmployeeN, StartDate, Department. On the subform I Have added 2 unbound Combo boxes
CboDepart – Created from table tblDepartment
CboSDate – Created from qry_SDate_Sum. The query is created from [tblEmployee]. It gives a summary of [StartDate], since more that one employee can start on a particular day.

The idea is I from [CboDepart] I can select a particular department and it will list all the employees for the department, then if I select a particular StartDate from [CboSDate] then it will show all employees with the same StartDate for the department.

So far in the AfterUpdate of [CboDepart] I have the following.
Code:
Private Sub CboDepart_AfterUpdate()
Dim strFilter As String

strFilter = "select * from [tblEmployee] WHERE [Department]='" & CboDepart & "';"

Me.RecordSource = strFilter
Me.Requery

End Sub

This works perfectly, where I am stuck is how do I get the second filter [CboSDate] to work. From what I’ve read I think I have to create a link between the combo boxes but I don’t know how. Any guidance would be greatly appreciated.

[atkellyx]

Hi

Try this:


1. Set the data source of the form to tblEmployee

2. In the on_click even of the cboDepart, set the filter of the form AND the rowsource on the cboDate like this:



Private Sub CboDepart_onClick()

Dim strFilter As String

strFilter = "WHERE [Department]='" & CboDepart & "';"

Me.filter = strFilter
Me.filterOn = true

strFilter = "SELECT DISTINCT startDate FROM tblEmployee WHERE Department = '" & me.cboDepart & "'"
me.cboStartDate.rowSource = strFilter

End Sub

3. When a date is selected,use the onClick event in the date combo to re-set the form filter to something like:
Private Sub CboDate_onClick()

Dim strFilter As String

strFilter = "WHERE [Department]='" & CboDepart & "' AND startDate = #" & me.cboDate & "#"

Me.filter = strFilter
Me.filterOn = true

That's a bit rough but should get you going.

cheers

AKC

[LostBoy]

Sorry it took so long to reply, thanks for the info atkellyx, this helps me out.