Solved

RecordSource not saving

Posted on 2011-02-21
7
462 Views
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
0
Comment
Question by:tonelm54
7 Comments
 
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
      Else
         MsgBox "No records for this report"
         GoTo ErrorHandlerExit
      End If
   Else
      Me![cboReports].SetFocus
      Me![cboReports].Dropdown
   End If

ErrorHandlerExit:
   Exit Sub

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

End Sub

Open in new window

0
 
LVL 84
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>




0
 
LVL 77

Expert Comment

by:peter57r
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 44

Expert Comment

by:GRayL
ID: 34946050
Try replacing:

Report_FaultLogs_Standard.RecordSource = "SELECT * from myTable;"

with:

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

Author Comment

by:tonelm54
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?
0
 

Accepted Solution

by:
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.
0
 

Author Closing Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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