Disappearing/Changing Combo Box Selections

[Rklein via AccessMonster.com]

First post, so be gentle. Here goes...
In Access 2003, I have a subform (headerdeductcodes), with the recordsource
being a table, connected to the main form by a ParentID. Within the subform,
I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
and Deduction_Description, which is populated based on the option selected
from Deduction_Type.

The After Update code for Deduction_Type reads:

Private Sub Deduction_Type_AfterUpdate()

Me.Deduction_Description = Null
Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
DeductDescTable.DeductDesc, DeductDescTable.DescripID, DeductDescTable.Rate
FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
[HeaderDeductCodes]![tableid]));"
Me.Deduction_Description.Value = 0

End Sub

The problem arises when the subform loses focus, some of the selected options
from Deduction_Description either disappear or change values. The actual
values written to the table don’t change, but, to the user, the selected
values are no longer visible or changed. Any ideas on how to keep the
Deduction_Description selected options on the form after focus is lost?

 

[Allen Browne]

Okay, this is a fairly common problem. Access does not have a different
RowSource for every row of the continuous form/datasheet. So if you modify
the RowSource so that some values are not in the list, the rows that use
those values have nothing to show, and so the combo goes blank on those
rows.

Solutions:
=======

a) The most obvious workaround is not to limit the combo's RowSource like
that.

b) Another solution is to make the combo's bound column visible. If it is
not a hidden column, Access has the value to show. Typically you do this by
dropping the autonumber, and storing the text value as the primary key of
the combo's lookup table.

c) If those 2 choices don't give you what you need, the next option is to
create a query that uses the main table and also the combo's lookup table.
The query outputs all fields from the main table, and the description field
from the lookup table. That means you can add a text box to your form to
show the description, and it stlll does so, even when the combo's RowSource
doesn't have the desired record.

The trick is to move make the text box's width 0.2" less than the combo, and
place it on top of the combo (Format menu.) Then use its GotFocus event to
SetFocus to the combo, so that as soon as the user clicks there, the combo
jumps in front and the use can choose the desired value. By setting the
combo's TabStop to No, it takes focus and passes focus to the combo, and so
the user doesn't even know there is a text box on top of the combo.

Post back if that's not clear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

First post, so be gentle. Here goes...
In Access 2003, I have a subform (headerdeductcodes), with the
recordsource
being a table, connected to the main form by a ParentID. Within the
subform,
I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
and Deduction_Description, which is populated based on the option selected
from Deduction_Type.

The After Update code for Deduction_Type reads:

Private Sub Deduction_Type_AfterUpdate()

Me.Deduction_Description = Null
Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
DeductDescTable.DeductDesc, DeductDescTable.DescripID,
DeductDescTable.Rate
FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
[HeaderDeductCodes]![tableid]));"
Me.Deduction_Description.Value = 0

End Sub

The problem arises when the subform loses focus, some of the selected
options
from Deduction_Description either disappear or change values. The actual
values written to the table don’t change, but, to the user, the selected
values are no longer visible or changed. Any ideas on how to keep the
Deduction_Description selected options on the form after focus is lost?

 

[Guest]

Hi Allen
re your option A. how else can I limit the rowsource?
thanks

Okay, this is a fairly common problem. Access does not have a different
RowSource for every row of the continuous form/datasheet. So if you modify
the RowSource so that some values are not in the list, the rows that use
those values have nothing to show, and so the combo goes blank on those
rows.

Solutions:
=======

a) The most obvious workaround is not to limit the combo's RowSource like
that.

b) Another solution is to make the combo's bound column visible. If it is
not a hidden column, Access has the value to show. Typically you do this by
dropping the autonumber, and storing the text value as the primary key of
the combo's lookup table.

c) If those 2 choices don't give you what you need, the next option is to
create a query that uses the main table and also the combo's lookup table.
The query outputs all fields from the main table, and the description field
from the lookup table. That means you can add a text box to your form to
show the description, and it stlll does so, even when the combo's RowSource
doesn't have the desired record.

