Changing RecordSource Property using VBA

[arnodys]

Hi

I'm trying to create a report that would display data from different tables with similar structure.

I have some 30 different tables which outline 5 different products for different years. It is impossible to put all of them in one big table since each of them are used by different department and hence have different field structures.

All of them however contain fields which I will try to display in the report. The fields are:

ProductName, Supply, Return, NetSale

Since it would be inefficient to create 30 different reports that would reference to each table, I am thinking to design one single report to display the desired fields and get it to point to different tables using VBA.

From posted threads, I know it is possible to modify the RecordsSource property using VBA on open report event. However, until now, I still am not able to get it working.

Can someone help?


Cheers

 

[llkhoutx]

A compiled report cannot have its recordsource modified.

One does this with having a query for the reports recordsource, then modify that query as necessary immediately prior to the report being opened. Any such modifications is transparent to the report.

 

[Dennisk]

the recordsource for a report can be changed after you open it. The report will then automatically requery.

 

[Wiz47]

You could also use the ON OPEN event for the report to assign the appropriate query or dynamic query at run time.

 

[Wiz47]

A compiled report cannot have its recordsource modified.
One does this with having a query for the reports recordsource, then modify that query as necessary immediately prior to the report being opened. Any such modifications is transparent to the report.

That's true, so that's why I thought it best to assign it (the query) at run time in the ON OPEN event.

 

[arnodys]

Thanks for the replies guys. I have noticed from other threads that it is possible to do this in on open event. However, I don't seem to be able to get it working.

Is there more to it than just write eg:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SalesTable"
End Sub

I even try to list all the fields like:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SELECT ProductName, Supply, Return, NetSale " & _
    "FROM SalesTable"
End Sub

But got nothing when the report opens
Cheers

 

[Wiz47]

Is there more to it than just write eg:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SalesTable"
End Sub

If "SalesTable" is the query or Table for the record source, then the above should work as long as you have the same fields listed in your report. I use the following to open a specific report (as determined by a combobox in my form) on the ON LOAD event of the report. Just change the names to match your data.

Private Sub Report_Open(Cancel As Integer)
    On Error Resume Next
    Set frm = Forms!YourFormName
    If Err.Number = 0 Then
        With frm
            If !ComboSelection = "Name1" Then
                Me.RecordSource = "Query1"
            ElseIf !ComboSelection = "Name2" Then
                Me.RecordSource = "Query2"
End If
        End With
        Set frm = Nothing
    End If
End Sub

 

[arnodys]

Thanks for the help guys. My program works beautifully now. Apparently I was looking for the wrong thing in my report before, hence I thought it doesn't work.

Thanks for all the help.