If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subform Refresh Problem (but only with an unbound combo box control)
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 |
#2
|
|||
|
|||
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 "Barry Skidmore" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
open a form through a subform in access 2000 | Tammy | Setting Up & Running Reports | 12 | October 22nd, 2004 02:43 PM |
How can I move the focus to a control on a subform? | Brandon | General Discussion | 7 | July 17th, 2004 01:39 AM |
Can't figger this'un out. | Jerry | Running & Setting Up Queries | 7 | July 7th, 2004 06:12 AM |
Design questions | Bruce | Database Design | 15 | June 19th, 2004 12:12 AM |
Combo Box Control - CONTROL tab missing from FORMAT CONTROL dialogue | JE McGimpsey | Worksheet Functions | 0 | March 11th, 2004 06:47 PM |