Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel spreadsheet. I'm having trouble
with my code at the point at which it hits ".ReadRecords" -- the
module just runs and runs without generating anything. I've gotten
this code to correctly save .rpt files without any data, but not with
data, nor have I been able to export to Excel. Can anyone see what
I'm not doing right? Below is my code with extraneous stuff taken
out.
Thanks so much for any help.
Robin
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database
Option Explicit
Public appl As New CRAXDRT.Application
Public db As DAO.Database
Public rst1 As DAO.Recordset
Public rst2 As DAO.Recordset
Public rst3 As DAO.Recordset
Public rpt As New CRAXDRT.Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub AutoGenerateReports()
Dim strSelectionFormula As String
Dim strParameter As String
Dim strNames As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strReportToRun As String
Dim strReportDestination As String
Dim strThisWeeksReport As String
Dim rst As DAO.Recordset
Dim strAllRptOwners As String
Dim LastSwipe As Date
Dim strReportData As String
On Error GoTo ErrorHandler
Set appl = CreateObject("CrystalRuntime.Application")
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblautogenreports", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qry_autogenrptsnames", dbOpenDynaset)
'set recordset for report
strReportData = "SELECT EvnLog.Loc, EvnLog.Dev, EvnLog.TimeDate,
EvnLog.Code, EvnLog.LName, EvnLog.FName, DEV.TNA, EvnLog.Event,
EvnLog.IOName, Evn.Event " & _
"FROM (Evn INNER JOIN EvnLog ON Evn.Event =
EvnLog.Event) INNER JOIN DEV ON EvnLog.Dev = DEV.Device WHERE " &
strSelectionFormula & ";"
strReportData = Replace(strReportData, "{", "")
strReportData = Replace(strReportData, "}", "")
Set rst3 = db.OpenRecordset(strReportData)
' open report and supply parameters, data, and then export as Excel to
file
Set rpt = appl.OpenReport("c:\TestingAutoGenRpts\TimeAttenda nceRpt_Qry
- 05-13-04.rpt")
With rpt
.DiscardSavedData
.FormulaFields(11).Text = "'" & strReportToRun & "'"
.FormulaFields(9).Text = "'" & strNames & "'"
.FormulaFields(13).Text = "#" & datBeginDate & "#"
.RecordSelectionFormula = strSelectionFormula
.Database.SetDataSource rst3
.Database.Verify
.ReadRecords 'THIS IS WHERE IT HANGS
.ExportOptions.DestinationType = crEDTDiskFile
.ExportOptions.FormatType = crEFTExcel80
.ExportOptions.DiskFileName = strReportDestination & "\" &
strThisWeeksReport & ".xls"
.Export True
End With
