Synchronized combo boxes with "All"

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I have a form with two synchronized combo boxes.

The first combo box lists project types (Abstract,
Manuscript, Study, etc.) as well as an "---All---" option.

The second combo box lists projects which correspond to
the project type selected in the first combo box.

The synchronization works except for when "---All---" is
selected in the first combo box, in which case the second
combo box doesn't list anything.

Can someone help me with the SQL to make this work? I
assume it's a matter of handling either the UNION SELECT
statement in the first combo box, and/or the WHERE...
OR... statement in the second combo box. Thanks.

###

First Combo Box:

SELECT tblProjectTypes.ProjectTypeID,
tblProjectTypes.ProjectType
FROM tblProjectTypes
UNION
SELECT '*', '--- ALL ---'
FROM tblProjectTypes
ORDER BY tblProjectTypes.ProjectType;

Second Combo Box:

SELECT DISTINCTROW tblProjects.ProjectID,
tblProjects.Title, tblProjects.ProjectTypeID
FROM tblProjects
WHERE (((tblProjects.ProjectTypeID)=[Forms]![frmProjects]!
[cboProjectType]))
OR (((([tblProjects].[ProjectTypeID]) Like [Forms]!
[frmProjects]![cboProjectType]) Is Null))
ORDER BY tblProjects.Title;

###

Kurt
 
Can you use an IIF statement in the SQL statement for the
second combo box, where if the first combo box =
"---ALL---" then the WHERE statement specifies "LIKE '*'",
else WHERE specifies the value of the first combo box?
 
Back
Top