Forms with ADP

  • Thread starter Thread starter Mitchell_Collen
  • Start date Start date
M

Mitchell_Collen

This is my stored procedure code. However without the syntax [Form].. it is
only useful with a report. I want to open up a record set from a form using
input parameters, I am not sure why in adp I can not use the = [Form]!
[rptEmployee]![StartDate] ...

Please help me.
Here is the code. Thanks in advance.

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,@empKey varchar(20))
AS SELECT *
FROM dbo.phmPYXHx
WHERE (TxDatetime BETWEEN @startDatetime = [Forms]![rptEmployee]![StartDate]
AND @endDatetime = [Forms]![rptEmployee]![EndDate]) and (UserID = @empKey =
[Forms]![rptEmployee]![EmployeeKey])
 
This is my stored procedure code. However without the syntax [Form].. it is
only useful with a report. I want to open up a record set from a form using
input parameters, I am not sure why in adp I can not use the = [Form]!
[rptEmployee]![StartDate] ...

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,@empKey varchar(20))
AS SELECT *
FROM dbo.phmPYXHx
WHERE (TxDatetime BETWEEN @startDatetime = [Forms]![rptEmployee]![StartDate]
AND @endDatetime = [Forms]![rptEmployee]![EndDate]) and (UserID = @empKey =
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...

SQL Server dowsn't know what [Forms].... is.
In the SP code you have tu use T-SQL parameters, as following:

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,
@empKey varchar(20))
AS
SELECT *
FROM dbo.phmPYXHx
WHERE
(TxDatetime BETWEEN @startDatetime AND @endDatetime)
AND
(UserID = @empKey)

Then, in the InputParameters form property you will enter the values as
following (all in the same row):

@startDatetime datetime = [Forms]![rptEmployee]![StartDate],
@endDatetime datetime = [Forms]![rptEmployee]![EndDate],
@empKey = [Forms]![rptEmployee]![EmployeeKey]

p.s.: why do you name a form "rpt..." instead of "frm..."?
 
Thanks Sylvan for helping me out twice today! But, yea, I am using the adp
project with no books and that even though odd did work. I don't know why, it
just wouldn't work with record sets.

-Misty

Sylvain said:
I'm very surprised that you can use the syntax
[Forms]![rptEmployee]![StartDate] even with a report.

For the forms (and reports?), take a look at the previous threads in this
forum; as this question has been answered many times in the past. In
particular, take attention to the InputParameters property. You can search
with Google:

http://groups.google.ca/group/micro...r/search?q=InputParameters&start=0&scoring=d&
This is my stored procedure code. However without the syntax [Form].. it
is
Click to expand...
[quoted text clipped - 16 lines]
=
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...
Click to expand...
 
Excuse me. I mispelled your name. I apologize.
-Misty

Mitchell_Collen said:
Thanks Sylvan for helping me out twice today! But, yea, I am using the adp
project with no books and that even though odd did work. I don't know why, it
just wouldn't work with record sets.

-Misty
I'm very surprised that you can use the syntax
[Forms]![rptEmployee]![StartDate] even with a report.
Click to expand...
[quoted text clipped - 11 lines]
=
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...
Click to expand...
Click to expand...
 
I will try this. Thanks Gabriele.
-Misty

Gabriele said:
This is my stored procedure code. However without the syntax [Form].. it is
only useful with a report. I want to open up a record set from a form using
Click to expand...
[quoted text clipped - 9 lines]
AND @endDatetime = [Forms]![rptEmployee]![EndDate]) and (UserID = @empKey =
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...

SQL Server dowsn't know what [Forms].... is.
In the SP code you have tu use T-SQL parameters, as following:

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,
@empKey varchar(20))
AS
SELECT *
FROM dbo.phmPYXHx
WHERE
(TxDatetime BETWEEN @startDatetime AND @endDatetime)
AND
(UserID = @empKey)

Then, in the InputParameters form property you will enter the values as
following (all in the same row):

