Create report with a variable recordsource (changing table names)

[VBAinspire]

The problem: I created a standard report that I want to be used by many users for many different scenarios. I'm trying to allow the user to generate standard reports from many different data sets (tables with exactly the same field names but different names).

The attempted solution: I created a form with a list box containing all tables in the database that the user can choose from. The user will then click a button that will execute a report based on the selected table. Here is a generic example of the code behind the button on the form:

Private Sub cmdReports_Click()

Dim strFrom As String
Dim strSQL As String
Dim intAlt As Integer

With Me![TableListbox]
   For intAlt = 0 To .ListCount - 1
     If .Selected(intAlt) Then
        strFrom = " FROM [" & .Column(1, intAlt) & "]"

        strSQL = "SELECT FIELD1, FIELD2 FROM " & strFrom & " WHERE DATATYPE = 1"

        [Report].RecordSource = strSQL
        
     End If
  Next intAlt
    
End With

DoCmd.OpenReport "TEST", acViewPreview

End Sub

The Error: The code is crashing on "[Report].Recordsource = strSQL".

The Questions:

1. Is it possible to set the recordsource for a report in a form?

2. If not, do you know of any other way that I can allow the user to select the table that the report is based on?

Thanks for your help.

The End.

 

[JerryDennison]

The report must be open in design mode for you to set it recordsource property, or any other property that can't be set using an option of the DoCmd.OpenReport method.

 

[Databaseguy]

This is what I've done in the past. Make a form with a list box. In the reports on open event:

Select case [forms].[formchoosereport].[cboChoose]
Case 1
me.recordsource = "table Name"
Case 2
me.recordsource = "Another table Name"
'etc
End Select
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[MichaelRed]

I usually (always?) do it the other way. Create a "MakeTable" query with the source table inserted via code and the dest table name something for the report. Before opening the report, run the make table query. The report record source doesn't need to change - you just need to run the query BEFORE opening the report.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over