Form Filter with multiple interdependent combo boxes

[klindell]

I have researched this as much as possible and can't get it to work properly 100% of the time.

I have a DB where a processing group enters T&E report exceptions. I have created a form for them to enter the data. A separate group reviews the T&E exceptions for validity. I have a second form for this that closely mirrors the first form but has three filter combo boxes.

The combo boxes are:
cboSelectedRvwDt - This is the list of dates for which T&E report have been processed.
cboSelectedEmpName - This is the list of employees with T&E reports processed.
cboSelectedReportTitle - This is the list of T&E report titles processed.

For the review form, I want the user to be able to select a value from any combination of the combo boxes. So, if they selected "14-Sep-08" from cboSelectedRvwDt, the cboSelectedEmpName and cboSelectedReportTitle combo boxes would only show the values of T&E reports processed on that date. The same scenario could occur if a user first selects a user they want to review, the cboSelectedRvwDt and cboSelectedReportTitle fields would only show dates and report titles where T&E reports have been processed for the employee.

I can get each combo box to display a full list when no values have been selected in the other prompts. If I select a value in cboSelectedRvwDt, the cboSelecteeEMPName and cboSelectedReportTitle fields limit the list properly most of the time. However, if I select a value from cboSelectedEmpName first, the cboSelectedRvwDt and cboSelectedReportTitle combo boxes sometimes show the full list of dates and report titles from the underlying table instead of only the valid values.

If I would prefer to give the users the flexibility to select the values independently, but will go to the path of forcing them to select a date first, then the name, and finally the report title if I can't get this fixed quickly. I haven't changed the form recordsource to query from the combo boxes yet because I want to make sure I get the combo boxes working first before messing with the form's record source.

Here are the combo box SQL statements for each of the combo boxes:
cboSelectedRvwDt SQL:
SELECT DISTINCT TblTERvwSummary.RvwDt
FROM TblTERvwSummary
WHERE (((TblTERvwSummary.ReportTitle)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedReportTitle]) AND ((TblTERvwSummary.EmpName)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedEmpName])) OR (((TblTERvwSummary.EmpName)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedEmpName]) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSelectedReportTitle]))<>False)) OR (((TblTERvwSummary.ReportTitle)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedReportTitle]) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSeelectedEmpName]))<>False)) OR (((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSelectedReportTitle]))<>False) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSeelectedEmpName]))<>False))
ORDER BY TblTERvwSummary.RvwDt;

cboSelectedEmpName SQL:
SELECT DISTINCT TblTERvwSummary.EmpName
FROM TblTERvwSummary
WHERE (((TblTERvwSummary.RvwDt)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]) AND ((TblTERvwSummary.ReportTitle)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedReportTitle])) OR (((TblTERvwSummary.ReportTitle)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedReportTitle]) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]))<>False)) OR (((TblTERvwSummary.RvwDt)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSeelectedReportTitle]))<>False)) OR (((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]))<>False) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSeelectedReportTitle]))<>False))
ORDER BY TblTERvwSummary.EmpName;

cboSelectedReportTitle SQL:
SELECT DISTINCT TblTERvwSummary.ReportTitle
FROM TblTERvwSummary
WHERE (((TblTERvwSummary.RvwDt)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]) AND ((TblTERvwSummary.EmpName)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedEmpName])) OR (((TblTERvwSummary.EmpName)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedEmpName]) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]))<>False)) OR (((TblTERvwSummary.RvwDt)=[Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSeelectedEmpName]))<>False)) OR (((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSelectedRvwDt]))<>False) AND ((IsNull([Forms]![FrmTERvwSummaryReviewer]![cboSeelectedEmpName]))<>False))
ORDER BY TblTERvwSummary.ReportTitle;

I can upload or email a copy of the DBs (they're split front-end/back-end) if would help anyone troubleshoot this issue.


Thanks,
Kriss

 

[Premy]

Ok, try it the VB way:

Sub cbo1_Afterupdate()
Me.cbo2.Rowsource = "Select ...."
Me.cbo3.Rowsource = "Select ...."
End Sub

Sub cbo2_Afterupdate()
Me.cbo1.Rowsource = "Select ...."
Me.cbo3.Rowsource = "Select ...."
End Sub

Sub cbo3_Afterupdate()
Me.cbo1.Rowsource = "Select ...."
Me.cbo2.Rowsource = "Select ...."
End Sub

HTH

 

[Kiwiman]

Howzit

I tried to find the link to where I got this sample - It was created by Uncle Gismo - but me know not where to find it!!! I'm sure I got it from this site.

So I thought I would post the db instead. This has 3 comboboxes that will filter the results accordingly, selecting a combobox in any order.

Maybe something like this will help...

 

[vicsar]

Howzit

I tried to find the link to where I got this sample - It was created by Uncle Gismo - but me know not where to find it!!! I'm sure I got it from this site.

So I thought I would post the db instead. This has 3 comboboxes that will filter the results accordingly, selecting a combobox in any order.

Maybe something like this will help...

Old thread, I know, but its OK. This is the link to the file mentioned Howzit.
http://www.access-programmers.co.uk/forums/showthread.php?t=170380