October 2, 2003 at 11:33 am
Hello,
I have a Stored Procedure that works the way I intended it to work, but is giving me problems when I try to implement it as the Record Source for an Access 2000 Project (ADP) subform. Basically, I am not able to assign fields to the 'Link Master' and 'Link Child' properties to the subform object, so the form and subform cannot 'link', and I think it may be becasue the the nature of the Record Source for this subform.
I think I need either a View that will accomplish the same thing that the SP does, or get the results of what I am testing for in the SP outside of the subform, or express the Procedure some other way so that it will work as a Record Source for the subform. The Procedure was designed to help with a security feature of the client application, and to avoid having individual log in accounts to SQL Server, so maybe there might be something I could also do within the current SQL Server Roles I have that would eliminate the need for this procedure all together.
Anyway, the following is the Procedure that is causing problems for the subform:
*********************************************
ALTER Procedure WOApproveAccess_sp
AS
IF
(SELECT COUNT(AD.WODistrict)FROM tblMMAdmins AD
WHERE AD.WODistrict <> 0
AND AD.SystemUserName = (SYSTEM_USER))>0
BEGIN
SELECT WO.WoSiteID, WO.WOapproved, WO.WorkOrderID
FROM tblWOWorkOrder WO
END
ELSE
SELECT
WO.WOSiteID, WO.WOApproved, WO.WorkOrderID
FROM tblMMAdmins AD INNER JOIN tblWOWorkOrder WO
ON AD.Schoolnum=WO.WOSiteID
WHERE
AD.WOSite <> 0
AND
AD.SystemUserName = (SYSTEM_USER)
*********************************************
First, the 'WO.WorkOrderID' field is supposed the be the 'Link Child Field'
for the Subform object.
The procedure is designed to return certain records if the value of the logged in SYSTEM_USER causes the count of AD.WODistrict(bit) to be greater than zero. If the first test is not greater than zero, and the logged in 'SYSTEM_USER' matches AD.SystemUserName, and WO.WOSiteID(bit)is not zero, then certain other records are returned where two numbers (AD.Schoolnum=WO.SiteID) match. If neither condition is met, no records are returned.
The result of either test will make 'WO.WorkOrderID' Null or Not Null, with a Not Null result of the second test being specific to a WO.WOSiteID number. A Macro in the application sets the 'Enabled' value of a checkbox on an input form to True or False for every instance of the main form in the first test, and True or False for specific instances related to WO.WOSiteID in the second test based on whether or not WO.WorkOrderID is Null.
This might be more than you wanted to know. The bottom line is, I need to see if I can either express what I am trying to accomplish in the SP as a View, or if there are some other ways I could express the SP in an attempt to make it work with the subform.
Please let me know if you have any ideas. If this is not clear, please also let me know that.
Thanks for your help!
CSDunn
October 2, 2003 at 12:42 pm
Call the SP with a Pass-Through query in access. If it still gives you headaches, then call the Pass-Through query from another query (i.e.. SELECT * FROM <pass-through query>) and use this as the source for the subform.
Dave
October 2, 2003 at 12:59 pm
quote:
Call the SP with a Pass-Through query in access.
I'm not sure if Pass-Through queries work in an Access Project (this is not an Access Database MDB file), but I'll check into this.
Thanks for your help!
CSDunn
October 3, 2003 at 5:30 am
The problem may be with the select count(...) statement - you may actually be getting two recordsets back when the query is run outside Query Analyzer. Try the following:
ALTER Procedure WOApproveAccess_sp
AS
SET NOCOUNT ON
DECLARE @DistrictCount int
select @DistrictCount = COUNT(AD.WODistrict)
FROM tblMMAdmins AD
WHERE AD.WODistrict <> 0
AND AD.SystemUserName = (SYSTEM_USER)
IF @DistrictCount <> 0
...
October 3, 2003 at 9:14 am
Maybe the NOCOUNT like previous post needs to be ON
It wasn't in your example and it has tripped me up many times in ADP.
Wierd error messages that aren't really the error.
Setting NOCOUNT ON helped me.
October 3, 2003 at 1:26 pm
Thanks for these ideas. I had thought that not having NOCOUNT on might be a problem, and I will try this.
CSDunn
October 4, 2003 at 10:27 am
Ok. This sounds like you are using databoumd controls. Not a good thing to do to your poor data. Pull the result back as a recordset and work with the recordset to populate the form. You will find it a bit more work, but the results will be better.
WABALUBADUBDUB
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply