I have taken some advice on creating a form with 2 synchronised combo boxes, subsequently I have turned this into a sub form within a bigger form. I made the necessary changes to identify the controls as belonging to a subform.
The subform is displayed in datasheet view, with value list in the 2nd combo box dependant upon the selection for the first etc. Unfortunately now when selecting a value for the first combo box all data in the 2nd combo box for previous records dissappears.
I have looked on MS Knowledge base and found this is a common problem when the form meets the criteria 1 to 3 which mine does. They have indicated a fix - utilising the D Lookup expression. I have never used this and the fix has no detail. I have been unable to find further info on this - does anyone have advice about where to look for a straightforward fix. I have pasted below the detail from MS knowledge base.
any advice very much appreciated,
cheers, Toby
SYMPTOMS
When you select a row in a combo box in a continuous form or a form that is open in Datasheet view, the text portion of the combo box in other records appears empty.
CAUSE
The behavior occurs under the following conditions:
1. The form is a continuous form, or it is open in Datasheet view so that you can view multiple records.
2. The combo box's RowSource property is set to a parameterized query to limit the number of rows in the combo box based on criteria in another field in the form.
3. The combo box criteria for the current record eliminate the rows that were selected in the other records.
Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.
When the criteria for the query that the combo box is based on change, rows that were selected in the other records may be eliminated from the recordset. As a result, the non-BoundColumn values are no longer available to be displayed.
RESOLUTION
Do not hide the BoundColumn field. If you want to see a different column, use the DLookup() function or the AutoLookup technique to display the information you want in another control on the form.
The subform is displayed in datasheet view, with value list in the 2nd combo box dependant upon the selection for the first etc. Unfortunately now when selecting a value for the first combo box all data in the 2nd combo box for previous records dissappears.
I have looked on MS Knowledge base and found this is a common problem when the form meets the criteria 1 to 3 which mine does. They have indicated a fix - utilising the D Lookup expression. I have never used this and the fix has no detail. I have been unable to find further info on this - does anyone have advice about where to look for a straightforward fix. I have pasted below the detail from MS knowledge base.
any advice very much appreciated,
cheers, Toby
SYMPTOMS
When you select a row in a combo box in a continuous form or a form that is open in Datasheet view, the text portion of the combo box in other records appears empty.
CAUSE
The behavior occurs under the following conditions:
1. The form is a continuous form, or it is open in Datasheet view so that you can view multiple records.
2. The combo box's RowSource property is set to a parameterized query to limit the number of rows in the combo box based on criteria in another field in the form.
3. The combo box criteria for the current record eliminate the rows that were selected in the other records.
Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.
When the criteria for the query that the combo box is based on change, rows that were selected in the other records may be eliminated from the recordset. As a result, the non-BoundColumn values are no longer available to be displayed.
RESOLUTION
Do not hide the BoundColumn field. If you want to see a different column, use the DLookup() function or the AutoLookup technique to display the information you want in another control on the form.
