Global Variables for Query Parameters

K

Kobi

I am missing something very basic that I need so I apologize for my inability
to grasp the obvious (because I'm sure it is) in advance.

I had an .mdb that I have converted to .adp. There are many forms that
provide the criteria for either the underlying queries or recordsets such
as...

PARAMETERS [Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegDate] DateTime, [Forms]![CallList_Frm].[BegDate]
DateTime, [Forms]![CallList_Frm].[BegSkill] Text ( 255 ),
[Forms]![CallList_Frm].[EndSkill] Text ( 255 );
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between [Forms]![CallList_Frm]![BegDate] And
[Forms]![CallList_Frm]![EndDate]) AND ((HRRM.State) Like
(([HRRM].[State])=[Forms]![CallList_Frm]![BegState] Or
[Forms]![CallList_Frm]![BegState] Is Null)));

Now, I understand that in .adp there are no queries and you should use
stored procedures and try as I might, I am not grasping the concept of how to
get the values from the combo boxes and use them as criteria for the sp
parameters. I have read a lot of documentation discussing declaring global
variables first from the form information and passing that to the sp. I
think what I'm missing is specifically how to declare those global variables
to use elsewhere. Somehow, I think that I've gotten to an advanced stage in
what I'm doing with Access, so the documentation explaining this in reference
to what I'm doing simply skips it because it assumes that you already know
this basic concept. Either that, or I am SO familiar with .mdb concepts that
I cannot see beyond it to accomplish this new task. Anyone want to take a
stab at guiding the blind? .............please...............
 
M

Malcolm Cook

One way is to set the form's InputParameters Property. Open the form's properties, select the 'Data' tab, click into the 'Input
Parameters' field and hit F1. You get help that begins: "You can use the InputParameters property to specify or determine the input
parameters that are passed to a SQL statement in the RecordSource property of a form or report or a stored procedure when used as
the record source within a Microsoft Access project (.adp). Read/write String."

But, this is NOT the whole story, but you should know among other things the following (undocumented?) fact, which _can_ greatly
simplify your efforts, namely, if you have a stored procedure as the record source of an ADP form, and its parameter names are
literraly identical to names of controls on the current form then ADP will contrive to pass the value of the control as the value of
the param. Magic.

Oh, and forget about setting stored procedure parameters from 'global variables'. If you find you need to do this, then instead use
a global hidden form and use its form variables as your global parameters.

Good luck,

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Kobi said:
I am missing something very basic that I need so I apologize for my inability
to grasp the obvious (because I'm sure it is) in advance.

I had an .mdb that I have converted to .adp. There are many forms that
provide the criteria for either the underlying queries or recordsets such
as...

PARAMETERS [Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegDate] DateTime, [Forms]![CallList_Frm].[BegDate]
DateTime, [Forms]![CallList_Frm].[BegSkill] Text ( 255 ),
[Forms]![CallList_Frm].[EndSkill] Text ( 255 );
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between [Forms]![CallList_Frm]![BegDate] And
[Forms]![CallList_Frm]![EndDate]) AND ((HRRM.State) Like
(([HRRM].[State])=[Forms]![CallList_Frm]![BegState] Or
[Forms]![CallList_Frm]![BegState] Is Null)));

Now, I understand that in .adp there are no queries and you should use
stored procedures and try as I might, I am not grasping the concept of how to
get the values from the combo boxes and use them as criteria for the sp
parameters. I have read a lot of documentation discussing declaring global
variables first from the form information and passing that to the sp. I
think what I'm missing is specifically how to declare those global variables
to use elsewhere. Somehow, I think that I've gotten to an advanced stage in
what I'm doing with Access, so the documentation explaining this in reference
to what I'm doing simply skips it because it assumes that you already know
this basic concept. Either that, or I am SO familiar with .mdb concepts that
I cannot see beyond it to accomplish this new task. Anyone want to take a
stab at guiding the blind? .............please...............
Click to expand...
 
