ADP report based on stored procedure.

[jordan.krisiskoncepts]

Hello,

I have an ADP and a sql server 2005 express backend. I have a form
that is based on a ado recordset. I want to open a report based on
the same recordset. The form's recordset is based on a stored
procedure with multiple required input, optional input and output
parameters. I know you can set the value of a report's recordsource
to a SP and then specifiy the input parameter values programatically
in VBA during the open event of the report.

2 questions:
- Can I use a procedure that has output paramters with reports, or do
I have to create a new one without them?
- Can a report be based on a recordset? ie "set report.recordset = rs"

 

[Sylvain Lafontaine]

Theoritically, you shouldn't have any problem setting the record source a of
report to a recordset; however, there have been reports of people having
problems with this method. For example, look at the thread from Keyth G
Hicks in this newsgroup on 2007-11-08. Personally, I don't use this method
because it's impossible to use it with sub-reports.

As for presence of output parameters, they shouldn't have any effect on the
report.

 

[jordan.krisiskoncepts]

Theoritically, you shouldn't have any problem setting the record source a of
report to a recordset; however, there have been reports of people having
problems with this method. For example, look at the thread from Keyth G
Hicks in this newsgroup on 2007-11-08. Personally, I don't use this method
because it's impossible to use it with sub-reports.

As for presence of output parameters, they shouldn't have any effect on the
report.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)








- Show quoted text -

thanks for the reply,

I read the posts that you mentioned. I am kind of stuck, what method
do you use. You mentioned something about using the recordsource with
the name of the stored procedure. I tried that, but had trouble
getting it to work, it kept throwing errors about not passing required
output parameters to the procedure.

Do you think this problem might be a refferenece issue, maybe I don't
have the right library linked to my project?

I am going to try importing the whole project into a new blank ADP to
see if that fixes anything.

Do you have any other ideas?

 

[Sylvain Lafontaine]

Besides some exception, I always use the InputParameters method but I don't
set it dynamically in the OnOpen event of the report: I always set it
statically, pointing the parameters toward the control of a form that I use
as a generator (ie; the form where the user enters parameters such as the
beginning and ending dates, etc.); something like:

@ComboIdRoute int = Forms!d_Rapports!ComboIdRoute,
@ComboIdEcole int = Forms!d_Rapports!ComboIdEcole,
@ComboDateJour1 datetime = Forms!d_Rapports!ComboDateJour,
@ComboDateJour2 datetime = Forms!d_Rapports!ComboDateJour2,
@ComboUserCode nvarchar(10) = Forms!d_Rapports!ComboUserCode

To my knowledge, the keywords int, datetime, nvarchar(10), etc.; are
optional here and can be omitted. (Also, I'm not sure but I think that on
some installations, you must use the semi-comma ";" instead of the comma ","
to separate the items.). See the thread "Form Control Reference in SQL
Server 2005" from Klatuu on 2007-11-08 for more details on this for other
options such a dynamically building the query string using the EXEC
statement.

 

[jordan.krisiskoncepts]

Besides some exception, I always use the InputParameters method but I don't
set it dynamically in the OnOpen event of the report: I always set it
statically, pointing the parameters toward the control of a form that I use
as a generator (ie; the form where the user enters parameters such as the
beginning and ending dates, etc.); something like:

@ComboIdRoute int = Forms!d_Rapports!ComboIdRoute,
@ComboIdEcole int = Forms!d_Rapports!ComboIdEcole,
@ComboDateJour1 datetime = Forms!d_Rapports!ComboDateJour,
@ComboDateJour2 datetime = Forms!d_Rapports!ComboDateJour2,
@ComboUserCode nvarchar(10) = Forms!d_Rapports!ComboUserCode

To my knowledge, the keywords int, datetime, nvarchar(10), etc.; are
optional here and can be omitted. (Also, I'm not sure but I think that on
some installations, you must use the semi-comma ";" instead of the comma ","
to separate the items.). See the thread "Form Control Reference in SQL
Server 2005" from Klatuu on 2007-11-08 for more details on this for other
options such a dynamically building the query string using the EXEC
statement.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)











- Show quoted text -

thank you,

i will give it a try

 

[jordan.krisiskoncepts]

thank you,

i will give it a try- Hide quoted text -

- Show quoted text -

I solved the trouble i was having with assigning the recordset to the
report during the open event.

I had opened a connection, got a recordset from a sql server stored
procedure, bound the recordset to the report's recordset property, and
then closed the connection and recordset (in good best practices
fashion in order to clear variable resources). What I found was that
if I closed the recordset within the Report_Open sub of the report, it
did not work. The open event has to complete before the recordset can
be closed and released.

