Duplicate a set of records

[Wildster]

Hi,

I have the following table called tblShift: -

*Shift_ID
Employee_ID
Shift_Day
Shift_Start
Shift_End
Shift_Wk_End

What I'm trying to do is have a form with two text boxes and a command button. In text box 1 I want to enter a 'Shift_Wk_End' date and in text box 2 I want to enter a different date. The when I click the command button it looks at the table specified above (tblShift) and I want it to copy each record which has a 'Shift_Wk_End' which matches that entered in text box 1 (OldDate), and I want it to create duplicated of those records but changing the 'Shift_Wk_End to the date entered in text box 2 (NewDate).

Basically the table holds info on peoples shifts and the 'Shift_Wk_End' field identifies the week ending date which the shift is for. What I'm trying to do is to create a way of easily setting up the shifts for the next week in question (with a different week ending date).

I've figured I need something along the lines of the statement below, although it's bringing back a syntax error: -

DoCmd.RunSQL (insert into tblShift select Employee_ID, Shift_Day, Shift_Start, Shift_End, Forms!frmShift_Update_WK_End!NewDate as Shift_Wk_End from tblShift where Shift_Wk_End = Forms!frmShift_Update_WK_End!OldDate;)

Any suggestions would be very much appreciated.

Thanks

 

[DavidBoyleHerts]

I think you need to define the target field list after the target table and then field match that to your select list. I dont think that aliasing
Shift_Wk_End automatically matches that to the field name in the target table.

See if this works then extend

insert into tblShift(Shift_Wk_End) select
Forms!frmShift_Update_WK_End!NewDate as
Shift_Wk_End from tblShift where Shift_Wk_End = Forms!frmShift_Update_WK_End!OldDate;

 

[Wildster]

Hi,

Thanks for the quick reply. It doesnt work when I use the code within DoCmd.RunSQL in VB, however when I put it into a query it works fine.

Two questions: -

1. Is there a way that this code can be run in VB? I thought DoCmd.RunSQL(query) would do the job?

2. I can understand what you did by rearranging the code which I initially posted, but I'm struggling to extend for the other fields I require. I must be making a mistake somewhere, could you provide an example with one more field added please so I can see where I'm going wrong and then I can add the rest of the fields myself?

Thanks very much for your help

 

[DavidBoyleHerts]

insert into tblShift(Employee_ID,Shift_Wk_End) select
Employee_ID,Forms!frmShift_Update_WK_End!NewDate as
Shift_Wk_End from tblShift where Shift_Wk_End = Forms!frmShift_Update_WK_End!OldDate;

 

[DavidBoyleHerts]

You need to wrap the query in quotes like this

DoCmd.RunSQL ("insert into tblShift(Employee_ID,Shift_Wk_End) select
Employee_ID,Forms!frmShift_Update_WK_End!NewDate as
Shift_Wk_End from tblShift where Shift_Wk_End = Forms!frmShift_Update_WK_End!OldDate;")

 

[Wildster]

Get you, I was missing the quotes off - schoolboy error!

Thansk very much for your help wth this, it's very much appreciated.