S

Sylvain Lafontaine

Nothing is obvious with ADP. SQL-Server doesn't know anything about the
local parameters in an ADP project, so these must be explicited transferred
to the SQL-Server. There are three ways of doing this: first, you can build
the record source to a string that will directly make the select statement
with the right values, knowing that you must use the single quote ' as the
string and date delimiters:

Me.RecordSource = "SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM WHERE (((HRRM.LastContactDate) Between '" &
[Forms]![CallList_Frm]![BegDate] & "' And
'" & [Forms]![CallList_Frm]![EndDate]) & "' " ...

I cannot finish the translation because your LIKE clause doesn't mean
anything to me the way that you have written it.

Second, you can write a stored procedure and again set the record source to
make an EXEC call to this SP with the right values:

Me.RecordSource = "EXEC MyStoredProcedure '" &
[Forms]![CallList_Frm]![BegDate] & "', '" & &
[Forms]![CallList_Frm]![EndDate]) & "' " ....

Or you can set the RecordSource to the name of the SP and use the
InputParameters properties to send the values:

Me.RecordSource = "dbo.MyStoredProcedure"

or also:

Me.RecordSourceQualifier = "dbo"
Me.RecordSource = "MyStoredProcedure"

Me.InputParameters = "@BegDate DateTime = [Forms]![CallList_Frm]![EndDate],
@BegDate DateTime = [Forms]![CallList_Frm]![EndDate], @BegState nvarchar
(50) = [Forms]![CallList_Frm].[BegState], ...

and for the Stored Procedure:

CREATE PROCEDURE dbo.MyStoredProcedure
(
@BegDate DateTime,
@EndDate DateTime,
@BegState nvarchar (50),
@EndState nvarchar (50),
@BegSkill nvarchar (255),
@EndSkill nvarchar (255),
)
AS
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between @BegDate And @EndDate) AND
((HRRM.State) Like
(([HRRM].[State]) = @BegState Or @BegState Is Null)))

GO

In your piece of code, you have repeated two times the parameter @BegDate
and the LIKE clause that you have written doesn't make sense to me. Also,
there is no ; at the end of a sql statement on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Kobi said:
I am missing something very basic that I need so I apologize for my
inability
to grasp the obvious (because I'm sure it is) in advance.

I had an .mdb that I have converted to .adp. There are many forms that
provide the criteria for either the underlying queries or recordsets such
as...

PARAMETERS [Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegDate] DateTime,
[Forms]![CallList_Frm].[BegDate]
DateTime, [Forms]![CallList_Frm].[BegSkill] Text ( 255 ),
[Forms]![CallList_Frm].[EndSkill] Text ( 255 );
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between [Forms]![CallList_Frm]![BegDate]
And
[Forms]![CallList_Frm]![EndDate]) AND ((HRRM.State) Like
(([HRRM].[State])=[Forms]![CallList_Frm]![BegState] Or
[Forms]![CallList_Frm]![BegState] Is Null)));

Now, I understand that in .adp there are no queries and you should use
stored procedures and try as I might, I am not grasping the concept of how
to
get the values from the combo boxes and use them as criteria for the sp
parameters. I have read a lot of documentation discussing declaring
global
variables first from the form information and passing that to the sp. I
think what I'm missing is specifically how to declare those global
variables
to use elsewhere. Somehow, I think that I've gotten to an advanced stage
in
what I'm doing with Access, so the documentation explaining this in
reference
to what I'm doing simply skips it because it assumes that you already know
this basic concept. Either that, or I am SO familiar with .mdb concepts
that
I cannot see beyond it to accomplish this new task. Anyone want to take a
stab at guiding the blind? .............please...............
Click to expand...
 
K

Kobi

