Query in a sub form

[Tony]

Hello,

I have a form with a subform in Access 2003. One of the fields in the
subform is called categoryid. I am trying to base a combobox on a
query which looks up the value of the categoryid of the current record
on the subform and then displays an appropriate list for that
categoryid in the combobox.

In the query I use the following line in the criteria [Forms]!
[TblOrderDetails1]![TblItemOrderDetails Subform].[Form]![CategoryID]

However, this query only works for the first record in the subform.
Regardless of which record the combobox is in, it always looks up the
CategoryID of the first record in the subform.

Does anyone have any ideas? this has been puzzling me for days.

Thank you,

Tony

 

[Dirk Goldgar]

Hello,

I have a form with a subform in Access 2003. One of the fields in the
subform is called categoryid. I am trying to base a combobox on a
query which looks up the value of the categoryid of the current record
on the subform and then displays an appropriate list for that
categoryid in the combobox.

In the query I use the following line in the criteria [Forms]!
[TblOrderDetails1]![TblItemOrderDetails Subform].[Form]![CategoryID]

However, this query only works for the first record in the subform.
Regardless of which record the combobox is in, it always looks up the
CategoryID of the first record in the subform.

Does anyone have any ideas? this has been puzzling me for days.

You have to requery the combo box every time you move to a new record on the
subform; that is, in the subform's Current event. You also have to requery
it whenever you change the value of the CategoryID field on the subform;
that is, in the CategoryID control's AfterUpdate event.