I have a table: tblSchedule in SQL Server with fields:
Schedule_ID (int PRIMARYKEY Autoincrementing), Schedule_CostType (char 16), Schedule_Section (char 16) , Schedule_No (char 16), Schedule_Order (float 8), Schedule_Desc (char 32), Schedule_Unit (char 16), Schedule_Qty (real 4) ,tender_ID (int FK)
I want to select all records where Tender_ID = a variable tender_ID_Original
and append them in the same table but with Tender_ID = a variable tender_ID_New
(Note: there are no integrity problems as The FK tender_ID_New already exists)
I have written the sp :
ALTER PROCEDURE procNewSchedule
SET NOCOUNT ON
dbo.tblSchedule(Schedule_CostType, Schedule_Section, Schedule_No, Schedule_Order, Schedule_Desc, Schedule_Unit, Schedule_Qty,tender_ID)
SELECT Schedule_CostType,Schedule_No, Schedule_Section, Schedule_Order, Schedule_Desc, Schedule_Unit, Schedule_Qty,@tender_ID_New
WHERE Tender_ID = @tender_ID_Original
SET NOCOUNT OFF
When I run it asks for the variables OK and seems to run OK but I get the message 'The stored procedure executed successfully but did not return records"
What have i done wrong?
also I want to run thus sp in Access using VBA code but I'm not sure how to pass the variables
Private Sub btnAppendSchedule_Click()
Dim tender_ID_Original, tender_ID_New As Integer
Dim vSQL As String
tender_ID_Original = Forms!frmRepeatSchedule.cboTenderOld.Column(0)
tender_ID_New = Forms!frmRepeatSchedule.txtTenderNew
vSQL = " procNewSchedule ??????? "
Thanks in advance