Hello,
I have spent several hours trying to figure this out but have not succeeded. So, I need help please!
I have a SQL stored procedure where it deletes a row from a table based on a Username and then inserts new rows for that Username based on the Username and Department that is entered:
DELETE FROM [dbo].[UserPermissions] where Userid = @user
INSERT INTO [dbo].[UserPermissions]
SELECT @user, Divisions.division FROM (Select division from tbl_Divisions where Department = @Dept) as Divisions;
I also created an Access form with two drop-down boxes for Userid and Department and captured the user selection in a couple of global variables. Then, put a command button on the form and tried several different things in the Click_Event to pass the parameters (through the global variables) to the pass-through query.
I also tried bypassing the stored procedure altogether and instead doing the Delete and Insert statements right in the Click_Event of the form. But, nothing worked. I could get the Delete statement working fine but the INSERT statement is throwing errors.
So, after a few hours of trial and error, I decided to lower my expectations and ditch the form and just try to make the pass-through query prompt me for the parameters. When I run the stored procedure in SQL, I get prompted for the parameters. So, I just want Access to prompt me as well but can't figure out how to do so. Here is the code in my Access pass-through:
Execute [OMBudget].[dbo].Update_UserPermissions UserName, Department
My SQL stored procedure is called, "Updata_UserPermissions".
Any help is much appreciated.
Thanks,
Setareh
I have spent several hours trying to figure this out but have not succeeded. So, I need help please!
I have a SQL stored procedure where it deletes a row from a table based on a Username and then inserts new rows for that Username based on the Username and Department that is entered:
DELETE FROM [dbo].[UserPermissions] where Userid = @user
INSERT INTO [dbo].[UserPermissions]
SELECT @user, Divisions.division FROM (Select division from tbl_Divisions where Department = @Dept) as Divisions;
I also created an Access form with two drop-down boxes for Userid and Department and captured the user selection in a couple of global variables. Then, put a command button on the form and tried several different things in the Click_Event to pass the parameters (through the global variables) to the pass-through query.
I also tried bypassing the stored procedure altogether and instead doing the Delete and Insert statements right in the Click_Event of the form. But, nothing worked. I could get the Delete statement working fine but the INSERT statement is throwing errors.
So, after a few hours of trial and error, I decided to lower my expectations and ditch the form and just try to make the pass-through query prompt me for the parameters. When I run the stored procedure in SQL, I get prompted for the parameters. So, I just want Access to prompt me as well but can't figure out how to do so. Here is the code in my Access pass-through:
Execute [OMBudget].[dbo].Update_UserPermissions UserName, Department
My SQL stored procedure is called, "Updata_UserPermissions".
Any help is much appreciated.
Thanks,
Setareh