Blank combo box? (3 boxes, choices based on previous box entry)

[Guest]

I'm trying to create a form in which there are 3 combo boxes. In the first
one (cboMain), the user chooses a issue (such as Merchandise). Then the
choices in the 2nd combo box (cboSub) reflect only the choices that are
related to Merchandise (such as Size). The 3rd combo box (cboDetail) should
reflect the choices available in for that sub-issue (such as 14/16).

With my limited self-taught knowledge of Access and a lot of web searching,
I was able to get the 2nd box to display the correct choices based on the
choice in the 2st box. However, when I repeated the process, the 3rd box
continues to offer no choices (just a blank line).

I have 3 tables (tblMainItems, tblSubItems, and tblDetail), each of which
have Primary Keys set for each choice listed. The table fields are:
tblMainItems: MainID (primary key), MainName
tblSubItems: SubID (primary key), SubName, MainID
tblDetail: DetailID (primary key), DetailName, SubID, MainID

I have made relationships between all the fields of the same names in the
different tables. I don't know if that makes a difference, but it didn't fix
the problem.

The RowSource for cboSub (the one that is operating properly) is:
SELECT DISTINCTROW [tblSubItems].[SubID], [tblSubItems].[SubName],
[tblSubItems].[MainID] FROM tblSubItems WHERE
((([tblSubItems].[MainID])=[forms]![frmCustomerIssuesLog]![cboMain])) ORDER
BY [tblSubItems].[SubName];

The RowSource for cboDetails (the one that is blank) is:
SELECT DISTINCTROW tblDetail.DetailID, tblDetail.DetailName, tblDetail.SubID
FROM tblDetail WHERE
(((tblDetail.SubID)=[Forms]![frmCustomerIssuesLog]![cboSub])) ORDER BY
tblDetail.DetailName;

I'm hoping someone can decipher my inexperienced explanation and help me set
up the 3rd combo box. Thanks!

 

[tina]

well, i doubt that it's contributing to the problem, but you don't need to
link tblDetail to tblMainItems. each detail record is already indirectly
linked to a main item record via the associated sub item record. recommend
you remove the MainID field from tblDetail.

i'm questioning why you need DISTINCTROW in the RowSource of cboSub (or in
the RowSource of cboDetails, for that matter). are the SubName values in
tblSubItems not unique? (and ditto the DetailName values in tblDetail?) and
if not, how do you, as a user, tell them apart?

hth

 

[Guest]

I removed the MainID field from tblDetail as you suggested. The only reason
I had put the MainID field in tblDetail was for my own reference when
entering the initial data.

The Primary Keys in all 3 files are unique, no duplicates allowed. The only
reason I put DISTINCTROW is because the website I was using to write the
RowSource used it. I didn't know what it did exactly, and it didn't seem to
make a difference either way, so I left it in.

Thank you for your help. As you surmised, making the suggested changes did
not solve the issue. Any other thoughts?

well, i doubt that it's contributing to the problem, but you don't need to
link tblDetail to tblMainItems. each detail record is already indirectly
linked to a main item record via the associated sub item record. recommend
you remove the MainID field from tblDetail.

i'm questioning why you need DISTINCTROW in the RowSource of cboSub (or in
the RowSource of cboDetails, for that matter). are the SubName values in
tblSubItems not unique? (and ditto the DetailName values in tblDetail?) and
if not, how do you, as a user, tell them apart?

hth

I'm trying to create a form in which there are 3 combo boxes. In the first
one (cboMain), the user chooses a issue (such as Merchandise). Then the
choices in the 2nd combo box (cboSub) reflect only the choices that are
related to Merchandise (such as Size). The 3rd combo box (cboDetail) should
reflect the choices available in for that sub-issue (such as 14/16).

With my limited self-taught knowledge of Access and a lot of web searching,
I was able to get the 2nd box to display the correct choices based on the
choice in the 2st box. However, when I repeated the process, the 3rd box
continues to offer no choices (just a blank line).

I have 3 tables (tblMainItems, tblSubItems, and tblDetail), each of which
have Primary Keys set for each choice listed. The table fields are:
tblMainItems: MainID (primary key), MainName
tblSubItems: SubID (primary key), SubName, MainID
tblDetail: DetailID (primary key), DetailName, SubID, MainID

I have made relationships between all the fields of the same names in the
different tables. I don't know if that makes a difference, but it didn't fix
the problem.

