Form Control Reference in SQL Server 2005

[Klatuu]

I am working on my first conversion to adp using AC2003 and SQL 2005
All my action queries that referenced a form control failed to convert to
Stored Procedures.
I have been told it can't be done in an adp. What is the work around for
this problem?

 

[Sylvain Lafontaine]

You must use the InputParameters property or dynamically build a string for
the record source using a full Select statement or an EXEC statement in the
case that you want to call a stored procedure:

Me.RecordSouce = "Select * from dbo.MyTable ..."

Me.RecordSource = "EXEC dbo.MySP 10, 20, 30, '20071001', ... "

For the InputParameters property, take a look with Google:

http://groups.google.ca/group/micro...r/search?q=InputParameters&start=0&scoring=d&

 

[Klatuu]

Thanks, Sylvain. I got as far as figuring out the input parameter part. I
am struggling along with it. This is for a record source for a report.
This is what I have so far.

Record source:
Select * from qryMonthlySubs ?

Input Parameters:
cboperiod char = [Forms]![frmReports]![cboPeriod]

And the SQL Function (don't know why it got converted to a function)

ALTER FUNCTION [dbo].[qryMonthlySubs] (@cboPeriod varchar (10))
RETURNS TABLE
AS RETURN (SELECT tblSUB.*, tblPIR.txtPropInvFormat, tblPIR.txtPropertyName,
tblPeriod.txtPerDescription
FROM tblPeriod, tblSUB INNER JOIN tblPIR ON (tblSUB.txtContractDBID =
tblPIR.txtContractDBID)
WHERE (((tblSUB.datSubmitDate)>=datBegDate And
(tblSUB.datSubmitDate)<=datEndDate) AND
((tblPeriod.txtPeriodID)=@cboPeriod)))

When I try to run it, I get a message box with
Incorrect Syntax near 'P1'.

Nowhere is there a P1.

I will look at the link you sent. thanks

 

[Sylvain Lafontaine]

When you use the placement holder ?, you must not give any name or type for
the parameter, so Input Parameters become simply
[Forms]![frmReports]![cboPeriod] or ?=[Forms]![frmReports]![cboPeriod]:

[Forms]![frmReports]![cboPeriod]

or:

?=[Forms]![frmReports]![cboPeriod]

When used with the *name* of a stored procedure, you must give the name of
the parameter as used in the procedure, including the @:

@cboperiod = [Forms]![frmReports]![cboPeriod]

You can also add the type of the field but I don't think/know if this has
any value:

@cboperiod varchar(10) = [Forms]![frmReports]![cboPeriod]


Create dbo.[qryMonthlySubs] (@cboPeriod varchar (10))
AS
SELECT tblSUB.*, tblPIR.txtPropInvFormat, tblPIR.txtPropertyName,
tblPeriod.txtPerDescription

FROM tblPeriod, tblSUB INNER JOIN tblPIR ON (tblSUB.txtContractDBID =
tblPIR.txtContractDBID)

WHERE (((tblSUB.datSubmitDate)>=datBegDate And
(tblSUB.datSubmitDate)<=datEndDate) AND
((tblPeriod.txtPeriodID)=@cboPeriod)))


The parameters that are used for the main form can also be used for SP used
as row source for controls such as the combobox and the listbox. However,
as all parameters listed in the InputParameters property must also be part
of the collection of parameters used for the main SP of the form; this
somewhat limits its usefulness.

Finally, any time you make a change to the InputParameters property, you
must close and reopen the form and not just only switch from design to view.

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

Thanks, Sylvain. I got as far as figuring out the input parameter part.
I
am struggling along with it. This is for a record source for a report.
This is what I have so far.

Record source:
Select * from qryMonthlySubs ?

Input Parameters:
cboperiod char = [Forms]![frmReports]![cboPeriod]

