Optional Cascading Combo Box

[DoveArrow]

I have two combo boxes, labeled Department and Major. What I want to
have happen is if the user enters a department code in the Department
combo box, I want the Major combo box to be limited to majors from
that department. However, if there is no department code listed in the
Department combo box, I want the Major combo box to accept all majors,
regardless of department.

This seems like it should be a pretty straightforward process, but I
can't, for the life of me, figure it out. Any help would be
appreciated. Thanks.

 

[Klatuu]

You can use a Where clause in the Major Combo's row source something like this:

Select Major FROM SomeTable WHERE Department LIKE IIf(Me.DeparmentCombo
IS NULL, "*", Me.DeparmentCombo);

 

[DoveArrow]

You can use a Where clause in the Major Combo's row source something likethis:

    Select Major FROM SomeTable WHERE Department LIKE IIf(Me.DeparmentCombo
IS NULL, "*", Me.DeparmentCombo);
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

I tried using the following query, based on your suggestion, as the
RowSource and it still isn't working. it just comes up blank.

SELECT DISTINCT tblProgram.Program, tblProgram.ProgramDescription
FROM tblProgram INNER JOIN jtblProgramCatalogYear ON
tblProgram.Program = jtblProgramCatalogYear.Program
WHERE (((tblProgram.Department) Like IIf([Forms]![frmProgramChange]!
[Department] Is Null,"*",[Forms]![frmProgramChange]![Department])) AND
((jtblProgramCatalogYear.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]));

 

[Klatuu]

Try removing the Where clause completely. If you get a list, then it likely
means there is a syntax error in the Where clause. Nothing jumps out at me,
but I do know that when there is a syntax error in the row source of a combo,
it will not report an error, it will return nothing.
--
Dave Hargis, Microsoft Access MVP

You can use a Where clause in the Major Combo's row source something like this:

Select Major FROM SomeTable WHERE Department LIKE IIf(Me.DeparmentCombo
IS NULL, "*", Me.DeparmentCombo);
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

I tried using the following query, based on your suggestion, as the
RowSource and it still isn't working. it just comes up blank.

SELECT DISTINCT tblProgram.Program, tblProgram.ProgramDescription
FROM tblProgram INNER JOIN jtblProgramCatalogYear ON
tblProgram.Program = jtblProgramCatalogYear.Program
WHERE (((tblProgram.Department) Like IIf([Forms]![frmProgramChange]!
[Department] Is Null,"*",[Forms]![frmProgramChange]![Department])) AND
((jtblProgramCatalogYear.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]));

 

[DoveArrow]

You can use a Where clause in the Major Combo's row source something like this:

    Select Major FROM SomeTable WHERE Department LIKE IIf(Me.DeparmentCombo
IS NULL, "*", Me.DeparmentCombo);

- Show quoted text -

I tried using the following query, based on your suggestion, as the
RowSource and it still isn't working. it just comes up blank.

SELECT DISTINCT tblProgram.Program, tblProgram.ProgramDescription
FROM tblProgram INNER JOIN jtblProgramCatalogYear ON
tblProgram.Program = jtblProgramCatalogYear.Program
WHERE (((tblProgram.Department) Like IIf([Forms]![frmProgramChange]!
[Department] Is Null,"*",[Forms]![frmProgramChange]![Department])) AND
((jtblProgramCatalogYear.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]));- Hide quoted text -

- Show quoted text -

Nevermind. I think I'm getting it figured out.

 

[DoveArrow]

Try removing the Where clause completely.  If you get a list, then it likely
means there is a syntax error in the Where clause.  Nothing jumps out at me,
but I do know that when there is a syntax error in the row source of a combo,
it will not report an error, it will return nothing.
--
Dave Hargis, Microsoft Access MVP

I tried using the following query, based on your suggestion, as the
RowSource and it still isn't working. it just comes up blank.

SELECT DISTINCT tblProgram.Program, tblProgram.ProgramDescription
FROM tblProgram INNER JOIN jtblProgramCatalogYear ON
tblProgram.Program = jtblProgramCatalogYear.Program
WHERE (((tblProgram.Department) Like IIf([Forms]![frmProgramChange]!
[Department] Is Null,"*",[Forms]![frmProgramChange]![Department])) AND
((jtblProgramCatalogYear.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]));- Hide quoted text -

