A
Ann in CA
Hello,
I understand how to use basic cascading combo boxes, but here's one I can't
figure out:
I want to update the first value, "Program", and have the second combo box
lookup to see if the type has been associated with the File Type or not in a
previous record. If it has, then those values will go to the top of the
second combo, in ascending order; if it has not, those values will still be
in the combo box, but sorted ascending AFTER all the matching values.
Make sense? To give a scenario:
In the record, I see Project ABC. I have a subform that wants to know what
programs & files are associated. in the First combo box, I choose "Excel."
The second dropdown then populates as such:
CSV
XLS
(invisible line here)
DOC
EXE
PSD
Since someone has already associated CSV and XLS with Excel, they move to
the top, but all of the other options are still there. When someone adds EXE
there, it would shift to the top of the combo box next time.
The best non-working concept I've come up with is
Me.DocType.RowSource = "SELECT DISTINCTROW tblFileTypes.FileType, " &
IIf(Nz(DLookup("PF_ID", "tblProgFileLink", strCriteria), 0) = 0, 1, 0) & "" &
_
" FROM tblFileTypes LEFT JOIN tblProgFileLink ON tblFileTypes.FileType=
tblProgFileLink.FileType" & _
"ORDER BY '[Top]' DESC, tblFileTypes.FileType"
where strCriteria = "[Program] = """ & Me.cboProgram & """
But that doesn't work.
Pretty please do not suggest setting up the database differently, these are
not standard programs and the same file extension will be used by different
programs, I'm just trying to give an example to make the question more clear.
Hope someone has a suggestion...thank you!
I understand how to use basic cascading combo boxes, but here's one I can't
figure out:
I want to update the first value, "Program", and have the second combo box
lookup to see if the type has been associated with the File Type or not in a
previous record. If it has, then those values will go to the top of the
second combo, in ascending order; if it has not, those values will still be
in the combo box, but sorted ascending AFTER all the matching values.
Make sense? To give a scenario:
In the record, I see Project ABC. I have a subform that wants to know what
programs & files are associated. in the First combo box, I choose "Excel."
The second dropdown then populates as such:
CSV
XLS
(invisible line here)
DOC
EXE
PSD
Since someone has already associated CSV and XLS with Excel, they move to
the top, but all of the other options are still there. When someone adds EXE
there, it would shift to the top of the combo box next time.
The best non-working concept I've come up with is
Me.DocType.RowSource = "SELECT DISTINCTROW tblFileTypes.FileType, " &
IIf(Nz(DLookup("PF_ID", "tblProgFileLink", strCriteria), 0) = 0, 1, 0) & "" &
_
" FROM tblFileTypes LEFT JOIN tblProgFileLink ON tblFileTypes.FileType=
tblProgFileLink.FileType" & _
"ORDER BY '[Top]' DESC, tblFileTypes.FileType"
where strCriteria = "[Program] = """ & Me.cboProgram & """
But that doesn't work.
Pretty please do not suggest setting up the database differently, these are
not standard programs and the same file extension will be used by different
programs, I'm just trying to give an example to make the question more clear.
Hope someone has a suggestion...thank you!