Interactive input into SQL view in adp

  • Thread starter Thread starter BCW
  • Start date Start date
B

BCW

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?
 
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.
 
Norman said:
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.
Click to expand...
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.
 
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.
 
Back
Top