Subform combo box values disappear

[Guest]

Using Access 2003. I have a subform with 3 bound fields to a table. 1 text
box - product number, and 2 comboboxes tied together. The first is a category
field with 8 values that come from a table that contains cateogryid and
categoryname, and the second is a subcategory field that has values dependant
on the category selected (using rowsource query) and comes from a table that
contains the subcategory id, category id and subcategory name.

The problem is that after i enter a couple of records, if I go back and and
click in one of the previous records subcategory field the value disappears
if the category doesn't match the last records category.

I tried using both a category after update code and subcategory got focus to
get the subcategory field to populate and I get the same results, the values
disappear if I go back.

I am very frustrated and would appreciate any help. Thanks.

 

[Guest]

I forgot to mention that the subform is a continuous form and that the data
saves great in the table. The data in the subform looks great as long as I
don't go and click in a previously entered record.

 

[Allen Browne]

You have set up these combos so that the RowSource of the last one shows
only the records that match the value of the previous one?

If so, that will cause the problem you describe. The combo has only one
RowSource - not one for every row of the continuous form. So if you limit it
to only the values valid for the current row, and the bound column is
hidden, Access won't have the information it needs to display the values for
the other rows. Consequently, the combo shows as blank in those rows.

Possible solutions:
=============
Solution A: Don't limit the combo's RowSource.
It then has the values to show on every row.

Solution B: Don't hide the combo's bound column.
For example, set up the combo's RowSource table so it just has this field:
CagegoryID Text primary key
so the text of the category is the primary key (no hidden AutoNumber.) Then
set up the foreign key (the field the combo is bound to) so it is Text also.
Now Access has the text value to display on every row, regardless of how its
RowSource is filtered.

Solution C: Use a text box as well as the combo.
1. Change the subform's RecordSource to a query that includes the combo's
lookup table as well as its main table. (Use an outer join if the field the
combo is bound to is not Required.) Drag the combo's display column into the
query design grid. Save the query.

2. Add a text box to your form for the field you just added to the query.
The text box will display the same thing as the combo, but it works for all
rows.

3. Position this text box so it is on top of the combo (Format | Bring To
Front), but make it a bit narrower so you can see the drop-down arrow at the
right of the combo.

4. In the Got Focus event of the text box, SetFocus to the combo. This makes
the combo jump in front of the text box, but only for the current row! The
other rows still have the text box in front, and so they display correctly.

 

[Guest]

Thank you Allen, you are always a great help and it is MUCH appreciated! I am
no longer tearing my hair out and am happily coding away. Because there are
60 items in the list I don't think optionA would be best, I will try option B
or C

 

[DocBrown]

If I understand your situation correctly, I solved this by creating the
following macro and assigning it to the OnClick Event for the SubCategory
control:

Name: UpdateList : OnGotFocus
Action: Requery
Control Name: =[ActiveControl].[Name]

This can then be used on any of the combo boxes that have a lookup list
based on a query.

John S.