Solved

RecordSource not saving

Posted on 2011-02-21
7
463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

697 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