Assigning a query to a new report in VBA

[Guest]

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

 

[Marshall Barton]

A better way to deal with your needs is to park the SQL
string in a hidden text box on your form. Then the report's
Open event procedure can set its own RecordSource property.
Not only does this approach avoid all the negative
ramifications of changing a design on the fly, but the code
is simpler.

Operating on a report's design in a running application is a
really bad idea for several very serios reasons. Opening a
form/report in Design View should only be used when you are
creating a wizard like procedure to assist yourself in
creating a report, not as something that happens while you
application is being run by users.
--
Marsh
MVP [MS Access]

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.

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

 

[Guest]

Marshall,
Thanks for your response. How would I link this form textbox to the report's
recordsource property, and would this be different from passing the string in
vba;
rpt.RecordSource = pSQL

Are you also saying that once the report opens it will take that SQL
statement and autoformat/autosize (or the closest thing to it) the textboxes
to and headings to display the data? -- this is definiteley what I'm
interested in.

 

[Marshall Barton]

Thanks for your response. How would I link this form textbox to the report's
recordsource property, and would this be different from passing the string in
vba;
rpt.RecordSource = pSQL

No. You can not reliable assign a report's record source
after you use the OpenReport method. The report might have
already started printing by the time the your statement
runs. (There are many asynchrounous activities in Access.)

As I said before, use code in the report's Open event
procedure to copy the SQL statement from the text box to its
own RecordSource. The code would simply be something like
this:
Me.RecordSource = Forms![name of form].[name of textbox]

Are you also saying that once the report opens it will take that SQL
statement and autoformat/autosize (or the closest thing to it) the textboxes
to and headings to display the data? -- this is definiteley what I'm
interested in.

No it won't do that, but you haven't explained how you want
it done, nor how you accomplished it using design view.

Are you saying that the code you posted in your original
question is not complete? I didn't see anything that
required different labels or different size text boxes. If
you can explain what this is all about, maybe I can make a
few suggestions.

 

[Guest]

Marshall,
Thanks again for you input.

Are you saying that the code you posted in your original
question is not complete? I didn't see anything that
required different labels or different size text boxes. If
you can explain what this is all about, maybe I can make a
few suggestions.

Well...at this point I haven't thought about any of the formatting needed
for the reports. I was just trying to get the SQL string into the report as a
first step and was hoping access would make me happy and magically autoformat
the whole thing -- fat chance.
Right now I'm manuall formatting all the reports. Onvce I get done with the
SQL codes and see what parameters and which queries i need to code I'll
revisit using VBA code for the reports. I will come back on this topic. Thanks

As I said before, use code in the report's Open event
procedure to copy the SQL statement from the text box to its
own RecordSource. The code would simply be something like
this:
Me.RecordSource = Forms![name of form].[name of textbox]

I tried to use

Thanks for your response. How would I link this form textbox to the report's
recordsource property, and would this be different from passing the string in
vba;
rpt.RecordSource = pSQL

No. You can not reliable assign a report's record source
after you use the OpenReport method. The report might have
already started printing by the time the your statement
runs. (There are many asynchrounous activities in Access.)

As I said before, use code in the report's Open event
procedure to copy the SQL statement from the text box to its
own RecordSource. The code would simply be something like
this:
Me.RecordSource = Forms![name of form].[name of textbox]

Are you also saying that once the report opens it will take that SQL
statement and autoformat/autosize (or the closest thing to it) the textboxes
to and headings to display the data? -- this is definiteley what I'm
interested in.

No it won't do that, but you haven't explained how you want
it done, nor how you accomplished it using design view.

Are you saying that the code you posted in your original
question is not complete? I didn't see anything that
required different labels or different size text boxes. If
you can explain what this is all about, maybe I can make a
few suggestions.