select record source on open

[jguscs]

Is there a way for a user to select a report's record source when the report is opened?
A drop-down list of the database's available tables and/or queries would be optimal, but typing in a table name would suffice (as is done with an "enter parameter value" query for named unbound controls).

 

[jguscs]

Right, I understand that using a modal form with a single combo-box control will give the illusion of a simple pop-up box.
I also understand that a global variable might have to be used so that the selection could be used in both the form and the report.

Could you please explain how I would:

1) take the selection from that modal form's combo box and make it the record source for the report? Maybe something like this:
Me.RecordSource = Forms!TableChooser!Tables
(where Tables is the value in the combo-box... how would the combo box be considered a global variable so its value can be used in the report?)

2) populate the combo box with the available tables in the database via a global variable? ...The key to this is to have the values in the combo box populate automatically with whatever is available as a Table in the database.

Thanks Pat.

 

[jguscs]

Progress:
I've created a reference to open a form (called TableChooser) in my report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "TableChooser", acNormal, , , acFormPropertySettings, acDialog, ""

The report is modal and has a drop-down combo box (called ComboChooser) and an OK and Cancel button.

Cancel Button:
Private Sub Cancel_Click()
DoCmd.Close
End Sub

OK Button:
Private Sub OK_Click()
Reports![MyReport].RecordSource = ComboChooser
DoCmd.Close
End Sub

When I click the cancel button on the modal form, the form closes, but the report continues to run. How can I stop the report from running when the form's Cancel button is pressed?