Hi,
Have been playing with this for 2 days and unable to get it to work properly. Any help is greatly appreciated. I'm using Access 2003 (with an upgrade to Access 2010 in the near future.)
My objective is to have a subform that displays a datasheet based on a query that changes - the query is "SELECT * FROM Seldata2" however Seldata2 table will have different fields (different in quantity and field names) each time the user selects different combinations from the controlling form.
The details:
The form "DisplayData" has a subform "DisplayDataSUB". In DisplayData, the user selects from various lists that determine what data will be displayed in DisplayDataSUB. The user will do this repetitively, each time deleting the table Seldata2 and recreating it with different fields.
The Displaydata form contains DisplayDataSUB and uses a Source Object of "Query.qrySeldata2". The DisplayDataSUB subform has a record source of "qrySelData2". There are no controls on the subform.
The problem:
When the form initially opens, it properly displays the data in SelData2. However, when the selection is changed (and Seldata2 is recreated), and the form and subform requeried, only the data corresponding to the initial fields in Seldata2 (from the first pass) are shown. For example, if initially Seldata2 contained fields named "A" and "B", these will properly display in the datasheet. But then, if SelData2 is updated and now contains fields "A", "C", "D", then column "A" will properly show in the datasheet, column "B" will display "#Name?" and there will not be a column "C".
The interesting thing is, that if the entire form is closed and re-opened, the fields from the previous execution of the form will be displayed. So somehow, even though the subform has no controls, the subform is retaining the field structure of the previous Seldata2 even though it didn't properly display that previously. So when closing and reopening - if the previous iteration of the form had a Seldata2 with 4 fields, but displayed only 2, if the form was closed and reopened with a Seldata2 with 3 fields, the datasheet would contain 4 columns.
So somehow, the form retains the previous field structure, and if I can figure out how to "erase" that, possibly all will work correctly.
Have been playing with this for 2 days and unable to get it to work properly. Any help is greatly appreciated. I'm using Access 2003 (with an upgrade to Access 2010 in the near future.)
My objective is to have a subform that displays a datasheet based on a query that changes - the query is "SELECT * FROM Seldata2" however Seldata2 table will have different fields (different in quantity and field names) each time the user selects different combinations from the controlling form.
The details:
The form "DisplayData" has a subform "DisplayDataSUB". In DisplayData, the user selects from various lists that determine what data will be displayed in DisplayDataSUB. The user will do this repetitively, each time deleting the table Seldata2 and recreating it with different fields.
The Displaydata form contains DisplayDataSUB and uses a Source Object of "Query.qrySeldata2". The DisplayDataSUB subform has a record source of "qrySelData2". There are no controls on the subform.
The problem:
When the form initially opens, it properly displays the data in SelData2. However, when the selection is changed (and Seldata2 is recreated), and the form and subform requeried, only the data corresponding to the initial fields in Seldata2 (from the first pass) are shown. For example, if initially Seldata2 contained fields named "A" and "B", these will properly display in the datasheet. But then, if SelData2 is updated and now contains fields "A", "C", "D", then column "A" will properly show in the datasheet, column "B" will display "#Name?" and there will not be a column "C".
The interesting thing is, that if the entire form is closed and re-opened, the fields from the previous execution of the form will be displayed. So somehow, even though the subform has no controls, the subform is retaining the field structure of the previous Seldata2 even though it didn't properly display that previously. So when closing and reopening - if the previous iteration of the form had a Seldata2 with 4 fields, but displayed only 2, if the form was closed and reopened with a Seldata2 with 3 fields, the datasheet would contain 4 columns.
So somehow, the form retains the previous field structure, and if I can figure out how to "erase" that, possibly all will work correctly.
