Trouble saving a Report after setting RecordSource

Question

Trouble saving a Report after setting RecordSource

teach314 (TechnicalUser)

(OP)

13 Apr 13 20:08

hello

I have a function that I use to set a Report's RecordSource. If I run the function TEST_Class(3) shown below, I want to...

a) make a copy of the the Template Report "rClass______BLANK",
b) Rename the copy as "rClass______V_3"
c) Set the RecordSource of the new Report as Table "tClass______V_3"
d) Save the changes to the Report

*** Everything works perfectly until I get to step d). I can't get the changes (RecordSource) to SAVE. The Report shows that the RecordSource is working as it should, but when I try to open it later, it shows no RecordSource.

CODE

Function TEST_Class(V As Integer)

    Dim var_Arg As Variant
    Dim strRptTemplateName As String, strRptNewName As String
    
    var_Arg = "tClass______V_" & CStr(V) & ""
        
    strRptTemplateName = "rClass______BLANK":     strRptNewName = "rClass______V_" & CStr(V) & ""
    
    'copy Template Report, and Rename
    DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acReport, strRptTemplateName, strRptNewName, StructureOnly:=True
    
    'open Report using var_Arg as the RecordSource in the Open event
    DoCmd.OpenReport strRptNewName, acViewPreview, , , , var_Arg
    
    'save the report
    DoCmd.Close acReport, strRptNewName, acSaveYes
    
End Function

In the OPEN event of Report "rClass______BLANK", I have code...

CODE

Private Sub Report_Open(Cancel As Integer)
   Dim strRecordSource As String
  
   If Not IsNull(Me.OpenArgs) Then
      strRecordSource = Me.OpenArgs
      Me.RecordSource = strRecordSource
   End If
End Sub

Thenk you for any assistance.

dhookom (Programmer) · Answer 1 · 2013-04-13 22:50:24

The only way you can get the Record Source to "stick" is to open the report in design view, set the property, and save the report.

Can you provide some context regarding why you are attempting to do this?

Duane
Hook'D on Access
MS Access MVP

teach314 (TechnicalUser) · Answer 2 · 2013-04-14 08:12:02

hi Duane

I generate many tables (tClass______V_1, tClass______V_2,.....) used to form the RecordSource for Reports (rClass______V_1, rClass______V_2,...).
I could easily skip the tables and use SQL directly to produce the Reports.

My thought was to make copies of a 'BLANK' template Report, rename as rClass______V_1, rClass______V_2,etc, then save the Repoprts with their respective RecordSources.
I suppose it would be easier to change the RecordSouce of the 'BLANK' Template Report then just SAVE AS rClass______V_1, etc. Still, my problem is that the RecordSource changes I made allowed the Reports to display properly, but I wasn;t sure how to SAVE the changes. (I'm relativley new to VBA)

Thank you for any clues!

dhookom (Programmer) · Answer 3 · 2013-04-14 10:48:27

Why do all of this work? Apparently all of the fields are the same or your report would produce errors.
Why all of the tables/record sources? Are you simply changing the subset of records displayed in the report? If so, this can be done in a number of easier methods.

There is rarely any reason to "save" copies of reports or tables.

Duane
Hook'D on Access
MS Access MVP

teach314 (TechnicalUser) · Answer 4 · 2013-04-14 16:38:19

hi Duane - I'm sure you're right, but I felt it necessary to SAVE the actual reports because I wanted to...
a) run a bulk routine to procuce the Reports in batches of, say, 50, then...
b) later, examine the Reports in Preview mode. I'll usually only end up printing a few pages from each Report (they average about 30 pages each)

dhookom (Programmer) · Answer 5 · 2013-04-14 20:13:39

Apparently you want to create multiple similar reports in your database. Do you still have questions about how to do this?

Duane
Hook'D on Access
MS Access MVP

teach314 (TechnicalUser) · Answer 6 · 2013-04-15 10:21:15

hi Duane - All is OK now. Your advice "...only way you can get the Record Source to "stick" is to open the report in design view, set the property, and save the report..." was what I needed. I had used code to modify the Report's RecordSource and then opened the Report in Preview mode, I but couldn't get it to save. Thanks

Come Join Us!

Posting Guidelines

Jobs

Trouble saving a Report after setting RecordSource