Combo box data changing

  • Thread starter Thread starter Dan @BCBS
  • Start date Start date
D

Dan @BCBS

My form has a subform (Link Child & Master field = ICNNO).
On that subform are two fields "CA_Name" and "CA_Decision" and they are both
Combo Boxes.

A different list is displayed in "CA_Decision" based on the choice made in
the "CA_Name" field.

This subform is for multiple entries which all pertain to the same ICNNO.
Example: ICNNO = 123
CA_Name = MG
CA_Decision = 1

CA_Name = BH
CA_Decision = 29
Everything is fine to this point:

The problem is when a change is made to "CA_Name" it changes all the
CA_Decision lists on the other entries to the list associated with the
CA_Name just entered.


Private Sub CA_NAME_Change()
If Me.CA_NAME = "MC" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','7','14','22','25')"
ElseIf Me.CA_NAME = "MG" Or Me.CA_NAME = "AG" Or Me.CA_NAME = "TL" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','14','22','31')"
ElseIf Me.CA_NAME = "BH" Or Me.CA_NAME = "BM" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('3','29','32')"
ElseIf Me.CA_NAME = "GC" Then.......

HELP>>>>>>>>> I'm Confused...
 
Dan @BCBS said:
My form has a subform (Link Child & Master field = ICNNO).
On that subform are two fields "CA_Name" and "CA_Decision" and they are both
Combo Boxes.

A different list is displayed in "CA_Decision" based on the choice made in
the "CA_Name" field.

This subform is for multiple entries which all pertain to the same ICNNO.
Example: ICNNO = 123
CA_Name = MG
CA_Decision = 1

CA_Name = BH
CA_Decision = 29
Everything is fine to this point:

The problem is when a change is made to "CA_Name" it changes all the
CA_Decision lists on the other entries to the list associated with the
CA_Name just entered.

Private Sub CA_NAME_Change()
If Me.CA_NAME = "MC" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','7','14','22','25')"
ElseIf Me.CA_NAME = "MG" Or Me.CA_NAME = "AG" Or Me.CA_NAME = "TL" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','14','22','31')"
ElseIf Me.CA_NAME = "BH" Or Me.CA_NAME = "BM" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('3','29','32')"
ElseIf Me.CA_NAME = "GC" Then.......


There is only one combo box so there is only one row source.
That in itself is not the problem. The annoying issue in a
continuous or datasheet form is that the CA_Decision combo
box goes blank on any rows that have a different CA_Name
value. As you've already deduced, that's because the
CA_Decision value is not in the new row source. Regardless
of all that, the data is unaffected and it is only the
appearance that's disconcerting.

To get things to look right is a messy affair, but in this
case, I presume that the value of the CA_DECISION combo box
is saved in the CA_DECISION field in the subform's base
table, which, believe it or not, makes the issue easer to
deal with.

Add a text box to the sunform and bind it to the CA_DECISION
field. Then size and position the text box eactly on top of
the text portion of the combo box. Set the text box's Tab
Stop property to No and add a line of code to its GotFocus
event that immediately switched the focus to the combo box:

Me.CA_DECISION.SetFocus

The combo box will behave normally because a soon as it
receives the focus, it will jump in front of the text box.
On the other records the text box will display the value of
the field.
 
That was very interesting and helpful..

The new text box displays the value saved to the table.
That value is not what is displayed on the form in the combo box.
How can I change the display in the text box.

I have tried to add something like "MD";"Medicare" in the Row Source - but
text boxes don't have a row source. So I tried repeating what you had me do
but with a combo box. It displays the same thing as the text box - When I
add the "MD";"Medicare" in the Row Source it has no affect..

Obviously, my description is Medicare and the stored value is "MD" - any
suggestions on how to display Medicare and not what is being stored in the
table???

Thanks




Marshall Barton said:
Dan @BCBS said:
My form has a subform (Link Child & Master field = ICNNO).
On that subform are two fields "CA_Name" and "CA_Decision" and they are both
Combo Boxes.