This is really great information and will help in many other places.
Unfortunately, I used a bad example because I need to pass these parameters
to a sp that is performing an update, so I can't really attach it to the
form's record source can I? This is the beginning part of the sp that I'm
trying to work with. I need to pass HRCo and HRRef from a form. The
beginning part of the sp is below. All the update statements use these
declarations, so if I can just figure out how to assign the form values to
the variables here, I'd be in business....

(@hrco bCompany, @prco bCompany, @hrref bHRRef, @employee bEmployee, @msg
varchar(80) = '' output)

as
set nocount on

declare @rcode int, @updatenameyn bYN, @updateaddressyn bYN,
@updatehiredateyn bYN,
@updateactiveyn bYN, @updateprgroupyn bYN, @updatetimecardyn bYN,
@updatew4yn bYN, @err int,
@updateoccupyn bYN, @updatessnyn bYN

select @rcode = 0, @err = 0

--Get the update flags from HRCO

select @updatenameyn = UpdateNameYN, @updateaddressyn = UpdateAddressYN,
@updatehiredateyn = UpdateHireDateYN, @updateactiveyn = UpdateActiveYN,
@updateprgroupyn = UpdatePRGroupYN, @updatetimecardyn = UpdateTimecardYN,
@updatew4yn = UpdateW4YN, @updateoccupyn = UpdateOccupCatYN, @updatessnyn
= UpdateSSNYN
from HRCO with (nolock)
where HRCo = @hrco



Sylvain Lafontaine said:
Nothing is obvious with ADP. SQL-Server doesn't know anything about the
local parameters in an ADP project, so these must be explicited transferred
to the SQL-Server. There are three ways of doing this: first, you can build
the record source to a string that will directly make the select statement
with the right values, knowing that you must use the single quote ' as the
string and date delimiters:

Me.RecordSource = "SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM WHERE (((HRRM.LastContactDate) Between '" &
[Forms]![CallList_Frm]![BegDate] & "' And
'" & [Forms]![CallList_Frm]![EndDate]) & "' " ...

I cannot finish the translation because your LIKE clause doesn't mean
anything to me the way that you have written it.

Second, you can write a stored procedure and again set the record source to
make an EXEC call to this SP with the right values:

Me.RecordSource = "EXEC MyStoredProcedure '" &
[Forms]![CallList_Frm]![BegDate] & "', '" & &
[Forms]![CallList_Frm]![EndDate]) & "' " ....

Or you can set the RecordSource to the name of the SP and use the
InputParameters properties to send the values:

Me.RecordSource = "dbo.MyStoredProcedure"

or also:

Me.RecordSourceQualifier = "dbo"
Me.RecordSource = "MyStoredProcedure"

Me.InputParameters = "@BegDate DateTime = [Forms]![CallList_Frm]![EndDate],
@BegDate DateTime = [Forms]![CallList_Frm]![EndDate], @BegState nvarchar
(50) = [Forms]![CallList_Frm].[BegState], ...

and for the Stored Procedure:

CREATE PROCEDURE dbo.MyStoredProcedure
(
@BegDate DateTime,
@EndDate DateTime,
@BegState nvarchar (50),
@EndState nvarchar (50),
@BegSkill nvarchar (255),
@EndSkill nvarchar (255),
)
AS
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between @BegDate And @EndDate) AND
((HRRM.State) Like
(([HRRM].[State]) = @BegState Or @BegState Is Null)))

GO

In your piece of code, you have repeated two times the parameter @BegDate
and the LIKE clause that you have written doesn't make sense to me. Also,
there is no ; at the end of a sql statement on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Kobi said:
I am missing something very basic that I need so I apologize for my
inability
to grasp the obvious (because I'm sure it is) in advance.

I had an .mdb that I have converted to .adp. There are many forms that
provide the criteria for either the underlying queries or recordsets such
as...

PARAMETERS [Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegDate] DateTime,
[Forms]![CallList_Frm].[BegDate]
DateTime, [Forms]![CallList_Frm].[BegSkill] Text ( 255 ),
[Forms]![CallList_Frm].[EndSkill] Text ( 255 );
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between [Forms]![CallList_Frm]![BegDate]
And
[Forms]![CallList_Frm]![EndDate]) AND ((HRRM.State) Like
(([HRRM].[State])=[Forms]![CallList_Frm]![BegState] Or
[Forms]![CallList_Frm]![BegState] Is Null)));

