Disappearing Combo Box Value

[Guest]

I have a problem with a combo box on a form which is bound to a query.

I have used On Got Focus in the combo box to set values based on other
fields in the record. The fields which contain the criteria are not changed
on the form so that is why On Got Focus was used.

This has been done using an sql statement in code. When selecting the combo
box the correct values are available for the given criteria. There are 2
columns, with the first column value being added to the record. Upon opening
the form the value in the second column is visible as it should be, however
when scrolling through the records these values disappear, however the first
coulmn value is correctly contained in the table.

Any Ideas anyone?

 

[Armen Stein]

I have a problem with a combo box on a form which is bound to a query.

I have used On Got Focus in the combo box to set values based on other
fields in the record. The fields which contain the criteria are not changed
on the form so that is why On Got Focus was used.

This has been done using an sql statement in code. When selecting the combo
box the correct values are available for the given criteria. There are 2
columns, with the first column value being added to the record. Upon opening
the form the value in the second column is visible as it should be, however
when scrolling through the records these values disappear, however the first
coulmn value is correctly contained in the table.

Any Ideas anyone?

It sounds like you are changing the RowSource of the combobox on a
continuous form. This doesn't work the way you might expect. ALL the
records on a continuous form share the same properties (with the
exception of conditional formatting, another topic). So when you
change the RowSource, you are changing it for ALL the records, not
just the one.

When a RowSource recordset does not include the key value that is in a
combobox, and the key value is a hidden column, the value in the
combobox "disappears" (because it can't be looked up), but the key
value remains in the table.

There's no easy way around this. Usually we redesign the form so that
the records are edited using a pop-up detail form, or a linked detail
form below the list of records.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

This has been done using an sql statement in code. When selecting the combo
box the correct values are available for the given criteria. There are 2
columns, with the first column value being added to the record. Upon opening
the form the value in the second column is visible as it should be, however
when scrolling through the records these values disappear, however the first
coulmn value is correctly contained in the table.

As Armen says, the problem is that the continuous form actually has only one
combo box displayed many times. When you change its properties, all instances
change.

One (clunky) solution is to carefully superimpose a textbox over the text area
of the combo; its rowsource would be

=DLookUp("[fieldname]", "[tablename]", "[IDfield] =" & [IDfield])

to display the text value associated with the stored ID.

John W. Vinson [MVP]