A different list is displayed in "CA_Decision" based on the choice made in
the "CA_Name" field.

This subform is for multiple entries which all pertain to the same ICNNO.
Example: ICNNO = 123
CA_Name = MG
CA_Decision = 1

CA_Name = BH
CA_Decision = 29
Everything is fine to this point:

The problem is when a change is made to "CA_Name" it changes all the
CA_Decision lists on the other entries to the list associated with the
CA_Name just entered.

Private Sub CA_NAME_Change()
If Me.CA_NAME = "MC" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','7','14','22','25')"
ElseIf Me.CA_NAME = "MG" Or Me.CA_NAME = "AG" Or Me.CA_NAME = "TL" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','14','22','31')"
ElseIf Me.CA_NAME = "BH" Or Me.CA_NAME = "BM" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('3','29','32')"
ElseIf Me.CA_NAME = "GC" Then.......


There is only one combo box so there is only one row source.
That in itself is not the problem. The annoying issue in a
continuous or datasheet form is that the CA_Decision combo
box goes blank on any rows that have a different CA_Name
value. As you've already deduced, that's because the
CA_Decision value is not in the new row source. Regardless
of all that, the data is unaffected and it is only the
appearance that's disconcerting.

To get things to look right is a messy affair, but in this
case, I presume that the value of the CA_DECISION combo box
is saved in the CA_DECISION field in the subform's base
table, which, believe it or not, makes the issue easer to
deal with.

Add a text box to the sunform and bind it to the CA_DECISION
field. Then size and position the text box eactly on top of
the text portion of the combo box. Set the text box's Tab
Stop property to No and add a line of code to its GotFocus
event that immediately switched the focus to the combo box:

Me.CA_DECISION.SetFocus

The combo box will behave normally because a soon as it
receives the focus, it will jump in front of the text box.
On the other records the text box will display the value of
the field.
 
You do have the messier situation after all.

To deal with it, the form's record source needs to be a
query that Joins the form's base table to the combo box's
row source table and includes the field displayed in the
combo box. The text box should be bound to this field.
--
Marsh
MVP [MS Access]


Dan @BCBS said:
That was very interesting and helpful..

The new text box displays the value saved to the table.
That value is not what is displayed on the form in the combo box.
How can I change the display in the text box.

I have tried to add something like "MD";"Medicare" in the Row Source - but
text boxes don't have a row source. So I tried repeating what you had me do
but with a combo box. It displays the same thing as the text box - When I
add the "MD";"Medicare" in the Row Source it has no affect..

Obviously, my description is Medicare and the stored value is "MD" - any
suggestions on how to display Medicare and not what is being stored in the
table???

Dan @BCBS said:
My form has a subform (Link Child & Master field = ICNNO).
On that subform are two fields "CA_Name" and "CA_Decision" and they are both
Combo Boxes.

A different list is displayed in "CA_Decision" based on the choice made in
the "CA_Name" field.

This subform is for multiple entries which all pertain to the same ICNNO.
Example: ICNNO = 123
CA_Name = MG
CA_Decision = 1

CA_Name = BH
CA_Decision = 29
Everything is fine to this point:

The problem is when a change is made to "CA_Name" it changes all the
CA_Decision lists on the other entries to the list associated with the
CA_Name just entered.

Private Sub CA_NAME_Change()
If Me.CA_NAME = "MC" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','7','14','22','25')"
ElseIf Me.CA_NAME = "MG" Or Me.CA_NAME = "AG" Or Me.CA_NAME = "TL" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','14','22','31')"
ElseIf Me.CA_NAME = "BH" Or Me.CA_NAME = "BM" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('3','29','32')"
ElseIf Me.CA_NAME = "GC" Then.......

Marshall Barton said:
There is only one combo box so there is only one row source.
That in itself is not the problem. The annoying issue in a
continuous or datasheet form is that the CA_Decision combo
box goes blank on any rows that have a different CA_Name
value. As you've already deduced, that's because the
CA_Decision value is not in the new row source. Regardless
of all that, the data is unaffected and it is only the
appearance that's disconcerting.