The trick is to move make the text box's width 0.2" less than the combo, and
place it on top of the combo (Format menu.) Then use its GotFocus event to
SetFocus to the combo, so that as soon as the user clicks there, the combo
jumps in front and the use can choose the desired value. By setting the
combo's TabStop to No, it takes focus and passes focus to the combo, and so
the user doesn't even know there is a text box on top of the combo.

Post back if that's not clear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

First post, so be gentle. Here goes...
In Access 2003, I have a subform (headerdeductcodes), with the
recordsource
being a table, connected to the main form by a ParentID. Within the
subform,
I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
and Deduction_Description, which is populated based on the option selected
from Deduction_Type.

The After Update code for Deduction_Type reads:

Private Sub Deduction_Type_AfterUpdate()

Me.Deduction_Description = Null
Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
DeductDescTable.DeductDesc, DeductDescTable.DescripID,
DeductDescTable.Rate
FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
[HeaderDeductCodes]![tableid]));"
Me.Deduction_Description.Value = 0

End Sub

The problem arises when the subform loses focus, some of the selected
options
from Deduction_Description either disappear or change values. The actual
values written to the table don’t change, but, to the user, the selected
values are no longer visible or changed. Any ideas on how to keep the
Deduction_Description selected options on the form after focus is lost?

 

[Allen Browne]

The alternative is to programmatically assign a SQL statement to the combo's
RowSource in the Current event of the main form.

Again, Access has display problems with this approach if the bound column is
zero-width.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
re your option A. how else can I limit the rowsource?
thanks

Okay, this is a fairly common problem. Access does not have a different
RowSource for every row of the continuous form/datasheet. So if you
modify
the RowSource so that some values are not in the list, the rows that use
those values have nothing to show, and so the combo goes blank on those
rows.

Solutions:
=======

a) The most obvious workaround is not to limit the combo's RowSource like
that.

b) Another solution is to make the combo's bound column visible. If it is
not a hidden column, Access has the value to show. Typically you do this
by
dropping the autonumber, and storing the text value as the primary key of
the combo's lookup table.

c) If those 2 choices don't give you what you need, the next option is to
create a query that uses the main table and also the combo's lookup
table.
The query outputs all fields from the main table, and the description
field
from the lookup table. That means you can add a text box to your form to
show the description, and it stlll does so, even when the combo's
RowSource
doesn't have the desired record.

The trick is to move make the text box's width 0.2" less than the combo,
and
place it on top of the combo (Format menu.) Then use its GotFocus event
to
SetFocus to the combo, so that as soon as the user clicks there, the
combo
jumps in front and the use can choose the desired value. By setting the
combo's TabStop to No, it takes focus and passes focus to the combo, and
so
the user doesn't even know there is a text box on top of the combo.

Post back if that's not clear.

First post, so be gentle. Here goes...
In Access 2003, I have a subform (headerdeductcodes), with the
recordsource
being a table, connected to the main form by a ParentID. Within the
subform,
I have two combo boxes: Deduction_Type (SELECT DeductTypes.TABLE_ID,
DeductTypes.SYSCODES_FIELD1 FROM DeductTypes) [a table],
and Deduction_Description, which is populated based on the option
selected
from Deduction_Type.

The After Update code for Deduction_Type reads:

Private Sub Deduction_Type_AfterUpdate()

Me.Deduction_Description = Null
Me.Deduction_Description.RowSource = "SELECT DeductDescTable.TblID,
DeductDescTable.DeductDesc, DeductDescTable.DescripID,
DeductDescTable.Rate
FROM DeductDescTable WHERE (((DeductDescTable.TblID)=[forms]![form1]!
[HeaderDeductCodes]![tableid]));"
Me.Deduction_Description.Value = 0

End Sub

The problem arises when the subform loses focus, some of the selected
options
from Deduction_Description either disappear or change values. The
actual
values written to the table don’t change, but, to the user, the
selected
values are no longer visible or changed. Any ideas on how to keep the
Deduction_Description selected options on the form after focus is lost?