Cascading combo box data disappearing from form

[Guest]

I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.

 

[SteveS]

Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea
FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category
FROM tbl_Categories_Detail
WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description
FROM tbl_Descriptions_Detail
WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

 

[Guest]

Steve: I see. "Too much of a good thing" in the row sources. I'll try your
suggestions as soon as I get to work to morrow and let you know. Am grateful
for your taking the time to respond to my question.

Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea
FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category
FROM tbl_Categories_Detail
WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description
FROM tbl_Descriptions_Detail
WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

---
bound column 1, Column count 2, Col width 0;0.5.


--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.

 

[SteveS]

OK, let me know if it works.....
--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Steve: I see. "Too much of a good thing" in the row sources. I'll try your
suggestions as soon as I get to work to morrow and let you know. Am grateful
for your taking the time to respond to my question.

:

Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea
FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category
FROM tbl_Categories_Detail
WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description
FROM tbl_Descriptions_Detail
WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

---
bound column 1, Column count 2, Col width 0;0.5.


--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.

 

[Guest]

Steve: I made the changes you recommended in both the form that I'm
developing -- and in a small test database I developed using another copy of
Access on a different machine. In the second DB, i created a new form from
scratch.

Both databases are acting the same -- the selections in cboCategory and
cboDescription show while you're on that record, but when you move to another
record, one or both of the selections are not displayed on the form, even
though the values are in the underlying table.

In all of the instructions I've seen about cascading combos, I've never seen
anything indicating that one needs to requery the form....but, should I do
this? If so, which event should I use. Or do you have some other suggestions?
I'm at a loss.

OK, let me know if it works.....
--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Steve: I see. "Too much of a good thing" in the row sources. I'll try your
suggestions as soon as I get to work to morrow and let you know. Am grateful
for your taking the time to respond to my question.

:

Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea
FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category
FROM tbl_Categories_Detail
WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description
FROM tbl_Descriptions_Detail
WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

---
bound column 1, Column count 2, Col width 0;0.5.


--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


Susan L wrote:

I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.

 

[SteveS]

Susan,

I was going to say everything is working in my test db, but then I changed it to
a continuous form and the category and desc combo boxes went blank.

If the Where clause is deleted from the cboCategory and cboDescription combo
boxes, the combo boxes display correctly. I am at a loss as to why the cascading
combo boxes go blank. Maybe a MVP would know.

As a work around, I would try putting the cascading combo boxes on a data entry
only form. A second form would be used to display the data; a query as the
record source would be the main table and the three detail tables. Add whatever
fields are needed from the main table and the ProgramArea, Category and
Description fields (Not the ID fields) from the detail tables and the data
should show up correctly.

I've always used unbound combo boxes and used code to add the data and another
form for displaying.

Sorry I couldn't be more helpful
--
Steve
--------------------------------
"Veni, Vidi, Velcro"

Steve: I made the changes you recommended in both the form that I'm
developing -- and in a small test database I developed using another copy of
Access on a different machine. In the second DB, i created a new form from
scratch.

Both databases are acting the same -- the selections in cboCategory and
cboDescription show while you're on that record, but when you move to another
record, one or both of the selections are not displayed on the form, even
though the values are in the underlying table.

In all of the instructions I've seen about cascading combos, I've never seen
anything indicating that one needs to requery the form....but, should I do
this? If so, which event should I use. Or do you have some other suggestions?
I'm at a loss.

:

OK, let me know if it works.....
--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Susan L wrote:

Steve: I see. "Too much of a good thing" in the row sources. I'll try your
suggestions as soon as I get to work to morrow and let you know. Am grateful
for your taking the time to respond to my question.

:



Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea

FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category

FROM tbl_Categories_Detail

WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description

FROM tbl_Descriptions_Detail

WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

---
bound column 1, Column count 2, Col width 0;0.5.


--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


Susan L wrote:


I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.

 

[Guest]

Steve: Thanks for giving it your best shot. At least I know I'm not losing my
marbles about the selections disappearing.

I'm not sure the separate form idea will work for my purposes, but maybe I
can base the form containing the combos on a query that also contains the
values, which I need for reporting purposes. Or maybe I can do something like
putting unbound text boxes that pick up the text values on top of the combo
boxes.

At any rate, I certainly appreciate your efforts. I'm going to leave the
question open, in case anyone else has any ideas. Thanks again.

Susan,

I was going to say everything is working in my test db, but then I changed it to
a continuous form and the category and desc combo boxes went blank.

If the Where clause is deleted from the cboCategory and cboDescription combo
boxes, the combo boxes display correctly. I am at a loss as to why the cascading
combo boxes go blank. Maybe a MVP would know.

As a work around, I would try putting the cascading combo boxes on a data entry
only form. A second form would be used to display the data; a query as the
record source would be the main table and the three detail tables. Add whatever
fields are needed from the main table and the ProgramArea, Category and
Description fields (Not the ID fields) from the detail tables and the data
should show up correctly.

I've always used unbound combo boxes and used code to add the data and another
form for displaying.

Sorry I couldn't be more helpful
--
Steve
--------------------------------
"Veni, Vidi, Velcro"

Steve: I made the changes you recommended in both the form that I'm
developing -- and in a small test database I developed using another copy of
Access on a different machine. In the second DB, i created a new form from
scratch.

Both databases are acting the same -- the selections in cboCategory and
cboDescription show while you're on that record, but when you move to another
record, one or both of the selections are not displayed on the form, even
though the values are in the underlying table.

In all of the instructions I've seen about cascading combos, I've never seen
anything indicating that one needs to requery the form....but, should I do
this? If so, which event should I use. Or do you have some other suggestions?
I'm at a loss.

:

OK, let me know if it works.....
--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Susan L wrote:


Steve: I see. "Too much of a good thing" in the row sources. I'll try your
suggestions as soon as I get to work to morrow and let you know. Am grateful
for your taking the time to respond to my question.

:



Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea

FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category

FROM tbl_Categories_Detail

WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description

FROM tbl_Descriptions_Detail

WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

---
bound column 1, Column count 2, Col width 0;0.5.


--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


Susan L wrote:


I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.

 

[Guest]

Steve: I'm still holding my breath, but I think I've solved the problem. Put
a Requery for the combo boxes in the On Current event of the form. It seems
to work!

Susan,

I was going to say everything is working in my test db, but then I changed it to
a continuous form and the category and desc combo boxes went blank.

If the Where clause is deleted from the cboCategory and cboDescription combo
boxes, the combo boxes display correctly. I am at a loss as to why the cascading
combo boxes go blank. Maybe a MVP would know.

As a work around, I would try putting the cascading combo boxes on a data entry
only form. A second form would be used to display the data; a query as the
record source would be the main table and the three detail tables. Add whatever
fields are needed from the main table and the ProgramArea, Category and
Description fields (Not the ID fields) from the detail tables and the data
should show up correctly.

I've always used unbound combo boxes and used code to add the data and another
form for displaying.

Sorry I couldn't be more helpful
--
Steve
--------------------------------
"Veni, Vidi, Velcro"

Steve: I made the changes you recommended in both the form that I'm
developing -- and in a small test database I developed using another copy of
Access on a different machine. In the second DB, i created a new form from
scratch.

Both databases are acting the same -- the selections in cboCategory and
cboDescription show while you're on that record, but when you move to another
record, one or both of the selections are not displayed on the form, even
though the values are in the underlying table.

In all of the instructions I've seen about cascading combos, I've never seen
anything indicating that one needs to requery the form....but, should I do
this? If so, which event should I use. Or do you have some other suggestions?
I'm at a loss.

:

OK, let me know if it works.....
--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Susan L wrote:


Steve: I see. "Too much of a good thing" in the row sources. I'll try your
suggestions as soon as I get to work to morrow and let you know. Am grateful
for your taking the time to respond to my question.

:



Susan,

I think the problem is just the Row Source for the combo boxes. See if these work:


Combo1 cboProgramArea Row Source:
---
SELECT DISTINCT tbl_Program_Areas.ProgramID, tbl_Program_Areas.ProgramArea

FROM tbl_Program_Areas;

---
bound column 1, Column count 2, Col width 0;0.5
---
After Update Event: Me.cboCategory.Requery
Me.cboDescription.Requery

===================================================================

Combo 2 cboCategory Row Source:
---
SELECT DISTINCT tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category

FROM tbl_Categories_Detail

WHERE (((tbl_Categories_Detail.ProgramID)=[forms]![form1].[cboProgramArea]))
ORDER BY tbl_Categories_Detail.Category;

---
bound column 1, Column count 2, Col width 0;0.5.
---
After Update Event: cboDescription.Requery

===================================================================

Combo 3 cboDescription Row Source:
---
SELECT DISTINCT tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description

FROM tbl_Descriptions_Detail

WHERE (((tbl_Descriptions_Detail.CategoryID)=[forms]![form1].[cboCategory]))
ORDER BY tbl_Descriptions_Detail.Description;

---
bound column 1, Column count 2, Col width 0;0.5.


--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)


Susan L wrote:


I have three combo boxes on a form. The first (cboProgramArea) “filtersâ€
values for the second one (cboCategory) and the second one “filters†values
for the third (cboDescription). The problem I’m having is that after
selecting values on one record, and moving to another one and selecting
values on it, the values in the second combo or the third combo – or both
disappear from the previous record(s), although the selected value still
exists in the underlying table. Sometimes, the list for selection in the
third combo is not available; if you reselect in combo 2, the list may (or
may not appear.)

Here’s the set up:

Combo1 cboProgramArea Row Source:
SELECT DISTINCTROW tbl_Program_Areas.ProgramID,
tbl_Program_Areas.ProgramArea FROM tbl_Program_Areas;
Column count 2, bound column 1.

After Update Event: cboCategory.Requery
cboDescription.Requery
(I’ve also tried the requery in the After Update Event of Combo2)

Combo 2 cboCategory Row Source
SELECT DISTINCTROW tbl_Categories_Detail.ProgramID,
tbl_Categories_Detail.CategoryID, tbl_Categories_Detail.Category FROM
tbl_Categories_Detail WHERE
(((tbl_Categories_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea))
ORDER BY tbl_Categories_Detail.Category;

Combo 3 cboDescription Row Source
SELECT DISTINCTROW tbl_Descriptions_Detail.ProgramID,
tbl_Descriptions_Detail.CategoryID, tbl_Descriptions_Detail.DescriptionID,
tbl_Descriptions_Detail.Description FROM tbl_Descriptions_Detail WHERE
(((tbl_Descriptions_Detail.ProgramID)=forms!frm_SR_Data_Entry!cboProgramArea
And
(tbl_Descriptions_Detail.CategoryID)=forms!frm_SR_Data_Entry!cboCategory))
ORDER BY tbl_Descriptions_Detail.Description;

I’m also having other problems: default value of two date fields =Date() –
are filled intermittently – one new record not filled; another new record
filled. A concatenated control concatenates three of four elements – does not
concatenate at all on another record. This control is set to concatenate on
the After Update event of the third combo box noted above.

Here are my questions: 1) is there something wrong with my setup of the
cascading combo boxes? I have tried recreating the combos in a new database
and am experiencing the same issues. Do you have any suggestions?
2) Could the problem be that the form is corrupted? Or maybe my copy of
Access, since I recreated the combo boxes in a new test database and
experienced similar strange behavior. Any help will be appreciated.