combo box on subform showing same result for each record

[gmazza via AccessMonster.com]

Hey there,
I have a combo box on my subform(single form) and when I choose a value from
it, it changes all the values in each record to the value I chose in the
combo box. Any ideas why?
Let me know if you need more info!

Thanks!

 

[Jeanette Cunningham]

Hi gmazza,
In ContinuousForms view, that is the expected outcome. Even though you're
seeing many records, there is in fact only the
one combobox control on the form, and it can only respond to the values in
the Current record - that is, the record that currently has the
focus. It's just the way controls work in a continuous form. Though many
rows are displayed, there is still only one combo so whatever you do to it
is seen on all rows.

The standard work-around is to overlay the combobox with an unbound textbox
that has in its' ControlSource a DLookup() function pointing to the same
data source used in the combobox's RowSource property. Make the textbox
disabled and locked, so it can be seen but not entered. That way
you can see the correct values in the visible records that do *not* have the
Current focus, and yet still enter data in the combobox in the Current
record.


Another work around: Include the display column of the combo in the
Recordsource query of the form (join the foreign table and drag in the
column). Position and size the new textbox so that you can place it directly
over the combo box allowing only the down-arrow portion of the
combo to show.


Create a GotFocus event for the textbox that only does a SetFocus to the
combo.


This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not fit the criteria
that is in effect based on the current record.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[gmazza via AccessMonster.com]

Thanks for your reply!
Problem is this is a datasheet view, not continuous forms, I can't really
overlay the combo box because it still shows up on the form view anyway. Do
you know what I mean?

Hi gmazza,
In ContinuousForms view, that is the expected outcome. Even though you're
seeing many records, there is in fact only the
one combobox control on the form, and it can only respond to the values in
the Current record - that is, the record that currently has the
focus. It's just the way controls work in a continuous form. Though many
rows are displayed, there is still only one combo so whatever you do to it
is seen on all rows.

The standard work-around is to overlay the combobox with an unbound textbox
that has in its' ControlSource a DLookup() function pointing to the same
data source used in the combobox's RowSource property. Make the textbox
disabled and locked, so it can be seen but not entered. That way
you can see the correct values in the visible records that do *not* have the
Current focus, and yet still enter data in the combobox in the Current
record.

Another work around: Include the display column of the combo in the
Recordsource query of the form (join the foreign table and drag in the
column). Position and size the new textbox so that you can place it directly
over the combo box allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not fit the criteria
that is in effect based on the current record.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hey there,
I have a combo box on my subform(single form) and when I choose a value

[quoted text clipped - 4 lines]

Thanks!

 

[Linq Adams via AccessMonster.com]

It's just the way controls work in a continuous form.

This should say it's just the way ***unbound controls*** work in a continuous
(or datasheet) form.

If you want the combobox value to reflect the record it lies in you have to
bind the combobox control to a field in the underlying table.