Subform Refresh Problem (but only with an unbound combo box control)

[Barry Skidmore]

Configuration:
Access 2000 - SP3
XP Home - SP2
=============

I have a main form linked to a subform using master-child linking.
Their data tables are related in a one-to-many relationship.

I am filtering the record that is displayed on the main form using an
unbound combo box. The row source for this combo box is a query with
the primary key of the main form table as the bound column.

This works fine, and the records shown on both the main form and on the
subform are refreshed properly, with one exception. Please bear with
me as this is a bit difficult to explain:

I also want to filter the record displayed on the subform, since there
are many such records for each corresponding record on the main form. I
am doing this using an analogous combo box on the subform, with the row
source for this combo box being another query, and using the primary
key of the subform table as the bound column.

To restrict the subform records appearing in this subform combo box to
_only_ those belonging to the current record displayed in the main
form, this query includes both the main form table and the subform
table. This query has the following in the criteria row for the
primary key of the main table:

Field: MainTablePrimaryKey
Table: MainTable
Criteria: Forms![frm_Main]![MainTablePrimaryKey]

The idea is that the subform combo box will only display the 'correct'
subform records.

Both combo box filters work as expected, with one bothersome exception:

The subform records displayed in the subform combo box are _not_
refreshed after I select a different main form record. The only way
this combo box can be refreshed is using a refresh button installed
directly on the subform. I have tried including the following in the
'After Update' event property of the combo box on the main form, after
the code that filters the main form (this will refresh all records in
the subform, _except_ the subform combo box):

Forms![frm_Main].[frm_Subform].Form.Requery

The other approach that works is to use the 'mouse down' event property
of the subform combo box to trigger a 'refresh'. However, for some
reason this prevents the combo box from being able to filter the
selected record.

Please excuse this lengthy explanation, however, I have attempted to be
complete.

Thanks,
Barry

 

[tina]

Forms![frm_Main].[frm_Subform].Form.Requery

the above line of code requerys the RecordSource of the subform. to requery
the RowSource of the combo box on the subform, add the following additional
line of code in the same event procedure, as

Me!frm_Subform!NameOfSubformComboBox.Requery

since you're running the code from the main form's module, you don't need to
refer explicitly to the form's name - that's why i replaced that part of the
reference with "Me". also, usually you don't have to explicitly refer to the
subform as ".Form", unless you're wanting to act on the subform itself as a
"form" - such as in the Form.Requery action. that's why i skipped the
".Form" reference in the combobox.requery action above.

hth

Configuration:
Access 2000 - SP3
XP Home - SP2
=============

I have a main form linked to a subform using master-child linking.
Their data tables are related in a one-to-many relationship.

I am filtering the record that is displayed on the main form using an
unbound combo box. The row source for this combo box is a query with
the primary key of the main form table as the bound column.

This works fine, and the records shown on both the main form and on the
subform are refreshed properly, with one exception. Please bear with
me as this is a bit difficult to explain:

I also want to filter the record displayed on the subform, since there
are many such records for each corresponding record on the main form. I
am doing this using an analogous combo box on the subform, with the row
source for this combo box being another query, and using the primary
key of the subform table as the bound column.

To restrict the subform records appearing in this subform combo box to
_only_ those belonging to the current record displayed in the main
form, this query includes both the main form table and the subform
table. This query has the following in the criteria row for the
primary key of the main table:

Field: MainTablePrimaryKey
Table: MainTable
Criteria: Forms![frm_Main]![MainTablePrimaryKey]

The idea is that the subform combo box will only display the 'correct'
subform records.

Both combo box filters work as expected, with one bothersome exception:

The subform records displayed in the subform combo box are _not_
refreshed after I select a different main form record. The only way
this combo box can be refreshed is using a refresh button installed
directly on the subform. I have tried including the following in the
'After Update' event property of the combo box on the main form, after
the code that filters the main form (this will refresh all records in
the subform, _except_ the subform combo box):

Forms![frm_Main].[frm_Subform].Form.Requery

The other approach that works is to use the 'mouse down' event property
of the subform combo box to trigger a 'refresh'. However, for some
reason this prevents the combo box from being able to filter the
selected record.

Please excuse this lengthy explanation, however, I have attempted to be
complete.

Thanks,
Barry