problem with dependent combo boxes

[MeredithS]

I've read the threads on the above -- and posted this problem in another
user's group -- but still can't get it to work. It's on an intake form for
hospital admission. Tbl structure involved in this part is:
Tbl ReferralSourcesAll
ReferralID (autonumber)
ReferralType (text, e.g., hospital, legal, school, etc)
Referral (text,e.g., specific referring entity)

I want 2 boxes -- the 1st to just display the ReferralTypes; the 2nd to
allow input of a specific referring entity based on the results of the 1st. I
can get the 1st box set up, but the 2nd won't work at all, even by itself
(just a list of all the referring entities). I'm assuming I must be setting
properties incorrectly, like bound columns, etc. At this point, I'd settle
for the one combo box with a dropdown list of all referring sources --

Thanks,

Meredith

P.S. I've tried Dev Ashish's code, Ricciardi's from the Knowledge Base, and
the advice of another MVP but am continuing to be stuck.

 

[Jeff Boyce]

Meredith

The first combo box will be based on a query that looks something like
(following is untested psuedo-SQL):

SELECT ReferralType
FROM tblReferralSourcesAll;

The second combo box will be based on a query that looks something like
(...):

SELECT Referral
FROM tblReferralSourcesAll
WHERE ReferralType =
[Forms]![YourIntakeFormName]![cboYourFirstComboBoxName];

The first combo box will need an Event Procedure for the AfterUpdate event,
with something like:
Me!cboYourSecondComboBoxName.Requery

The process works something like this:
1. a choice is selected from the first combo box, selecting a
ReferralType
2. the first combo box's AfterUpdate event fires, causing
3. the second combo box to requery its source, which
4. uses the ReferralType selected in the first combo box to
5. limit the list of possible Referrals in the second combo box.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MeredithS]

I can't believe it, Jeff, after a week spent slogging away unsuccessfully
with this; but, it WORKS!!

One minor addition I'd like to make is to add a choice "All" to the 1st box
so that if the input person doesn't know what category the source comes from,
they can see the whole list. This is the code I put in for that, and it
displays "All" in the 1st combo box, as it should, but nothing comes up in
the 2nd ... ?

This isn't my own code; I was given it elsewhere. I'm assuming I have to do
something with the code in the 2nd box to get it to return all list items if
"All" is the choice? (see below)

Combo Box #1: cboReferralType
SELECT DISTINCT ReferralSourcesAll.ReferralType FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.ReferralType;

Combo Box #2: cboReferral
SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![Intake
Worksheet2]![cboReferralType]="All","",[Referral]=[Forms]![Intake
Worksheet2]![cboReferralType]))=True))
ORDER BY ReferralSourcesAll.Referral;

The 1st code works; the 2nd returns nothing ... ??

Thanks,


Meredith

Meredith

The first combo box will be based on a query that looks something like
(following is untested psuedo-SQL):

SELECT ReferralType
FROM tblReferralSourcesAll;

The second combo box will be based on a query that looks something like
(...):

SELECT Referral
FROM tblReferralSourcesAll
WHERE ReferralType =
[Forms]![YourIntakeFormName]![cboYourFirstComboBoxName];

The first combo box will need an Event Procedure for the AfterUpdate event,
with something like:
Me!cboYourSecondComboBoxName.Requery

The process works something like this:
1. a choice is selected from the first combo box, selecting a
ReferralType
2. the first combo box's AfterUpdate event fires, causing
3. the second combo box to requery its source, which
4. uses the ReferralType selected in the first combo box to
5. limit the list of possible Referrals in the second combo box.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've read the threads on the above -- and posted this problem in another
user's group -- but still can't get it to work. It's on an intake form for
hospital admission. Tbl structure involved in this part is:
Tbl ReferralSourcesAll
ReferralID (autonumber)
ReferralType (text, e.g., hospital, legal, school, etc)
Referral (text,e.g., specific referring entity)

I want 2 boxes -- the 1st to just display the ReferralTypes; the 2nd to
allow input of a specific referring entity based on the results of the
1st. I
can get the 1st box set up, but the 2nd won't work at all, even by itself
(just a list of all the referring entities). I'm assuming I must be
setting
properties incorrectly, like bound columns, etc. At this point, I'd settle
for the one combo box with a dropdown list of all referring sources --

Thanks,

Meredith

P.S. I've tried Dev Ashish's code, Ricciardi's from the Knowledge Base,
and
the advice of another MVP but am continuing to be stuck.

 

[Jeff Boyce]

Meredith

Nothing comes up in the second combobox because there is no ReferralType =
"All".

Now you're getting more complex.

Again, this is untested psuedo-process:

