Module to Modify Report Controls Based on Form

[MarkK]

But the problem we are working on arises from the fact that the data is not normalized. If the tables were well designed, we could modify a where clause and select the rows we need. In this case, we are forced to modify the ControlSources to select the fields we need, which is not how a database works, even one that was scaled down from a larger file.

 

[Adam.Furtado]

Mark,

Thank you for your help. I am going to start looking into database design to get a better grasp on what you are suggesting. If you have any thoughts, I would certainly appreciate them.

For anyone who is searching for this solution, I have figured it out. It was so much easier than I was making it.

Private Sub Report_Open(Cancel As Integer)
Me![txtCurrentPrice].ControlSource = [Forms]![frmChooseFields]![cboCurrentPrice]
Me![txtHistory1].ControlSource = [Forms]![frmChooseFields]![cboHistory1]
Me![txtHistory2].ControlSource = [Forms]![frmChooseFields]![cboHistory2]
Me![txtHistory3].ControlSource = [Forms]![frmChooseFields]![cboHistory3]
End Sub

This code will allow you to use combo boxes from a form to alter the source of your controls in a report. You can then convert the labels of the controls to text boxes and define their control source to read the name of the field being displayed.

Ex.
lblHistory1 = [txtHistory1].[ControlSource]

This is using a form with 4 combo coxes, one for each field. The combo boxes are displaying the field list from my pricing table.

Hope this helps someone.

 

[MarkK]

Thanks Adam, have a good one.