Input Parameters Property in ADP Project

[Cheryl3D]

Hello:

I'm trying to use the Input Parameters property in an ADP Project. I'm using SQL Server 2000 as the database server. I've tried using the following syntax in the Input parameters property on a report object within the ADP project:

@ID = [Forms]![FrmSelectAnnex]![Text1], @ID_Num = [Forms]![FrmSelectAnnex]![POCName]


These are the input parameters to pass to the following stored procedure, which is the record source for the report:

Alter PROCEDURE dbo.procqrySelectAnnex
@ID nvarchar(6),
@ID_Num nvarchar(6)
AS
SELECT DISTINCT
dbo.qrySupplierBase_View.[Host Base],
dbo.qrySupplierBase_View.[Office Symbol],
dbo.qrySupplierBase_View.CertStat,
dbo.qrySupplierBase_View.Host_ID,
dbo.qrySupplierBase_View.CertPOC,
dbo.qrySupplierBase_View.Comptroller,
dbo.qrySupplierBase_View.ID,
dbo.qrySupplierBase_View.SRAN,
dbo.qrySupplierBase_View.JULIAN,
dbo.qrySupplierBase_View.Receiver,
dbo.qrySupplierBase_View.WorkyearCost,
dbo.TabAnnex.[ID #],
dbo.qrySupplierBase_View.ID AS ID_1,
dbo.TabAnnex.Categories,
dbo.TabAnnex.SubCategory,
dbo.TabAnnex.Support,
dbo.TabAnnex.RDescription,
dbo.TabAnnex.RUnits,
dbo.TabAnnex.RUnitCost,
dbo.TabAnnex.NRManpower,
dbo.TabAnnex.NRDescription,
dbo.TabAnnex.NRUnits,
dbo.TabAnnex.NRUnitCost,
COALESCE (dbo.TabAnnex.NRManpower * dbo.qrySupplierBase_View.WorkyearCost, 0)
AS NRWorkyearCost,
COALESCE (dbo.TabAnnex.RUnits * dbo.TabAnnex.RUnitCost, 0) AS RCost,
COALESCE (dbo.TabAnnex.NRUnits * dbo.TabAnnex.NRUnitCost, 0) AS NRCost,
dbo.TabAnnex.Document,
dbo.TabAnnex.Billed,
dbo.TabAnnex.Coll,
SUM(dbo.qryAnnex_View.RCost) AS RCost_Sum, SUM(dbo.qryAnnex_View.NRWorkyearCost + dbo.qryAnnex_View.NRCost) AS NRCost_Sum,
SUM(dbo.qryAnnex_View.NRManpower) AS Manpower_sum
FROM dbo.TabAnnex INNER JOIN
dbo.qrySupplierBase_View ON dbo.TabAnnex.[ID #] = dbo.qrySupplierBase_View.ID INNER JOIN
dbo.qryAnnex_View ON dbo.qrySupplierBase_View.ID = dbo.qryAnnex_View.[ID #]
WHERE (dbo.qrySupplierBase_View.Host_ID = @ID_Num) AND (dbo.qrySupplierBase_View.ID = @ID)
GROUP BY dbo.qrySupplierBase_View.[Host Base], dbo.qrySupplierBase_View.[Office Symbol], dbo.qrySupplierBase_View.CertStat,
dbo.qrySupplierBase_View.Host_ID, dbo.qrySupplierBase_View.CertPOC, dbo.qrySupplierBase_View.Comptroller, dbo.qrySupplierBase_View.ID,
dbo.qrySupplierBase_View.SRAN, dbo.qrySupplierBase_View.JULIAN, dbo.qrySupplierBase_View.Receiver, dbo.qrySupplierBase_View.WorkyearCost,
dbo.TabAnnex.[ID #], dbo.TabAnnex.Categories, dbo.TabAnnex.SubCategory, dbo.TabAnnex.Support, dbo.TabAnnex.RDescription,
dbo.TabAnnex.RUnits, dbo.TabAnnex.RUnitCost, dbo.TabAnnex.NRManpower, dbo.TabAnnex.NRDescription, dbo.TabAnnex.NRUnits,
dbo.TabAnnex.Coll, dbo.TabAnnex.Billed, dbo.TabAnnex.Document, dbo.TabAnnex.NRUnitCost

When I run the report, the parameter values from the form 'FrmSelectAnnex' are not passed to the stored procedure via the report's Input Parmeters property.

The report only is generated via 2 dialog boxes that pop-up, one for ID, the other for ID_Num. Once I enter those into the 2 pop-ups, then the report generates iteself just fine.

The problem is that I don't want the 2 pop-ups to appear.

I want them to not appear and the stored procedure just pick-up the passed values.


Later, I tried an alternative method.

I entered the following sql syntax into the report's On Open procedure:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "dbo.procqrySelectAnnex" & " @ID = " & [Forms]![FrmSelectAnnex]![Text1].Value & _
"@ID_Num = " & [Forms]![FrmSelectAnnex]![POCName].Value & ""

End Sub


(I made the form unbound for this alternative.)

When I ran the report the received the following Microsoft Access error:

The record source 'dbo.procqrySelectAnnex @ID= 102@ID_Num = 4' specified on the form or report does not exist.

Is something wrong with my syntax?

Any help to get the report to work properly based on the input parameters without the 2 dialog boxes appearing, would be greatly appreciated.

Thanks,
Cheryl

 

[cmmrfrds]

I usually pass the variables through a function which fairly flexible.

Example in the InputParameter property. There are 4 parameters in the stored procedure named @beginDate etc..

@beginDate=ReturnBegDate(),@endDate=ReturnEndDate(),@supervisor=ReturnSupervisor(),@employeeIN=ReturnEmployeeID()

In a standard module.
Public pubEmployeeID As Double
Public pubBegDate As Date
Public pubEndDate As Date
Public pubSupervisor As Long

Function ReturnSupervisor() As Long
ReturnSupervisor = pubSupervisor
End Function
Function ReturnEmployeeID() As Long
ReturnEmployeeID = pubEmpID
End Function
Function ReturnBegDate() As Date
ReturnBegDate = pubBegDate
End Function
Function ReturnEndDate() As Date
ReturnEndDate = pubEndDate
End Function

In a Form load the variables and then open the Form with the stored procedure.

 

[Cheryl3D]

Thanks cmmrfrds:

I'm trying to follow your advise but I need a little more clarification. I'll create the functions in a standard module easily, and then provide the 2 parameter variables in the InputParameter property on the report, not a form.

But on the last line of your instructions did you mean to say
'In a Report load the variables and then open the Report with the stored procedure.'?

Also, please explain further what you mean by in a form/report load the variables, how?

Please clarify and provide an example. Your help is greatly appreciated.

Thanks,
Cheryl

 

[cmmrfrds]

What I meant was to load the variables before opening the Form/Report. Once the variables are loaded the functions will take care of returning the variables when the Form/Report is opened. I suppose an option is to load the variables in the OnOpen event if they are passed as OpenArgs. Hope this helps.

 

[Cheryl3D]

Thanks again cmmrfrds:

How exactly do I load the variables in the OnOpen event of the report if they are passed as OpenArgs?

Would the code for the report look like this?

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = Me.OpenArgs

End Sub


Also, I created the following in a module called ModOrgAdHoc:

Public pubID As Variant
Public pubIDNum As Variant

Function ReturnID() As Variant
ReturnID = pubID
End Function


Function ReturnIDNum As Variant
ReturnIDNum = pubIDNum
End Function

Thanks,
Cheryl

 

[cmmrfrds]

What you have looks good. Normally, I would update the variables in a Form. Maybe in the afterupdate event of a field like txtPubId.

pubID = Me.txtPubId
' at this point the variable is loaded and is available which is easier than passing as an OpenArgs which would be a string and you would need to parse to pull out the 2 variables.

'You wouldn't do this unless you were passing an SQL statement or equivalent.
Me.RecordSource = Me.OpenArgs

The easiest is to put your 2 fields on a Form and in the afterupdate event load the variables and then maybe have a button to fire off the report.