The RowSource for cboSub (the one that is operating properly) is:
SELECT DISTINCTROW [tblSubItems].[SubID], [tblSubItems].[SubName],
[tblSubItems].[MainID] FROM tblSubItems WHERE
((([tblSubItems].[MainID])=[forms]![frmCustomerIssuesLog]![cboMain])) ORDER
BY [tblSubItems].[SubName];

The RowSource for cboDetails (the one that is blank) is:
SELECT DISTINCTROW tblDetail.DetailID, tblDetail.DetailName, tblDetail.SubID
FROM tblDetail WHERE
(((tblDetail.SubID)=[Forms]![frmCustomerIssuesLog]![cboSub])) ORDER BY
tblDetail.DetailName;

I'm hoping someone can decipher my inexperienced explanation and help me set
up the 3rd combo box. Thanks!

 

[tina]

well, the RowSource SQL you posted for each combo box looked correct to me.
(good job, btw - many people don't provide the necessary information on a
first post; you covered everything very well). is the BoundColumn property
of each combo box control set to 1? if that doesn't solve the problem, i'm
willing to take a look at your db. if you want me to, post back and i'll
tell you how to email it to me.

hth

I removed the MainID field from tblDetail as you suggested. The only reason
I had put the MainID field in tblDetail was for my own reference when
entering the initial data.

The Primary Keys in all 3 files are unique, no duplicates allowed. The only
reason I put DISTINCTROW is because the website I was using to write the
RowSource used it. I didn't know what it did exactly, and it didn't seem to
make a difference either way, so I left it in.

Thank you for your help. As you surmised, making the suggested changes did
not solve the issue. Any other thoughts?

well, i doubt that it's contributing to the problem, but you don't need to
link tblDetail to tblMainItems. each detail record is already indirectly
linked to a main item record via the associated sub item record. recommend
you remove the MainID field from tblDetail.

i'm questioning why you need DISTINCTROW in the RowSource of cboSub (or in
the RowSource of cboDetails, for that matter). are the SubName values in
tblSubItems not unique? (and ditto the DetailName values in tblDetail?) and
if not, how do you, as a user, tell them apart?

hth

I'm trying to create a form in which there are 3 combo boxes. In the first
one (cboMain), the user chooses a issue (such as Merchandise). Then the
choices in the 2nd combo box (cboSub) reflect only the choices that are
related to Merchandise (such as Size). The 3rd combo box (cboDetail) should
reflect the choices available in for that sub-issue (such as 14/16).

With my limited self-taught knowledge of Access and a lot of web searching,
I was able to get the 2nd box to display the correct choices based on the
choice in the 2st box. However, when I repeated the process, the 3rd box
continues to offer no choices (just a blank line).

I have 3 tables (tblMainItems, tblSubItems, and tblDetail), each of which
have Primary Keys set for each choice listed. The table fields are:
tblMainItems: MainID (primary key), MainName
tblSubItems: SubID (primary key), SubName, MainID
tblDetail: DetailID (primary key), DetailName, SubID, MainID

I have made relationships between all the fields of the same names in the
different tables. I don't know if that makes a difference, but it

didn't
fix

the problem.

The RowSource for cboSub (the one that is operating properly) is:
SELECT DISTINCTROW [tblSubItems].[SubID], [tblSubItems].[SubName],
[tblSubItems].[MainID] FROM tblSubItems WHERE
((([tblSubItems].[MainID])=[forms]![frmCustomerIssuesLog]![cboMain])) ORDER
BY [tblSubItems].[SubName];

The RowSource for cboDetails (the one that is blank) is:
SELECT DISTINCTROW tblDetail.DetailID, tblDetail.DetailName, tblDetail.SubID
FROM tblDetail WHERE
(((tblDetail.SubID)=[Forms]![frmCustomerIssuesLog]![cboSub])) ORDER BY
tblDetail.DetailName;

I'm hoping someone can decipher my inexperienced explanation and help

me
set

up the 3rd combo box. Thanks!

 

[Guest]

Changing the BoundColumn to 1 in the first combo box actually made the 2nd
box display only a blank line also. However, when I reversed that change,
then changed the BoundColumn to 1 in the 2nd combo box, the 3rd box operated
properly! Hooray! Now I now what control I need to fiddle with the next
time I go to create a similar file.

Thanks so much for your help, Tina!

 

[tina]

you're welcome