To get things to look right is a messy affair, but in this
case, I presume that the value of the CA_DECISION combo box
is saved in the CA_DECISION field in the subform's base
table, which, believe it or not, makes the issue easer to
deal with.

Add a text box to the sunform and bind it to the CA_DECISION
field. Then size and position the text box eactly on top of
the text portion of the combo box. Set the text box's Tab
Stop property to No and add a line of code to its GotFocus
event that immediately switched the focus to the combo box:

Me.CA_DECISION.SetFocus

The combo box will behave normally because a soon as it
receives the focus, it will jump in front of the text box.
On the other records the text box will display the value of
the field.
 
I created a query to be used as the forms record source. It is created from
the two tables and it includes the field to be displayed. Then I bound the
text box to this combo box created from the above mentioned query. Result:
The field is to small..

I think you had me do all this thinking if I used a query I could bind the
textbox to the new combo box and it would save the value description to the
table.
But it cannot save the description.

The value has two fields Code and Description.
I need to bind the code but display the description...

So when I do what you have suggested the field on the table is too small to
hold the description!!!

Help









Marshall Barton said:
You do have the messier situation after all.

To deal with it, the form's record source needs to be a
query that Joins the form's base table to the combo box's
row source table and includes the field displayed in the
combo box. The text box should be bound to this field.
--
Marsh
MVP [MS Access]


Dan @BCBS said:
That was very interesting and helpful..

The new text box displays the value saved to the table.
That value is not what is displayed on the form in the combo box.
How can I change the display in the text box.

I have tried to add something like "MD";"Medicare" in the Row Source - but
text boxes don't have a row source. So I tried repeating what you had me do
but with a combo box. It displays the same thing as the text box - When I
add the "MD";"Medicare" in the Row Source it has no affect..

Obviously, my description is Medicare and the stored value is "MD" - any
suggestions on how to display Medicare and not what is being stored in the
table???

My form has a subform (Link Child & Master field = ICNNO).
On that subform are two fields "CA_Name" and "CA_Decision" and they are both
Combo Boxes.

A different list is displayed in "CA_Decision" based on the choice made in
the "CA_Name" field.

This subform is for multiple entries which all pertain to the same ICNNO.
Example: ICNNO = 123
CA_Name = MG
CA_Decision = 1

CA_Name = BH
CA_Decision = 29
Everything is fine to this point:

The problem is when a change is made to "CA_Name" it changes all the
CA_Decision lists on the other entries to the list associated with the
CA_Name just entered.

Private Sub CA_NAME_Change()
If Me.CA_NAME = "MC" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','7','14','22','25')"
ElseIf Me.CA_NAME = "MG" Or Me.CA_NAME = "AG" Or Me.CA_NAME = "TL" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','14','22','31')"
ElseIf Me.CA_NAME = "BH" Or Me.CA_NAME = "BM" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('3','29','32')"
ElseIf Me.CA_NAME = "GC" Then.......

Marshall Barton said:
There is only one combo box so there is only one row source.
That in itself is not the problem. The annoying issue in a
continuous or datasheet form is that the CA_Decision combo
box goes blank on any rows that have a different CA_Name
value. As you've already deduced, that's because the
CA_Decision value is not in the new row source. Regardless
of all that, the data is unaffected and it is only the
appearance that's disconcerting.

To get things to look right is a messy affair, but in this
case, I presume that the value of the CA_DECISION combo box
is saved in the CA_DECISION field in the subform's base
table, which, believe it or not, makes the issue easer to
deal with.

Add a text box to the sunform and bind it to the CA_DECISION
field. Then size and position the text box eactly on top of
the text portion of the combo box. Set the text box's Tab
Stop property to No and add a line of code to its GotFocus
event that immediately switched the focus to the combo box:

Me.CA_DECISION.SetFocus

The combo box will behave normally because a soon as it
receives the focus, it will jump in front of the text box.
On the other records the text box will display the value of
the field.
 