First cbo - AfterUpdate
1) if "All", change the RowSource of cbo#2 to something like:
SELECT * FROM tblYourTable
(note -- no WHERE clause, since you DON'T want it limited to
ReferralType)
or 2) if not "All", change/set the RowSource of cbo#2 to what we had before.

To change the recordsource of cbo#2, using something like:

Me!cbo#2.RowSource = "SELECT * FROM tblYourTable"

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MeredithS]

Thanks, Jeff. I sort of understand what you're saying, but don't understand
where to put what. Are you saying to put all the code for RowSource cbo#2
within the AfterUpdate procedure of cbo#1? An if/then kind of thing? What
happens, if so, to the RowSource query set into cbo#2?

Meredith

Meredith

Nothing comes up in the second combobox because there is no ReferralType =
"All".

Now you're getting more complex.

Again, this is untested psuedo-process:

First cbo - AfterUpdate
1) if "All", change the RowSource of cbo#2 to something like:
SELECT * FROM tblYourTable
(note -- no WHERE clause, since you DON'T want it limited to
ReferralType)
or 2) if not "All", change/set the RowSource of cbo#2 to what we had before.

To change the recordsource of cbo#2, using something like:

Me!cbo#2.RowSource = "SELECT * FROM tblYourTable"

Regards

Jeff Boyce
Microsoft Office/Access MVP

I can't believe it, Jeff, after a week spent slogging away unsuccessfully
with this; but, it WORKS!!

One minor addition I'd like to make is to add a choice "All" to the 1st
box
so that if the input person doesn't know what category the source comes
from,
they can see the whole list. This is the code I put in for that, and it
displays "All" in the 1st combo box, as it should, but nothing comes up in
the 2nd ... ?

This isn't my own code; I was given it elsewhere. I'm assuming I have to
do
something with the code in the 2nd box to get it to return all list items
if
"All" is the choice? (see below)

Combo Box #1: cboReferralType
SELECT DISTINCT ReferralSourcesAll.ReferralType FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.ReferralType;

Combo Box #2: cboReferral
SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![Intake
Worksheet2]![cboReferralType]="All","",[Referral]=[Forms]![Intake
Worksheet2]![cboReferralType]))=True))
ORDER BY ReferralSourcesAll.Referral;

The 1st code works; the 2nd returns nothing ... ??

Thanks,


Meredith

 

[Jeff Boyce]

Meredith

Yes. You want to have Access check what's been selected in cbo#1.

If "All" was selected, you want to use the RowSource for cbo#2 that has no
limitations.

If one of the values was selected, you want to use the variation on the
RowSource for cbo#2 that limits to that selected value.

Use an If... Then... Else... EndIf statement -- Access HELP has examples.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Jeff. I sort of understand what you're saying, but don't
understand
where to put what. Are you saying to put all the code for RowSource cbo#2
within the AfterUpdate procedure of cbo#1? An if/then kind of thing? What
happens, if so, to the RowSource query set into cbo#2?

Meredith

Meredith

Nothing comes up in the second combobox because there is no ReferralType
=
"All".

Now you're getting more complex.

Again, this is untested psuedo-process:

First cbo - AfterUpdate
1) if "All", change the RowSource of cbo#2 to something like:
SELECT * FROM tblYourTable
(note -- no WHERE clause, since you DON'T want it limited to
ReferralType)
or 2) if not "All", change/set the RowSource of cbo#2 to what we had
before.

To change the recordsource of cbo#2, using something like:

Me!cbo#2.RowSource = "SELECT * FROM tblYourTable"

Regards

Jeff Boyce
Microsoft Office/Access MVP

I can't believe it, Jeff, after a week spent slogging away
unsuccessfully
with this; but, it WORKS!!

One minor addition I'd like to make is to add a choice "All" to the 1st
box
so that if the input person doesn't know what category the source comes
from,
they can see the whole list. This is the code I put in for that, and it
displays "All" in the 1st combo box, as it should, but nothing comes up
in
the 2nd ... ?

This isn't my own code; I was given it elsewhere. I'm assuming I have
to
do
something with the code in the 2nd box to get it to return all list
items
if
"All" is the choice? (see below)

Combo Box #1: cboReferralType
SELECT DISTINCT ReferralSourcesAll.ReferralType FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.ReferralType;

Combo Box #2: cboReferral
SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![Intake
Worksheet2]![cboReferralType]="All","",[Referral]=[Forms]![Intake
Worksheet2]![cboReferralType]))=True))
ORDER BY ReferralSourcesAll.Referral;

The 1st code works; the 2nd returns nothing ... ??

Thanks,


Meredith