Interactive input into SQL view in adp

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BCW - 15 Apr 2006 23:22 GMT

I am converting an existing change request app from an mdb to an adp. The
mdb has an existing query that has an interactive keyboard entry built into
it. For ReqID field the query criteria has [enter ReqID] which works fine
in the mdb in a popup form. That syntax does not seem to work for the
equivalent SQL view though. How can I accomplish the same thing in the
view? Note, I'm not allowed to use VBA code at all. There is unsupported
application paranoia here (with which I agree). There can be virtually no
code in the application, it is just related tables,and forms with their
associated queries.

The existing mdb is using a table called ChangeRequests and a query called
[Referenced Change Request]. The [Referenced Change Request] query SQL
statement is:

SELECT ChangeRequests.ReqID, ChangeRequests.Org_code,
ChangeRequests.Requester, ChangeRequests.Title, ChangeRequests.Desc,
ChangeRequests.Tasker, ChangeRequests.Location_code,
ChangeRequests.Req_start, ChangeRequests.Req_finish,
ChangeRequests.Act_start, ChangeRequests.Act_finish, ChangeRequests.Status,
ChangeRequests.Workplan_link, ChangeRequests.Backout_plan_link,
ChangeRequests.Backout_steps, ChangeRequests.Category
FROM ChangeRequests
WHERE (((ChangeRequests.ReqID)=[enter ReqID]));

Which will return a specific change record entered from the keyboard.

The query to check for overlapping changes is:

SELECT ChangeRequests.ReqID, ChangeRequests.Requester, ChangeRequests.Title,
ChangeRequests.Req_start, ChangeRequests.Req_finish
FROM [Referenced Change Request], ChangeRequests
WHERE (((ChangeRequests.Req_start)<=[Referenced Change Request.Req_finish]
And (ChangeRequests.Req_start)>=[Referenced Change Request.Req_start])) OR
(((ChangeRequests.Req_finish)<=[Referenced Change Request.Req_finish] And
(ChangeRequests.Req_finish)>=[Referenced Change Request.Req_start])) OR
(((ChangeRequests.Req_start)<=[Referenced Change Request.Req_start]) AND
((ChangeRequests.Req_finish)>=[Referenced Change Request.Req_finish]))
ORDER BY ChangeRequests.Req_start;

How can I get the target change request entered by keyboard into the SQL
view?

Reply to this Message

Norman Yuan - 16 Apr 2006 17:23 GMT

View does not take a parameter as Access query. You can put the SQL SELECT
statement in a Stored Procedure that takes intpu parameters and use that SP
instead of View.

In Access ADP, if you execute a SP that has input parameter, Access ADP will
pop up input box for entering parameter values. However, you should design
your own data entering form to allow uer to input these values in a
controlled way, so you can handle entering validation properly.

>I am converting an existing change request app from an mdb to an adp. The
> mdb has an existing query that has an interactive keyboard entry built
[quoted text clipped - 40 lines]
> How can I get the target change request entered by keyboard into the SQL
> view?

Reply to this Message

BCW - 16 Apr 2006 19:16 GMT

> View does not take a parameter as Access query. You can put the SQL SELECT
> statement in a Stored Procedure that takes intpu parameters and use that
[quoted text clipped - 4 lines]
> design your own data entering form to allow uer to input these values in a
> controlled way, so you can handle entering validation properly.

Thanks for the reply. I did discover that I can create both an inline
function in Access that takes a parameter as an entry. Here is the SQL
code for the function fctGetReferenceChangeRequest:

SELECT ReqID AS expr, dbo.ChangeRequests.*
FROM dbo.ChangeRequests
WHERE (ReqID = @ReqID)

When run directly this returns a proper row from ChangeRequests.

When I try to use combine the function with the ChangeRequests table as
inputs to a new view:

SELECT dbo.ChangeRequests.ReqID, dbo.ChangeRequests.Requester,
dbo.ChangeRequests.Title, dbo.ChangeRequests.Req_start,
dbo.ChangeRequests.Req_finish
FROM dbo.ChangeRequests CROSS JOIN
dbo.fctGetReferencedChange() fctGetReferencedChange
WHERE (dbo.ChangeRequests.Req_start <=
fctGetReferencedChange.Req_finish) AND
(dbo.ChangeRequests.Req_finish >=
fctGetReferencedChange.Req_start)

it does not popup a parameter request, instead I get this error:
ADO error: An insufficient number of arguments were supplied for the
procedure or function dbo.fctGetReferencedChange

It looks like whenever I use the function in a query, it will not ask for
the parameter, but will die when it cannot get the input.

Reply to this Message

BCW - 16 Apr 2006 20:06 GMT

I figured it out! I have to use the function itself as the data source to
the form, not add the function to another view. I just added the table to
the function & it asked for the parameter just fine.

>> View does not take a parameter as Access query. You can put the SQL
>> SELECT statement in a Stored Procedure that takes intpu parameters and
[quoted text clipped - 34 lines]
> It looks like whenever I use the function in a query, it will not ask for
> the parameter, but will die when it cannot get the input.

Reply to this Message

aaron.kempf@gmail.com - 20 Apr 2006 16:39 GMT

functions are slow

use a sproc

Reply to this Message