Ulitmatley I wanted to clone the recordset from the open form it was
based on so that I did not have to call another stored procedure thus
reducing network and sql server trafic. I was able to do this with
the following code:

Option Compare Database
Option Explicit

Public rsCust As ADODB.Recordset

Private Sub Report_Load()
Set rsCust = Nothing
End Sub

Private Sub Report_Open(Cancel As Integer)
Set rsCust = New ADODB.Recordset

''' clone recordset from customer search subform
Set rsCust = Forms!frmCustomerSearch.Form!
fsubCustomerSearch.Form.RecordsetClone

''' set the recordset value of this report to the recordset clone
from the search subreport
Set Me.Recordset = rsCust

End Sub

Thanks for your help.

PS

I still wonder if there is a way to do this from the form comand
button that is used to open this report. Like passing the recordset
in the openargs, but I tried and was unable to do so (it appears
openargs only accepts type variant, not recordset). That would have
been nice because then this report could have been called very easily
from any form, now I will have to figure out how to determine which
form opened this report and assign the appropraite recordset
accordingly, but I ramble...

 

[Sylvain Lafontaine]

Put public variables in your report and use them as the source for your
parameters:

Public D1 As Date
Public D2 As Date

Private Sub Report_Open(Cancel As Integer)
D1 = #10/1/2007#
D2 = #10/1/2007#
End Sub


and use the Reports collection for the InputParameters:

@Day1 datetime = Reports!MyReport.D1, @Day2 datetime = Reports!MyReport.D2


Of course, you can set the values for these public variables from anywhere
or any form and not only from the Report_Open event. You could also use a
global module but I don't remember the exact syntaxe.

 

[Sylvain Lafontaine]

Also, as the RecordsetClone could be already used from your form (for
example in a search and locate procedure) and also to the fact that there
have been many reports of problem/bug with the call to this function and
ADP, you should use Recordset.Clone instead of RecordsetClone in order to
have a separate clone from the one used internally in the method
RecordsetClone and to avoid possible memory corruption/problem/bug.

 

[jordan.krisiskoncepts]

Also, as the RecordsetClone could be already used from your form (for
example in a search and locate procedure) and also to the fact that there
have been many reports of problem/bug with the call to this function and
ADP, you should use Recordset.Clone instead of RecordsetClone in order to
have a separate clone from the one used internally in the method
RecordsetClone and to avoid possible memory corruption/problem/bug.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message








- Show quoted text -

thanks again,

I used your suggestion for the recordset.clone, but get the following
error in a test:

"run-time error 3704
Operation is not allowed when the object is closed"

here is my test code:

Private Sub cmdPrint_Click()

Dim rsTest1 As ADODB.Recordset
Set rsTest1 = New ADODB.Recordset
Dim rsTest2 As ADODB.Recordset
Set rsTest2 = New ADODB.Recordset

Set rsTest1 = Me.fsubCustomerSearch.Form.Recordset
Set rsTest2 = rsTest1.Clone********error occurs here

ExitSub:
Exit Sub

End Sub

 

[jk]

thanks again,

I used your suggestion for the recordset.clone, but get the following
error in a test:

"run-time error 3704
Operation is not allowed when the object is closed"

here is my test code:

Private Sub cmdPrint_Click()

Dim rsTest1 As ADODB.Recordset
Set rsTest1 = New ADODB.Recordset
Dim rsTest2 As ADODB.Recordset
Set rsTest2 = New ADODB.Recordset

Set rsTest1 = Me.fsubCustomerSearch.Form.Recordset
Set rsTest2 = rsTest1.Clone********error occurs here

ExitSub:
Exit Sub

End Sub- Hide quoted text -

- Show quoted text -

i can get the clone to wrk wehn i use a sql string but not a refrence
to a form's recordset
EXAMPLE:

Private Sub cmdPrint_Click()

Dim rsTest1 As ADODB.Recordset
Set rsTest1 = New ADODB.Recordset
Dim rsTest2 As ADODB.Recordset
Set rsTest2 = New ADODB.Recordset

Set rsTest1 = Me.fsubCustomerSearch.Form.Recordset********DOES NOT
WORK
rsTest1.Open "Select * From Customer", CurrentProject.Connection,
adOpenStatic, adLockOptimistic********THIS DOES WORK
Set rsTest2 = rsTest1.Clone

ExitSub:
Exit Sub

End Sub

 

[Sylvain Lafontaine]

If you have so much trouble with reusing a recordset as the source of your
reports, while are you trying to keep using this method? I don't use this
method myself, so I'm sorry but I cannot help you any further on this topic.