In addition to orange's comments, providing accurate, complete names (table names, field names, form names) makes it easier to provide good answers.
how to define different values in a new year, eg checkbox completed always "no" in a new year. I assume that feilds which need to be empty in a new year simply do not need to be included in the selection.
This is correct.
how to add further fields to be copied? eg. GDS where GDS is yes/no or employee, where employee is a number. I have tried this with adding select records, but that didn't work. I have added those fields in the table
Are "GDS" or "employee" main table fields?
What are the actual field names?
See below
tab deadlines:
how can the deadline date of the copied record be increased by one year in the new year. Rem. the year of the deadline usually is also the following year itr.newyear.
See below
I thought I had added enough comments that you could figure out what to do. You really NEED to understand what the code does..
(this is) Below:
Code:
Option Compare Database
Option Explicit
Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim db As DAO.Database
Dim s As DAO.Recordset 'source record set (year to duplicate)
Dim r As DAO.Recordset 'recordset to add new records to
Dim r2 As DAO.Recordset ' recordset to check number child records
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim iNewYear As Integer
Dim RC As Integer ' record count
Dim TaskID As Long ' for new record
Dim NewITR_PK As Long
Set db = CurrentDb
'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If
'====== Main table records ===============
'select main table records to duplicate
' this SELECT query is where you would ADD fields you want to be duplicated from the itr table
strSql = "SELECT itr.itrID_PK, TaskID_FK, itr.TaskYear, itr.EMPLOYEE" ' <<<<---- Change EMPLOYEE to your field names
strSql = strSql & " FROM itr"
strSql = strSql & " WHERE itr.TaskYear = " & Me.cboYear & ";"
' Debug.Print strSql
Set s = db.OpenRecordset(strSql)
'recordset to add records to
Set r = db.OpenRecordset("itr")
'Make sure there is a record to duplicate.
If Not s.BOF And Not s.EOF Then
s.MoveLast
' Debug.Print s.RecordCount
s.MoveFirst
iNewYear = Me.cboYear + 1
'Duplicate the main record: add to form's clone.
Do While Not s.EOF
TaskID = s!TaskID_FK
' OldITR_PK = s!itrID_PK
With r
.AddNew
!TaskID_FK = TaskID
!TaskYear = iNewYear
!EMPLOYEE = s!EMPLOYEE ' <<<<---- Change EMPLOYEE to your field names
'this is to link to the child records
'Save the primary key value, to use as the foreign key for the related records.
NewITR_PK = CLng(r!itrID_PK)
.Update
'================= CHILD table records =====================
'*** Now Duplicate the related records: append query.
'check to see if there are child records to duplicate
strSql = "SELECT deadlines.itrID_FK FROM deadlines WHERE itrID_FK = " & s("itrID_PK") & ";"
' Debug.Print strSql
Set r2 = db.OpenRecordset(strSql)
If Not r2.BOF And Not r2.EOF Then
r2.MoveLast
RC = r2.RecordCount
End If
r2.Close
If RC > 0 Then
'child records found.... duplicate them
strSql = "INSERT INTO deadlines ( itrID_FK, canton, deadline, dteCompleted )"
strSql = strSql & "SELECT " & NewITR_PK & " As NewID, deadlines.canton, " & DateAdd("y", 1, deadlines.deadline) & ", deadlines.dteCompleted"
strSql = strSql & " FROM deadlines"
strSql = strSql & " WHERE deadlines.itrID_FK = " & s("itrID_PK") & ";"
' Debug.Print strSql
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
'================= end CHILD table records =====================
End With
s.MoveNext
Loop
'====== end Main table records ===============
End If
Exit_Handler:
'clean up
r.Close
s.Close
Set r = Nothing
Set r2 = Nothing
Set s = Nothing
Set db = Nothing
MsgBox "Done - Duplicated records of " & Me.cboYear & " for new year of " & iNewYear & "."
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
If you don't know what the DateAdd() function does, check HELP....