ADP form with parameterized function not prompting at runtime

  • Thread starter Thread starter Bill Kuhn - MCSE
  • Start date Start date
B

Bill Kuhn - MCSE

As I continue to try to salvage an ADP project in Access 2007 (and the light
at the end of the tunnel has pretty much gone out due to Access 2007 bugs),
my latest probable dead end is..

I have a parameterized function called
"Walmart_by_completion_info_parameterized" defined as..

SELECT DISTINCT
TOP 100 PERCENT dbo.[WM-TRACKING].TrackingNum,
dbo.[WM-Stores].StoreNum, dbo.[WM-Stores].City, dbo.[WM-Stores].State,
dbo.[WM-TRACKING].CMPLTD_DATE,
dbo.[WM-TRACKING].[CMPLTD SIGNATURE], dbo.ServiceProviders.Providers
FROM dbo.[WM-Stores] INNER JOIN
dbo.ServiceProviders INNER JOIN
dbo.[WM-TRACKING] ON dbo.ServiceProviders.ID =
dbo.[WM-TRACKING].SERVICE_BY ON
dbo.[WM-Stores].StoreNum = dbo.[WM-TRACKING].StoreNum
WHERE (dbo.[WM-TRACKING].RecvdCmpltdDate >= @Enter_RecvdCmpltdDate)
ORDER BY dbo.[WM-TRACKING].TrackingNum, dbo.ServiceProviders.Providers

which when used as the recordsource for a form in Access2007 prompts me for
@Enter_RecvdCmpltdDate. Works fine (surprising for Access2007).

Now I make my .accdr version and run on an XP machine with only Access2007
Runtime - Now I get error "Parameters were not supplied for the function
'dbo.Walmart_by_completion_info_parameterized'." In runtime is is not
prompting for the parameter.

Even more fun is that if I run the accdr using Access2007 Runtime on a
machine that has Access 2007 installed the form works fine.

Nice consistant behaviour between runtime, full version, and runtime where
full version is available.

This also happens on a report that uses a similar parameterized function as
the recordsource. On a report the error is a little different - "The record
source 'dbo.qryWalmartCountByState' specified on this form or report does not
exist". Again the recordsource expects a parameter. Again it works fine in
full access 2007. It works fine in accdr using Access 2007 runtime on a
machine that also has Access 2007. It gives me the error on machines that
only have runtime 2007. It does not prompt for the parameter.
 
Maybe you could prompt the user for the parameter value, and then call the
function with the parameter. Or include a control for the parameter on the
form and requery with the parameter on the AfterUpdate event.
 
I wound up doing essentially that last night. I created forms to get the
appropriate parameters (there are actually about 20 different forms in the
project where this is a problem) and have them open the original forms
passing them the parameter in the paramargs object. I found some code that
was very handy for parsing a parameter string that is actually a pipe
delimited set of parameters so I could pass multiple parameters in the
docmd.openform method.

So I have a workaround, but now I'm real curious as to WHY runtime does not
prompt for parameters the way full Access2007 does.

Is this the same behaviour in Access2003 (my access experience is limited to
this project - I'm a .NET and VFP developer)..?
--
Bill Kuhn - MCSE
President
The Kuhn Group, Inc.
http://www.kuhngroup.com


Paul Shapiro said:
Maybe you could prompt the user for the parameter value, and then call the
function with the parameter. Or include a control for the parameter on the
form and requery with the parameter on the AfterUpdate event.

Bill Kuhn - MCSE said:
As I continue to try to salvage an ADP project in Access 2007 (and the
light
at the end of the tunnel has pretty much gone out due to Access 2007
bugs),
my latest probable dead end is..

I have a parameterized function called
"Walmart_by_completion_info_parameterized" defined as..

SELECT DISTINCT
TOP 100 PERCENT dbo.[WM-TRACKING].TrackingNum,
dbo.[WM-Stores].StoreNum, dbo.[WM-Stores].City, dbo.[WM-Stores].State,
dbo.[WM-TRACKING].CMPLTD_DATE,
dbo.[WM-TRACKING].[CMPLTD SIGNATURE], dbo.ServiceProviders.Providers
FROM dbo.[WM-Stores] INNER JOIN
dbo.ServiceProviders INNER JOIN
dbo.[WM-TRACKING] ON dbo.ServiceProviders.ID =
dbo.[WM-TRACKING].SERVICE_BY ON
dbo.[WM-Stores].StoreNum =
dbo.[WM-TRACKING].StoreNum
WHERE (dbo.[WM-TRACKING].RecvdCmpltdDate >= @Enter_RecvdCmpltdDate)
ORDER BY dbo.[WM-TRACKING].TrackingNum, dbo.ServiceProviders.Providers

which when used as the recordsource for a form in Access2007 prompts me
for
@Enter_RecvdCmpltdDate. Works fine (surprising for Access2007).

Now I make my .accdr version and run on an XP machine with only Access2007
Runtime - Now I get error "Parameters were not supplied for the function
'dbo.Walmart_by_completion_info_parameterized'." In runtime is is not
prompting for the parameter.

Even more fun is that if I run the accdr using Access2007 Runtime on a
machine that has Access 2007 installed the form works fine.

Nice consistant behaviour between runtime, full version, and runtime where
full version is available.

This also happens on a report that uses a similar parameterized function
as
the recordsource. On a report the error is a little different - "The
record
source 'dbo.qryWalmartCountByState' specified on this form or report does
not
exist". Again the recordsource expects a parameter. Again it works fine in
full access 2007. It works fine in accdr using Access 2007 runtime on a
machine that also has Access 2007. It gives me the error on machines that
only have runtime 2007. It does not prompt for the parameter.
--
Bill Kuhn - MCSE
President
The Kuhn Group, Inc.
http://www.kuhngroup.com
Click to expand...
Click to expand...
 
Back
Top