I don't understand what you mean by "I bound the text box to
this combo box created from the above mentioned query".
The form's record source query is supposed to have one
additional field (description??) and this field has nothing
to do with the combo box beyond coming from the same table.
You were supposed to bind the text box to the new field in
the query.

You say that the "field" is too small, but what does that
mean? Is it the text box **control** that is not wide
enough for the description? If you made the text box the
same size as the text part of the combo box, then it should
display the same thing as the combo box.

Under no circumstances do you want to store the description
in the form's base table, so I have no idea what you have in
mind by "it cannot save the description"
--
Marsh
MVP [MS Access]


Dan @BCBS said:
I created a query to be used as the forms record source. It is created from
the two tables and it includes the field to be displayed. Then I bound the
text box to this combo box created from the above mentioned query. Result:
The field is to small..

I think you had me do all this thinking if I used a query I could bind the
textbox to the new combo box and it would save the value description to the
table.
But it cannot save the description.

The value has two fields Code and Description.
I need to bind the code but display the description...

So when I do what you have suggested the field on the table is too small to
hold the description!!!


Marshall Barton said:
You do have the messier situation after all.

To deal with it, the form's record source needs to be a
query that Joins the form's base table to the combo box's
row source table and includes the field displayed in the
combo box. The text box should be bound to this field.


Dan @BCBS said:
That was very interesting and helpful..

The new text box displays the value saved to the table.
That value is not what is displayed on the form in the combo box.
How can I change the display in the text box.

I have tried to add something like "MD";"Medicare" in the Row Source - but
text boxes don't have a row source. So I tried repeating what you had me do
but with a combo box. It displays the same thing as the text box - When I
add the "MD";"Medicare" in the Row Source it has no affect..

Obviously, my description is Medicare and the stored value is "MD" - any
suggestions on how to display Medicare and not what is being stored in the
table???


My form has a subform (Link Child & Master field = ICNNO).
On that subform are two fields "CA_Name" and "CA_Decision" and they are both
Combo Boxes.

A different list is displayed in "CA_Decision" based on the choice made in
the "CA_Name" field.

This subform is for multiple entries which all pertain to the same ICNNO.
Example: ICNNO = 123
CA_Name = MG
CA_Decision = 1

CA_Name = BH
CA_Decision = 29
Everything is fine to this point:

The problem is when a change is made to "CA_Name" it changes all the
CA_Decision lists on the other entries to the list associated with the
CA_Name just entered.

Private Sub CA_NAME_Change()
If Me.CA_NAME = "MC" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','7','14','22','25')"
ElseIf Me.CA_NAME = "MG" Or Me.CA_NAME = "AG" Or Me.CA_NAME = "TL" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('1','2','4','5','14','22','31')"
ElseIf Me.CA_NAME = "BH" Or Me.CA_NAME = "BM" Then
Me.CA_DECISION.RowSource = "Select * from t_Recommend where
[DEC_TYPE] in ('3','29','32')"
ElseIf Me.CA_NAME = "GC" Then.......


:
There is only one combo box so there is only one row source.
That in itself is not the problem. The annoying issue in a
continuous or datasheet form is that the CA_Decision combo
box goes blank on any rows that have a different CA_Name
value. As you've already deduced, that's because the
CA_Decision value is not in the new row source. Regardless
of all that, the data is unaffected and it is only the
appearance that's disconcerting.

To get things to look right is a messy affair, but in this
case, I presume that the value of the CA_DECISION combo box
is saved in the CA_DECISION field in the subform's base
table, which, believe it or not, makes the issue easer to
deal with.

Add a text box to the sunform and bind it to the CA_DECISION
field. Then size and position the text box eactly on top of
the text portion of the combo box. Set the text box's Tab
Stop property to No and add a line of code to its GotFocus
event that immediately switched the focus to the combo box:

Me.CA_DECISION.SetFocus

The combo box will behave normally because a soon as it
receives the focus, it will jump in front of the text box.
On the other records the text box will display the value of
the field.
 
Back
Top