Now, I understand that in .adp there are no queries and you should use
stored procedures and try as I might, I am not grasping the concept of how
to
get the values from the combo boxes and use them as criteria for the sp
parameters. I have read a lot of documentation discussing declaring
global
variables first from the form information and passing that to the sp. I
think what I'm missing is specifically how to declare those global
variables
to use elsewhere. Somehow, I think that I've gotten to an advanced stage
in
what I'm doing with Access, so the documentation explaining this in
reference
to what I'm doing simply skips it because it assumes that you already know
this basic concept. Either that, or I am SO familiar with .mdb concepts
that
I cannot see beyond it to accomplish this new task. Anyone want to take a
stab at guiding the blind? .............please...............
Click to expand...
Click to expand...
 
A

aaron.kempf

i've built adp for 6 years; and i've never used input parameters once;
i just do it by hand

for executing an update

Dim strSql as string
strSql = "spMySproc " & MyGlobalVariable1 & "," & MyGlobalVariable2 &
"," & MyGlobalVariable3
Docmd.RunSql strSql

if you pass varchar; etc then you've got to wrap the strings in single
quotes

hope that helps; i LOVE adp

-Aaron
MSN Instant Messenger (e-mail address removed) if you need more help; I
would love to help a little bit; i've got a couple of days off..



This is really great information and will help in many other places.
Unfortunately, I used a bad example because I need to pass these parameters
to a sp that is performing an update, so I can't really attach it to the
form's record source can I? This is the beginning part of the sp that I'm
trying to work with. I need to pass HRCo and HRRef from a form. The
beginning part of the sp is below. All the update statements use these
declarations, so if I can just figure out how to assign the form values to
the variables here, I'd be in business....

(@hrco bCompany, @prco bCompany, @hrref bHRRef, @employee bEmployee, @msg
varchar(80) = '' output)

as
set nocount on

declare @rcode int, @updatenameyn bYN, @updateaddressyn bYN,
@updatehiredateyn bYN,
@updateactiveyn bYN, @updateprgroupyn bYN, @updatetimecardyn bYN,
@updatew4yn bYN, @err int,
@updateoccupyn bYN, @updatessnyn bYN

select @rcode = 0, @err = 0

--Get the update flags from HRCO

select @updatenameyn = UpdateNameYN, @updateaddressyn = UpdateAddressYN,
@updatehiredateyn = UpdateHireDateYN, @updateactiveyn = UpdateActiveYN,
@updateprgroupyn = UpdatePRGroupYN, @updatetimecardyn = UpdateTimecardYN,
@updatew4yn = UpdateW4YN, @updateoccupyn = UpdateOccupCatYN, @updatessnyn
= UpdateSSNYN
from HRCO with (nolock)
where HRCo = @hrco



Sylvain Lafontaine said:
Nothing is obvious with ADP. SQL-Server doesn't know anything about the
local parameters in an ADP project, so these must be explicited transferred
to the SQL-Server. There are three ways of doing this: first, you can build
the record source to a string that will directly make the select statement
with the right values, knowing that you must use the single quote ' as the
string and date delimiters:

Me.RecordSource = "SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM WHERE (((HRRM.LastContactDate) Between '" &
[Forms]![CallList_Frm]![BegDate] & "' And
'" & [Forms]![CallList_Frm]![EndDate]) & "' " ...

I cannot finish the translation because your LIKE clause doesn't mean
anything to me the way that you have written it.

Second, you can write a stored procedure and again set the record source to
make an EXEC call to this SP with the right values:

