I have two combo boxes on a form bound to an underlying table. Combo 1 is bound to Category, Combo 2 is bound to Product. I would like to filter combo 2 based on combo 1. When I do, the result of combo 2 is either blank moving from record to record, or will reflect information for all records under the same category. If I remove the combo box sync the information selected for combo 2 is reflected from record to record.

Any ideas as to why and how I can correct the issue so that Combo 2 is filtered from record to record and the selection for combo 2 is reflected for each record?

From your post below you are trying to do this with a continuous form right?
Whilst the records are obviously different in a continuous form, the
controls are just copies so if you requery the row sorce of a combo in your
current event, this will affect the row source of the combo in all the
records with the result that if the value for a particular record is not in
the row source of the combo, it will display blank.

So AFAIK you can't effectively work with synchronised combos on a continuous
form. Generally it's best to use a continuous form to display records and
use a single form to edit a particular record, requerying the continuous
form when you save changes on the single form.

HTH





"vvariety" <vvariety816 (AT) gmail (DOT) com> wrote

I have two combo boxes on a form bound to an underlying table. Combo 1 is
bound to Category, Combo 2 is bound to Product. I would like to filter
combo 2 based on combo 1. When I do, the result of combo 2 is either blank
moving from record to record, or will reflect information for all records
under the same category. If I remove the combo box sync the information
selected for combo 2 is reflected from record to record.

Any ideas as to why and how I can correct the issue so that Combo 2 is
filtered from record to record and the selection for combo 2 is reflected
for each record?

Hi

Let say for the sake of this example:

Combo1's recordset is attached to tblCategory and it has 2 fields, txtCatID
<PK> & txtProdID<NUM>

place a txtfield of txtProdID on the form and have it invisible = True, this
way when the user selects the category they wish, it will update the
txtProdID field.

Combo2's recordset is attached to tblProducts and it has 2 fields,
txtProdID<PK>, and txtProdDesc<TEXT>.

When you invoke the recordset query builder for Combo2 have the criteria for
txtProdID as follows " =Forms!YourForm!YourAddedTextField" (remove quotes)

In the AfterUpdate of Combo1

me.Combo2.Requery

HTH
Mick