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).

 

[Pat Hartman]

You can add code to the report's open event to modify its recordsource.

Me.Recordsource = SomeQueryOrTableName

You can open a small MODAL form to give the user a combo to choose from then use the selection to change the recordsource. The form needs to be modal so that the code in the open event stops until the form closes. You'll probably need a to populate a global variable from the form so the report can pickup the selected value.

 

[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?