Joe Rojas
JRojas at tnco-inc.com
Tue Feb 25 09:42:30 CST 2003
Hello Francisco,
I was following this thread and I tried your example A that you mention
below and I ran into a bit of a snag. In the reports OnOpen event, I first
get some values for my variables and the execute the next line of code:
Me.RecordSource = "EXEC sproc_PartsShipped " & intCustID & ", '" & dtStart &
"', '" & dtEnd & "'"
Upon execution, it gives me an error similar to: "The record source 'EXEC
sproc_PartsShipped 0, '2/1/2003', '2/25/2003'" does not exist.
I made sure to cut and paste the sproc name into my code to eliminate any
typo error.
Any idea what I am doing wrong?
Thanks,
Joe Rojas
jrojas at tnco-inc.com
-----Original Message-----
From: Francisco H Tapia [mailto:my.lists at verizon.net]
Sent: Monday, February 24, 2003 7:13 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Sending parameters to a report
So I see not everything is so bright over on the XP side, This is a bug in
ADP's one that I am hoping will be fixed for the next version of ADPs. The
best way to solve your problem is one of 2 things... EITHER
a) set your rowsource through code... as me.Rowsource = "EXEC
stp_MyProcedure " & VariableContainingOpenArgs1 & ", " &
VariableContainingOpenArgs2
remember that the syntax is SQL so if you are passing date or string
parameters you'll also need to include the ' single quotes.
b) create a generic form that contains the values to your parameters and set
them prior to opening your report. For some reason the sproc runs before
the input paramters are set on the form or report...
HTHs
-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "David Emerson" <davide at dalyn.co.nz>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Monday, February 24, 2003 3:53 PM
Subject: RE: [dba-SQLServer]Sending parameters to a report
: Thanks for the response. Unfortunately there are two complications with
: this report -
: 1) It could be called from two different forms with different fields
: that are used for the parameters
: 2) It also is used in a situation where there might be several customers
: selected from a list box and copies of the report are to be printed for
: all of them. In the A97 version it was easy by just including the where
: clause in the openReport command but it doesn't seem as easy with SQL.
:
: David
:
: At 24/02/2003, you wrote:
:
:
: David,
:
: I've never been successful in setting the input parameters through code.
: What I do, is in the property box, under Input Parameters I reference
: somethiung on an open form such as:
:
: @DeptID = Forms!frmWiz!subcrit!txtParam_Dept
:
: I usually set the record source for the report in the on_open of the
: code such as:
:
: Me.RecordSource = "dbo.proc_DM_CCE_Phone_List_dyn"
:
: Seems to me that the way you describe below should work, but it's never
: worked for me.
:
: --Susan
:
:
:
:
: -----Original Message-----
:
:
: From: David Emerson [ mailto:davide at dalyn.co.nz
: <mailto:davide at dalyn.co.nz> ]
:
:
: Sent: Monday, February 24, 2003 3:37 PM
:
:
: To: dba-SQLServer at databaseadvisors.com
:
:
: Subject: [dba-SQLServer]Sending parameters to a report
:
:
:
: I have a problem with a report in an AXP/SQL2K database.
:
:
:
: I have a button on a customer form which is supposed to open up a report
: with just the current customers information on it. In the OpenReport
: statement I send the inputParameters via the OpenArgs to the report and
: then in the report's Open event set me.InputParameters = me.OpenArgs.
:
:
: The main report sproc then uses the InputParameters to select the
: correct record (theoretically).
:
:
:
: But I must be missing something because even though the inputparameter
: property is being updated, the sproc is not using the information to
: retrieve the correct data. Here is the main parts of the sproc -
:
:
:
: ALTER PROCEDURE sprptAccounts
:
:
:
: (
:
:
: @txtCustID int,
:
:
: @StatementNumber int
:
:
: )
:
:
:
: AS
:
:
: SET NOCOUNT ON
:
:
:
:
:
: SELECT tblCustomers.CustomerID, tblCustStatement.StatementID,
:
:
: case
:
:
: when [Residential]=1
:
:
: then [CSurname] + [C1stName]
:
:
: else [TradingName]
:
:
: end
:
:
: AS SortName, tblCustStatement.SMName,
: tblCustStatement.SMAddress1, tblCustStatement.SMAddress2,
:
:
: tblCustStatement.SMSuburb, tblCustStatement.SMCity,
: tblCustStatement.SMPostCode, tblCustStatement.SAccountNo,
:
:
: case
:
:
: when Not ([PrevStatementDate] is null)
:
:
: then DateAdd("d",1,[PrevStatementDate])
:
:
: else [PrevStatementDate]
:
:
: end
:
:
: AS StatFirstDate, tblCustStatement.StatementDate,
: tblCustStatement.StatementNumber, tblCustStatement.CurrentMth,
:
:
: [OneMonth]+[TwoMonths]+[ThreeMonths] AS Overdue,
: tblCustStatement.OneMonth, tblCustStatement.TwoMonths,
:
:
: tblCustStatement.ThreeMonths,
: tblCustStatement.ComBondBal, tblCustStatement.Processed,
: tblCustomers.AccStatus, tblCustomers.AccFreq,
: tblCustStatement.DDRegistered
:
:
: FROM tblCustomers INNER JOIN tblCustStatement ON
: tblCustomers.CustomerID = tblCustStatement.CustIDNo
:
:
: WHERE (tblCustomers.CustomerID = @txtCustID) and
: (tblCustStatement.StatementNumber = @StatementNumber)
:
:
:
:
: The call to open the report is -
:
:
: DoCmd.OpenReport "rptAccounts", acViewPreview, , , , "@CustID int = " &
: txtCustIDNo & ", @StatementNumber int = " & txtStatementNumber
:
:
:
: An example of the openarg sent is -
:
:
: @CustID int = 773500661, @StatementNumber int = 11
:
:
:
: The report's Open event is
:
:
:
: Private Sub Report_Open(Cancel As Integer)
:
:
:
: Me.InputParameters = Me.OpenArgs
:
:
:
: End Sub
:
:
:
: Am I going about this all wrong?
:
:
:
: Regards
:
:
:
: David Emerson
:
:
: DALYN Software Ltd
:
:
: 25b Cunliffe St, Johnsonville
:
:
: Wellington, New Zealand
:
:
: Ph/Fax (877) 456-1205
:
:
:
:
:
:
: Regards
:
: David Emerson
: DALYN Software Ltd
: 25b Cunliffe St, Johnsonville
: Wellington, New Zealand
: Ph/Fax (877) 456-1205
:
:
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please notify
the sender as soon as possible and destroy this message. While TNCO, Inc.
uses virus protection, the recipient should check this email and any
attachments for the presence of viruses. TNCO, Inc. accepts no liability for
any damage caused by any virus transmitted by this email.