• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

RecordSource not saving

Good afternoon,
Ive been building an app in MSAccess, which runs a report in VBA.

The code works great on one PC, but in every other PC in the office it doesnt work at all! And Im scratching my head over it!

The code its running is similar to:-
            Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"
            DoCmd.OutputTo acOutputReport, "FaultLogs_Standard", acFormatPDF, defaultSaveLocation & reportTitle & "-Standard-" & varBranch & ".pdf", False, "", 0, acExportQualityPrint

It runs but on every PC it runs with the default RecordSource, its like it ignores the line
RecordSource line, and runs the report on every PC, but on one it runs the RecordSource line and saves it.

Any ideas how I can fix this? Ive been looking at a way of saving the RecordSource back to the report and then being able to run the report with the recordsource saved back to it, but cant figure any method to save the code.

Any ideas?

Thank you
1 Solution
Helen FeddemaCommented:
Since you are not filtering the table for the report's record source, why not just set the record source to the table name?  Also, you may want to check whether there are any records (on the computer with the problem).  Do a DCount, and see what it yields.  I generally use code like the following before running a report from a report selector on the main menu (the report's record source is included in the combo box's row source SQL):
Private Sub cmdReports_Click()
'Created by Helen Feddema 9-2-2003
'Last modified 9-2-2003

On Error GoTo ErrorHandler

   Dim strReportName As String
   Dim strRecordSource As String
   If Nz(Me![cboReports]) <> "" Then
      strReportName = Me![cboReports]
      strRecordSource = Me![cboReports].Column(2)
      If Nz(DCount("*", strRecordSource)) > 0 Then
         If Me![fraReportMode] = 1 Then
               DoCmd.OpenReport ReportName:=strReportName, View:=acPreview
          ElseIf Me![fraReportMode] = 2 Then
            DoCmd.OpenReport ReportName:=strReportName, View:=acNormal
         End If
         MsgBox "No records for this report"
         GoTo ErrorHandlerExit
      End If
   End If

   Exit Sub

   MsgBox "Error No: " & Err.Number & "; Description: " & _
   Resume ErrorHandlerExit

End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where is this code running? Are you doing this in the Report's Open event? Otherwise, you can't change the Recordsource unless the report is opened in Design view (which is a bad idea).

In general, you're much better off building a report with your Recordsource, and then using the WHERE clause portion of the OpenReport method to define the records to be shown. If you need to print to PDF, the method is this:

DoCmd.OpenReport "YourReport", acViewPreview, , "SomeField=SomeCriteria"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, defaultSaveLocation & reportTitle & "-Standard-" & varBranch & ".pdf", False, "", 0, acExportQualityPrint
DoCmd.Close acReport, "YourReport"

Note you can also do this "hidden":

DoCmd.OpenReport "YourReport", acViewPreview, , "SomeField=SomeCriteria", acHidden
<other code here>

I don't see why this line ...

Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"

should work at all , unless there is previous related code that you are not showing here.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Try replacing:

Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"


Reports!Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"
tonelm54Author Commented:
Ok, so Ive tried GRayL suggestion, but just get 'Object required'. Ive tried other suggestions but none seem to work!

What I thought I could do I declare 'Public sql2Load As String' at the top of my module (outside of any sub or function), then I set sql2Load  to my SQL statment, then put the following code in the reports VBA:-
        Private Sub Report_Load()
            Report.RecordSource = sql2Load
        End Sub

But it doesnt seem to fire the Report_Load() in the report when I try and run in my sub:-
            DoCmd.OutputTo acOutputReport, "FaultLogs_Standard", acFormatPDF, defaultSaveLocation & reportTitle & "-Standard-" & varBranch & ".pdf", False, "", 0, acExportQualityPrint

Any ideas?
tonelm54Author Commented:
In the end I wrote to a temp file the SQL statment, then on report load put in that it opens the temp file and uses the SQL statment.
tonelm54Author Commented:
No other solution worked, mine is the only one I could get working in the end

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now