RecordSource not saving

Posted on 2011-02-21
Medium Priority
Last Modified: 2012-05-11
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
Question by:tonelm54
LVL 31

Expert Comment

by:Helen Feddema
ID: 34944162
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

LVL 86
ID: 34944169
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>

LVL 77

Expert Comment

ID: 34944182
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 44

Expert Comment

ID: 34946050
Try replacing:

Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"


Reports!Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"

Author Comment

ID: 34946194
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?

Accepted Solution

tonelm54 earned 0 total points
ID: 35127327
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.

Author Closing Comment

ID: 35304184
No other solution worked, mine is the only one I could get working in the end

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question