@startDatetime datetime = [Forms]![rptEmployee]![StartDate],
@endDatetime datetime = [Forms]![rptEmployee]![EndDate],
@empKey = [Forms]![rptEmployee]![EmployeeKey]

p.s.: why do you name a form "rpt..." instead of "frm..."?
Click to expand...
 
Nevermind, you are right. it doesn't work in sp. ...One day,,maybe I will get
this confusion straight. (:->)
Mitchell_Collen said:
Thanks Sylvan for helping me out twice today! But, yea, I am using the adp
project with no books and that even though odd did work. I don't know why, it
Click to expand...
[quoted text clipped - 7 lines]
=
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...
Click to expand...
Click to expand...
 
Gabriele,
About how I named it a rpt, well, I am working on trying to get the syntax
correct before I try to create and run the real report/record set. So its for
test right now, but it will be bigger, I just don't want to waste time on
getting everything together but I can't get the syntax right. (no books for
reference around here, I don't even have access to northwind.adp for examples.
)
-Misty

Gabriele said:
This is my stored procedure code. However without the syntax [Form].. it is
only useful with a report. I want to open up a record set from a form using
Click to expand...
[quoted text clipped - 9 lines]
AND @endDatetime = [Forms]![rptEmployee]![EndDate]) and (UserID = @empKey =
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...

SQL Server dowsn't know what [Forms].... is.
In the SP code you have tu use T-SQL parameters, as following:

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,
@empKey varchar(20))
AS
SELECT *
FROM dbo.phmPYXHx
WHERE
(TxDatetime BETWEEN @startDatetime AND @endDatetime)
AND
(UserID = @empKey)

Then, in the InputParameters form property you will enter the values as
following (all in the same row):

@startDatetime datetime = [Forms]![rptEmployee]![StartDate],
@endDatetime datetime = [Forms]![rptEmployee]![EndDate],
@empKey = [Forms]![rptEmployee]![EmployeeKey]

p.s.: why do you name a form "rpt..." instead of "frm..."?
Click to expand...
 
On Tue, 30 Oct 2007 19:46:44 GMT, "Mitchell_Collen via

Sorry, but that is no excuse.
You create a new form and save it. Access asks for a name. You enter
"rpt"whatever. There never is a better time to be accurate than the
first opportunity.

-Tom.


Gabriele,
About how I named it a rpt, well, I am working on trying to get the syntax
correct before I try to create and run the real report/record set. So its for
test right now, but it will be bigger, I just don't want to waste time on
getting everything together but I can't get the syntax right. (no books for
reference around here, I don't even have access to northwind.adp for examples.
)
-Misty
Click to expand...
<clip>
 
4454545465
Gabriele Bertolucci said:
This is my stored procedure code. However without the syntax [Form].. it is
only useful with a report. I want to open up a record set from a form using
input parameters, I am not sure why in adp I can not use the = [Form]!
[rptEmployee]![StartDate] ...

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,@empKey varchar(20))
AS SELECT *
FROM dbo.phmPYXHx
WHERE (TxDatetime BETWEEN @startDatetime = [Forms]![rptEmployee]![StartDate]
AND @endDatetime = [Forms]![rptEmployee]![EndDate]) and (UserID = @empKey =
[Forms]![rptEmployee]![EmployeeKey])
Click to expand...

SQL Server dowsn't know what [Forms].... is.
In the SP code you have tu use T-SQL parameters, as following:

ALTER PROCEDURE Pharmacy.rptEmployee
(@startDatetime datetime,
@endDatetime datetime,
@empKey varchar(20))
AS
SELECT *
FROM dbo.phmPYXHx
WHERE
(TxDatetime BETWEEN @startDatetime AND @endDatetime)
AND
(UserID = @empKey)

Then, in the InputParameters form property you will enter the values as
following (all in the same row):

@startDatetime datetime = [Forms]![rptEmployee]![StartDate],
@endDatetime datetime = [Forms]![rptEmployee]![EndDate],
@empKey = [Forms]![rptEmployee]![EmployeeKey]

p.s.: why do you name a form "rpt..." instead of "frm..."?
Click to expand...
 
Back
Top