I'm creating a form that will allow users to open a multitude of reports. I've created a table (tblReports) that stores ReportID, Title, cmdObject (Report / Query name to open), etc.
I've now creating another table (tblReportFilters) to store all the filters a particular report can have, using these fields:
ReportFilterID, ReportID, txtLabel, txtControlSource, txtRowSource, intColumnCount, txtColumnWidths
I've setup my reports form with the Parent form bound to tblReports.
Subform 1 shows extra info on selected report on Parent form and is also bound to tblReports.
Subform 2 (is nested on Subform 1) as is bound to tblReportFilters.
See Diagram attached.
tblReportFilters is setup to store all the information I need to populate the comobo box that will filter the data on the selected report.
Using the code shown below on the OnCurrent event of Subform 1, I'm dynamically changing the comobo boxes in Subform 2 to show the correct report filters available for any given report.
There is just one problem, whatever the rowsource for the last comobox is, that is the the only rowsource that becomes available for ALL comoboboxes.
Am I setting things up in the wrong way for what I want to do?
Private Sub Form_Current()
Dim rs As Recordset
Set rs = Me.frmReports_Filters_subfrm.Form.RecordsetClone
' Prepare all combo box filters
Do While Not rs.EOF
.cboControlSource.RowSource = Nz(rs!txtRowSource)
.cboControlSource.ColumnCount = Nz(rs!intColumnCount)
.cboControlSource.ColumnWidths = Nz(rs!txtColumnWidths)