cascading combo display is blank sometimes (Access 2007)

[sumthin_Missin]

Okay, so let me preface this by saying that I've waded through tons and tons
of posts about cascading combo boxes, and I've come up with something that
works *mostly* except for this one display issue. Any help would be much
appreciated:

form Project List (saves to Projects table) contains cascading combos
[Customer] and [Location]

[Customer] Rowsource pulls from table "Customers":
SELECT [Customers].[ID], [Customers].[Customer Name] FROM Customers;

[Location] Rowsource pulls from table "Locations", filtered by the value of
the [Customer] combo box:
SELECT Locations.ID, Locations.Location_Name, Locations.Customer FROM
Locations WHERE (((Locations.Customer)=[Forms]![Project List].[Customer]))
ORDER BY Locations.Location_Name;

For the [Customer] combo box, I've added:
Private Sub Customer_AfterUpdate()
Me!Location.Requery
End Sub

Also, (not sure if this is necessary):
Private Sub Form_Current()
Me!Location.Requery
End Sub


On the Project List form (which is used in datasheet view), the data pulling
and saving works okay as far as I can tell, except that saved location values
are only displayed for rows that have the same customer value.
For instance, when the form loads, I see:
Cust 1 -- Loc 1
cust 1 -- Loc 2
Cust 2 -- BLANK
Cust 2 -- BLANK
(values exist in the Projects table, however, for all these rows)

If I click on one of the location combos on a row with customer 2, the
displayed values immediately shift to:

Cust 1 -- BLANK
Cust 1 -- BLANK
Cust 2 -- Loc A
Cust 2 -- Loc B

Note: Customers - Locations is a one-to-many relationship (locations are
unique to each customer)

I'm not sure if this is an Access 2007 issue or an issue with my form's
events or what. Any help would be much appreciated. Thanks!

 

[Allen Browne]

In a continuous form or datatsheet, the combo appears on each row but there
is not a separate RowSource for every row. Therefore the combo will display
as blank if:
a) the display column is not the bound column, and
b) you filter the RowSource so it does not have the value to display.

You cannot change that behavior, but there are a couple of ways to work
around it.

One is not to hide the bound column. Could your Locations table use the
Location_Name as the primary key instead of the hidden ID number? Then the
related table would also use a Text field (for the Location_Name) as its
foreign key. Since the combo does not need a hidden number, it has the value
it needs to display for every row, and so it displays properly even when
filtered.

Another approach is to place a text box over top of the combo. Base the form
on a query that uses your main table and the locations table, so it can
return the location_name field. Bind the text box to this field, and set its
Locked proeprty to Yes so the user can't change it. In its Enter event,
SetFocus to the combo behind it.

What happens is that the text box has the values to show for every row, so
the form looks right. As you tab through the form (or click on the text
box), the combo takes focus, but Access draws the combo in front of the text
box on the current row only. Therefore the fact that the combo is restricted
to the current locations only means that it shows the desired values when
dropped down, but the other rows still display correctly.

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

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

Okay, so let me preface this by saying that I've waded through tons and
tons
of posts about cascading combo boxes, and I've come up with something that
works *mostly* except for this one display issue. Any help would be much
appreciated:

form Project List (saves to Projects table) contains cascading combos
[Customer] and [Location]

[Customer] Rowsource pulls from table "Customers":
SELECT [Customers].[ID], [Customers].[Customer Name] FROM Customers;

[Location] Rowsource pulls from table "Locations", filtered by the value
of
the [Customer] combo box:
SELECT Locations.ID, Locations.Location_Name, Locations.Customer FROM
Locations WHERE (((Locations.Customer)=[Forms]![Project List].[Customer]))
ORDER BY Locations.Location_Name;

For the [Customer] combo box, I've added:
Private Sub Customer_AfterUpdate()
Me!Location.Requery
End Sub

Also, (not sure if this is necessary):
Private Sub Form_Current()
Me!Location.Requery
End Sub


On the Project List form (which is used in datasheet view), the data
pulling
and saving works okay as far as I can tell, except that saved location
values
are only displayed for rows that have the same customer value.
For instance, when the form loads, I see:
Cust 1 -- Loc 1
cust 1 -- Loc 2
Cust 2 -- BLANK
Cust 2 -- BLANK
(values exist in the Projects table, however, for all these rows)

If I click on one of the location combos on a row with customer 2, the
displayed values immediately shift to:

Cust 1 -- BLANK
Cust 1 -- BLANK
Cust 2 -- Loc A
Cust 2 -- Loc B

Note: Customers - Locations is a one-to-many relationship (locations are
unique to each customer)

I'm not sure if this is an Access 2007 issue or an issue with my form's
events or what. Any help would be much appreciated. Thanks!

 

[sumthin_Missin]

Allen,
Excellent approach to overlay text box over the combo box -- worked like a
charm!

For the record/other readers, it only seems to work in continuous form view
vs. datasheet view because datasheet view doesn't seem to allow one field to
be on top of another field.

Thank you very much!