Cascaded ComboBoxes on a continuous form

[Yaacov Klapisch]

Crossposted on microsoft.public.access.forms, microsoft.public.access.formscoding

I have 3 cascaded ComboBoxes on a continuous form and for some reason the three comboboxes show values only for the current row. other rows are blank.
The comboboxes have the required code and I understand that the reason is because the key field is hidden.
Is there a solution for this problem?
I tried playing around with hidden TextBoxes and concatenated values on one ComboBox... Still only the current row is filled, all other blank.

Yaacov Klapisch

 

[Allen Browne]

The combos go blank in other rows when:
a) the Bound Column is zero-width, and
b) the Row Source is restricted.
To prevent the problem, you need to avoid (a) or (b).

You can avoid (a) if you use text-based keys for the lookup tables. For
example, if you have a category table with fields CategoryID (AutoNumber)
and CategoryName (Text), the CategoryName will be unique anyway so it can be
the primary key. The artificial key (AutoNumber) is not needed. You solve
the problem by using the natural key.

The other alternative is to NOT restrict the RowSource.

If you can't prevent the problem, you may be able to work around it setting
the RecordSource of the continuous form to a query that includes both the
main table and the lookup table. Use an outer join if the lookup field is
not required. You can now get the lookup value to display in the form, in a
text box that overlays the combo or whatever. Naturally you use the Enter
event of the text box to SetFocus to the combo.

It's messy, and there are still some display problems, so preventing the
problem is better if you can.

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

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

Crossposted on microsoft.public.access.forms,
microsoft.public.access.formscoding

I have 3 cascaded ComboBoxes on a continuous form and for some reason the
three comboboxes show values only for the current row. other rows are blank.
The comboboxes have the required code and I understand that the reason is
because the key field is hidden.
Is there a solution for this problem?
I tried playing around with hidden TextBoxes and concatenated values on one
ComboBox... Still only the current row is filled, all other blank.

Yaacov Klapisch

 

[YmK]

What do you mean by NOT to restrict the RowSource? Can you be more specific?

--
(¯`·.¸¸.·´¯`·.¸¸.->YmK<-.¸¸.·´¯`·.¸¸.·´¯)
Yaacov Klapisch
VB/Access Programmer - System Admin.
Computer technician.
(e-mail address removed)

 

[Allen Browne]

Your original post stated:
The comboboxes have the required code and I understand
that the reason is because the key field is hidden.

I assume this code sets the RowSource of the combo so that it does not show
all rows, e.g.:
Me.Combo1.RowSource = "SELECT CarName FROM CarTable WHERE Manufacturer =
'Honda';"

If you remove the WHERE clause so the combo can show all records, it is able
to show the value on all rows.