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

 

[davejam]

hmmm... a biggy!!!

first set the after updates for each combo

private sub cboSelectedRvwDt_afterupdate()
fill_cboSelectedEmpName
fill_cboSelectedReportTitle
end sub
private sub cboSelectedEmpName_afterupdate()
fill_cboSelectedRvwDt
fill_cboSelectedReportTitle
end sub
private sub cboSelectedReportTitle_afterupdate()
fill_cboSelectedRvwDt
fill_cboSelectedEmpName
end sub

'now set what the functions do
function fill_cboSelectedRvwDt()
dim sSql as string, fSql as string, wSql as string, oSql as string

sSql = "SELECT DISTINCT TblTERvwSummary.RvwDt "
fsql = "FROM TblTERvwSummary "
wsql = "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)) "
osql = "ORDER BY TblTERvwSummary.RvwDt "
'thats the initial data sorted now filter it if other combo's are selected
if not isnull(me.cboSelectedEmpName) and me.cboSelectedEmpName <> "" then
wsql = wsql & " AND EmpName = '" & me.cboSelectedEmpName & "' "
if not isnull(me.cboSelectedReportTitle) and me.cboSelectedReportTitle <> "" then
wsql = wsql & " AND ReportTitle = '" & me.cboSelectedReportTitle & "' "
end function


then create the other two functions to reflect the other possibilities and should work a treat

daveJam

even my shrink says its all your f#@/ing fault

 

[davejam]

oops forgot to put in just before the end function

me.cboSelectedRvwDt.rowsource = ssql & fsql & wsql & osql

make sure you leave a " " one space gap at the end of your lines or the ssql will run into the wsql etc etc

daveJam

even my shrink says its all your f#@/ing fault

 

[klindell]

Thanks for the quick response!!! I'll use what you have and test it out.

Kriss