Solved

RecordSource not saving

Posted on 2011-02-21
7
461 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 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