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.
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.
Code:
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.