- Show quoted text -

Ugh! I guess I didn't get it figured out. I have tried removing the
WHERE clause, and it works just fine without it. I can also tell it
something like "WHERE tblProgram.Department Like "BUSI" and it works.

 

[DoveArrow]

Try removing the Where clause completely.  If you get a list, then itlikely
means there is a syntax error in the Where clause.  Nothing jumps outat me,
but I do know that when there is a syntax error in the row source of a combo,
it will not report an error, it will return nothing.

You can use a Where clause in the Major Combo's row source something like this:
    Select Major FROM SomeTable WHERE Department LIKE IIf(Me.DeparmentCombo
IS NULL, "*", Me.DeparmentCombo);
--
Dave Hargis, Microsoft Access MVP
:
I have two combo boxes, labeled Department and Major. What I wantto
have happen is if the user enters a department code in the Department
combo box, I want the Major combo box to be limited to majors from
that department. However, if there is no department code listed in the
Department combo box, I want the Major combo box to accept all majors,
regardless of department.
This seems like it should be a pretty straightforward process, but I
can't, for the life of me, figure it out. Any help would be
appreciated. Thanks.- Hide quoted text -
- Show quoted text -
I tried using the following query, based on your suggestion, as the
RowSource and it still isn't working. it just comes up blank.
SELECT DISTINCT tblProgram.Program, tblProgram.ProgramDescription
FROM tblProgram INNER JOIN jtblProgramCatalogYear ON
tblProgram.Program = jtblProgramCatalogYear.Program
WHERE (((tblProgram.Department) Like IIf([Forms]![frmProgramChange]!
[Department] Is Null,"*",[Forms]![frmProgramChange]![Department])) AND
((jtblProgramCatalogYear.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]));- Hide quoted text -

- Show quoted text -

Ugh! I guess I didn't get it figured out. I have tried removing the
WHERE clause, and it works just fine without it. I can also tell it
something like "WHERE tblProgram.Department Like "BUSI" and it works.- Hide quoted text -

- Show quoted text -

Okay, here's what ultimately worked for me.

Private Sub PrimaryMajor_GotFocus()
If Len(Trim(Nz(Me.Department))) = 0 Then
Me.PrimaryMajor.RowSourceType = "Table/Query"
Me.PrimaryMajor.RowSource = "SELECT
jtblProgramCatalogYear.Program, tblProgram.ProgramDescription FROM
tblProgram INNER JOIN jtblProgramCatalogYear ON (tblProgram.Program =
jtblProgramCatalogYear.Program) AND (tblProgram.DegreeType =
jtblProgramCatalogYear.DegreeType) WHERE
(((jtblProgramCatalogYear.CatalogYear) = [Forms]![frmProgramChange]!
[CatalogYear]) And ((jtblProgramCatalogYear.DegreeType) = [Forms]!
[frmProgramChange]![DegreeType])) ORDER BY
tblProgram.ProgramDescription;"
Else
Me.PrimaryMajor.RowSourceType = "Table/Query"
Me.PrimaryMajor.RowSource = "SELECT
jtblProgramCatalogYear.Program, tblProgram.ProgramDescription FROM
tblProgram INNER JOIN jtblProgramCatalogYear ON (tblProgram.Program =
jtblProgramCatalogYear.Program) AND (tblProgram.DegreeType =
jtblProgramCatalogYear.DegreeType) WHERE
(((jtblProgramCatalogYear.CatalogYear)=[Forms]![frmProgramChange]!
[CatalogYear]) AND ((jtblProgramCatalogYear.DegreeType)=[Forms]!
[frmProgramChange]![DegreeType]) AND ((tblProgram.Department)=[Forms]!
[frmProgramChange]![Department])) ORDER BY
tblProgram.ProgramDescription;"
End If
End Sub

I think the problem was that there were so many ways for the combo box
to be empty (it could be null, it could be nonzero, it could be a
space with no text), that the computer just couldn't figure out what
was going on. In any event, like I said, it works now.