Me.RecordSource = "EXEC MyStoredProcedure '" &
[Forms]![CallList_Frm]![BegDate] & "', '" & &
[Forms]![CallList_Frm]![EndDate]) & "' " ....

Or you can set the RecordSource to the name of the SP and use the
InputParameters properties to send the values:

Me.RecordSource = "dbo.MyStoredProcedure"

or also:

Me.RecordSourceQualifier = "dbo"
Me.RecordSource = "MyStoredProcedure"

Me.InputParameters = "@BegDate DateTime = [Forms]![CallList_Frm]![EndDate],
@BegDate DateTime = [Forms]![CallList_Frm]![EndDate], @BegState nvarchar
(50) = [Forms]![CallList_Frm].[BegState], ...

and for the Stored Procedure:

CREATE PROCEDURE dbo.MyStoredProcedure
(
@BegDate DateTime,
@EndDate DateTime,
@BegState nvarchar (50),
@EndState nvarchar (50),
@BegSkill nvarchar (255),
@EndSkill nvarchar (255),
)
AS
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between @BegDate And @EndDate) AND
((HRRM.State) Like
(([HRRM].[State]) = @BegState Or @BegState Is Null)))

GO

In your piece of code, you have repeated two times the parameter @BegDate
and the LIKE clause that you have written doesn't make sense to me. Also,
there is no ; at the end of a sql statement on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Kobi said:
I am missing something very basic that I need so I apologize for my
inability
to grasp the obvious (because I'm sure it is) in advance.

I had an .mdb that I have converted to .adp. There are many forms that
provide the criteria for either the underlying queries or recordsets such
as...

PARAMETERS [Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegState] Text ( 255 ),
[Forms]![CallList_Frm].[BegDate] DateTime,
[Forms]![CallList_Frm].[BegDate]
DateTime, [Forms]![CallList_Frm].[BegSkill] Text ( 255 ),
[Forms]![CallList_Frm].[EndSkill] Text ( 255 );
SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE (((HRRM.LastContactDate) Between [Forms]![CallList_Frm]![BegDate]
And
[Forms]![CallList_Frm]![EndDate]) AND ((HRRM.State) Like
(([HRRM].[State])=[Forms]![CallList_Frm]![BegState] Or
[Forms]![CallList_Frm]![BegState] Is Null)));

Now, I understand that in .adp there are no queries and you should use
stored procedures and try as I might, I am not grasping the concept of how
to
get the values from the combo boxes and use them as criteria for the sp
parameters. I have read a lot of documentation discussing declaring
global
variables first from the form information and passing that to the sp. I
think what I'm missing is specifically how to declare those global
variables
to use elsewhere. Somehow, I think that I've gotten to an advanced stage
in
what I'm doing with Access, so the documentation explaining this in
reference
to what I'm doing simply skips it because it assumes that you already know
this basic concept. Either that, or I am SO familiar with .mdb concepts
that
I cannot see beyond it to accomplish this new task. Anyone want to take a
stab at guiding the blind? .............please...............
Click to expand...
Click to expand...
Click to expand...
 
A

aaron.kempf

CREATE PROCEDURE spMySproc
(
@DATESTART SMALLDATETIME,
@DATEEND SMALLDATETIME,
@BegState CHAR(2)
)
AS

SELECT HRRM.HRRef, HRRM.LastContactDate, HRRM.State
FROM HRRM
WHERE
HRRM.LastContactDate
Between @DATESTART And @DATAEND
AND
(HRRM.State = @BegState
Or
@BegState IS NULL)


and then when you fire this sproc; you need to wrap date arguments
inside of single quotes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Defining variables as row numbers 1
Global Template Network and saving auto texts & macros 2
yes/no or both Error 3071 2
Input Parameters 2
Global variables - again - sorry 6
Include empty values in crosstab query 5
Global Variables 4
Returning variables from external form 9

Top