And the SQL Function (don't know why it got converted to a function)

ALTER FUNCTION [dbo].[qryMonthlySubs] (@cboPeriod varchar (10))
RETURNS TABLE
AS RETURN (SELECT tblSUB.*, tblPIR.txtPropInvFormat,
tblPIR.txtPropertyName,
tblPeriod.txtPerDescription
FROM tblPeriod, tblSUB INNER JOIN tblPIR ON (tblSUB.txtContractDBID =
tblPIR.txtContractDBID)
WHERE (((tblSUB.datSubmitDate)>=datBegDate And
(tblSUB.datSubmitDate)<=datEndDate) AND
((tblPeriod.txtPeriodID)=@cboPeriod)))

When I try to run it, I get a message box with
Incorrect Syntax near 'P1'.

Nowhere is there a P1.

I will look at the link you sent. thanks
--
Dave Hargis, Microsoft Access MVP

You must use the InputParameters property or dynamically build a string
for
the record source using a full Select statement or an EXEC statement in
the
case that you want to call a stored procedure:

Me.RecordSouce = "Select * from dbo.MyTable ..."

Me.RecordSource = "EXEC dbo.MySP 10, 20, 30, '20071001', ... "

For the InputParameters property, take a look with Google:

http://groups.google.ca/group/micro...r/search?q=InputParameters&start=0&scoring=d&

 

[Klatuu]

Thanks for the info. I work on this. Once I get this one done, I should be
able to breeze through the rest of them.

This is my first attempt at a conversion, so there is a lot of learning to do.
--
Dave Hargis, Microsoft Access MVP

When you use the placement holder ?, you must not give any name or type for
the parameter, so Input Parameters become simply
[Forms]![frmReports]![cboPeriod] or ?=[Forms]![frmReports]![cboPeriod]:

[Forms]![frmReports]![cboPeriod]

or:

?=[Forms]![frmReports]![cboPeriod]

When used with the *name* of a stored procedure, you must give the name of
the parameter as used in the procedure, including the @:

@cboperiod = [Forms]![frmReports]![cboPeriod]

You can also add the type of the field but I don't think/know if this has
any value:

@cboperiod varchar(10) = [Forms]![frmReports]![cboPeriod]


Create dbo.[qryMonthlySubs] (@cboPeriod varchar (10))
AS
SELECT tblSUB.*, tblPIR.txtPropInvFormat, tblPIR.txtPropertyName,
tblPeriod.txtPerDescription

FROM tblPeriod, tblSUB INNER JOIN tblPIR ON (tblSUB.txtContractDBID =
tblPIR.txtContractDBID)

WHERE (((tblSUB.datSubmitDate)>=datBegDate And
(tblSUB.datSubmitDate)<=datEndDate) AND
((tblPeriod.txtPeriodID)=@cboPeriod)))


The parameters that are used for the main form can also be used for SP used
as row source for controls such as the combobox and the listbox. However,
as all parameters listed in the InputParameters property must also be part
of the collection of parameters used for the main SP of the form; this
somewhat limits its usefulness.

Finally, any time you make a change to the InputParameters property, you
must close and reopen the form and not just only switch from design to view.

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

Thanks, Sylvain. I got as far as figuring out the input parameter part.
I
am struggling along with it. This is for a record source for a report.
This is what I have so far.

Record source:
Select * from qryMonthlySubs ?

Input Parameters:
cboperiod char = [Forms]![frmReports]![cboPeriod]

And the SQL Function (don't know why it got converted to a function)

ALTER FUNCTION [dbo].[qryMonthlySubs] (@cboPeriod varchar (10))
RETURNS TABLE
AS RETURN (SELECT tblSUB.*, tblPIR.txtPropInvFormat,
tblPIR.txtPropertyName,
tblPeriod.txtPerDescription
FROM tblPeriod, tblSUB INNER JOIN tblPIR ON (tblSUB.txtContractDBID =
tblPIR.txtContractDBID)
WHERE (((tblSUB.datSubmitDate)>=datBegDate And
(tblSUB.datSubmitDate)<=datEndDate) AND
((tblPeriod.txtPeriodID)=@cboPeriod)))

When I try to run it, I get a message box with
Incorrect Syntax near 'P1'.

Nowhere is there a P1.

I will look at the link you sent. thanks
--
Dave Hargis, Microsoft Access MVP

You must use the InputParameters property or dynamically build a string
for
the record source using a full Select statement or an EXEC statement in
the
case that you want to call a stored procedure:

Me.RecordSouce = "Select * from dbo.MyTable ..."

Me.RecordSource = "EXEC dbo.MySP 10, 20, 30, '20071001', ... "

For the InputParameters property, take a look with Google:

http://groups.google.ca/group/micro...r/search?q=InputParameters&start=0&scoring=d&

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


I am working on my first conversion to adp using AC2003 and SQL 2005
All my action queries that referenced a form control failed to convert
to
Stored Procedures.
I have been told it can't be done in an adp. What is the work around
for
this problem?