F
Frank
Hi there,
I'm trying to pass a SQL string in access vba (2000) to a report which I
want to create on the fly. Although the report can be created and saved by
the program while being viewed, it does not need to be saved for the users
(just viewed and printed)-- the endgame is to have like 30 reports without
having to create 30 report templates.
I've had some help on this post from Crystal (she's awsome) and she led me
to:
DoCmd.OpenReport pReportName, acViewDesign
but it gives me an error and unless I create and save pReportName it tells
me that that the report doesn't exist. I need to be able to create the report
and assign it the sql string with the fields assigned to the report's text
boxes.
I'm wondering if I can use the CreateReport method before to create the
report and put this data into it. The help isn't very useful on this.
Does anyone ahave any suggestions. Below is what I have so far:
Sub CreateName()
Dim pReportName, pSQL As String
Dim db As DAO.Database, tdf As DAO.TableDef
Dim s As String, fld As DAO.Field
Set db = CurrentDb
pSQL = "SELECT DISTINCTROW [Asset and SLN Data].[Trade TypeID],
" _
& "Sum([Asset and SLN Data].[Orig Notional]) AS [Total Principal
Balance], " _
& "2100000000 AS [Program Limit], " _
& "IIf([Asset and SLN Data].[Orig
Notional]<2100000000,'PASS','FAIL')AS Result " _
& "FROM [Asset and SLN Data] " _
& "GROUP BY [Asset and SLN Data].[Trade TypeID ], 2100000000, " _
& "IIf([Asset and SLN Data].[Orig
Notional]<2100000000,'PASS','FAIL') " _
& "HAVING ((([Asset and SLN Data].[Trade TypeID ])='SLN'));"
pReportName = "My test report"
Debug.Print pSQL, pReportName
Proc_Exit:
On Error Resume Next
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
MsgBox "Query Ran", vbOKOnly
Call SetRecordSource(pSQL, pReportName)
End Sub
Sub SetRecordSource(ByVal pSQL As String, ByVal pReportName As String)
' written by Crystal
' strive4peace2006 at yahoo.com
On Error GoTo err_proc
Dim rpt As Report
DoCmd.Echo False
DoCmd.OpenReport pReportName, acViewDesign
Debug.Print pReportName
Set rpt = Reports(pReportName)
rpt.RecordSource = pSQL
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
DoCmd.Echo True
Exit Sub
err_proc:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " SetRecordSource"
DoCmd.Echo True
'press F8 to step thru lines and fix problem
Stop
Resume
End Sub
I'm trying to pass a SQL string in access vba (2000) to a report which I
want to create on the fly. Although the report can be created and saved by
the program while being viewed, it does not need to be saved for the users
(just viewed and printed)-- the endgame is to have like 30 reports without
having to create 30 report templates.
I've had some help on this post from Crystal (she's awsome) and she led me
to:
DoCmd.OpenReport pReportName, acViewDesign
but it gives me an error and unless I create and save pReportName it tells
me that that the report doesn't exist. I need to be able to create the report
and assign it the sql string with the fields assigned to the report's text
boxes.
I'm wondering if I can use the CreateReport method before to create the
report and put this data into it. The help isn't very useful on this.
Does anyone ahave any suggestions. Below is what I have so far:
Sub CreateName()
Dim pReportName, pSQL As String
Dim db As DAO.Database, tdf As DAO.TableDef
Dim s As String, fld As DAO.Field
Set db = CurrentDb
pSQL = "SELECT DISTINCTROW [Asset and SLN Data].[Trade TypeID],
" _
& "Sum([Asset and SLN Data].[Orig Notional]) AS [Total Principal
Balance], " _
& "2100000000 AS [Program Limit], " _
& "IIf([Asset and SLN Data].[Orig
Notional]<2100000000,'PASS','FAIL')AS Result " _
& "FROM [Asset and SLN Data] " _
& "GROUP BY [Asset and SLN Data].[Trade TypeID ], 2100000000, " _
& "IIf([Asset and SLN Data].[Orig
Notional]<2100000000,'PASS','FAIL') " _
& "HAVING ((([Asset and SLN Data].[Trade TypeID ])='SLN'));"
pReportName = "My test report"
Debug.Print pSQL, pReportName
Proc_Exit:
On Error Resume Next
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
MsgBox "Query Ran", vbOKOnly
Call SetRecordSource(pSQL, pReportName)
End Sub
Sub SetRecordSource(ByVal pSQL As String, ByVal pReportName As String)
' written by Crystal
' strive4peace2006 at yahoo.com
On Error GoTo err_proc
Dim rpt As Report
DoCmd.Echo False
DoCmd.OpenReport pReportName, acViewDesign
Debug.Print pReportName
Set rpt = Reports(pReportName)
rpt.RecordSource = pSQL
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
DoCmd.Echo True
Exit Sub
err_proc:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " SetRecordSource"
DoCmd.Echo True
'press F8 to step thru lines and fix problem
Stop
Resume
End Sub