Dim strSql As String
strSql = "INSERT INTO tblSession ( fldPrivateLesson, fldTermID, fldLessonName, fldStaffID, fldClassID, fldSplitID, fldRoomID, fldStudentID, fldSubjectID, fldNote, fldAuxiliaryStaffID, fldAuxiliaryStaff2ID ) " & _
"SELECT fldPrivateLesson, fldTermID, fldLessonName, fldStaffID, fldClassID, fldSplitID, fldRoomID, fldStudentID, fldSubjectID, fldNote, fldAuxiliaryStaffID, fldAuxiliaryStaff2ID " & _
"FROM tblSession " & _
"WHERE fldSessionID = " & SessionID
CurrentDb.Execute strSql
End Sub
Public Function GetlatestSession() As Long
GetlatestSession = DMax("fldSessionID", "tblSession")
End Function
Public Sub CopySessionWeekDays(NewSessionID, OldSessionID)
Dim strSql As String
strSql = "INSERT INTO jtblSessionWeekday ( fldSessionID, fldWeekdayID ) " & _
"SELECT " & NewSessionID & ", jtblSessionWeekday.fldWeekdayID " & _
"FROM jtblSessionWeekday " & _
"WHERE jtblSessionWeekday.fldSessionID = " & OldSessionID
Debug.Print strSql
CurrentDb.Execute strSql
End Sub
Public Sub CopySessionTimes(NewSessionID As Long, OldSessionID As Long)
Dim strSql As String
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim NewDayID As Long
Dim OldSessionDayTime As Long
Dim NewSessionDayTime As Long
'These are the original days and times
strSql = "SELECT jtblSessionWeekday.fldWeekdayID, jtblSessionDayTimes.fldStart, jtblSessionDayTimes.fldEnd, jtblSessionWeekday.fldSessionID, jtblSessionDayTimes.fldSessionDayID, jtblSessionDayTimes.fldSessionDayTimesID " & _
"FROM jtblSessionWeekday INNER JOIN jtblSessionDayTimes ON jtblSessionWeekday.fldSessionDayID = jtblSessionDayTimes.fldSessionDayID " & _
"WHERE jtblSessionWeekday.fldSessionID = " & OldSessionID
Set rsOld = CurrentDb.OpenRecordset(strSql)
strSql = "select * from jtblSessionDayTimes where true = false"
Set rsNew = CurrentDb.OpenRecordset(strSql)
Do While Not rsOld.EOF
Debug.Print rsOld!fldWeekdayID
'Need to get the new day id for the same day of the week
NewDayID = GetNewDayID(NewSessionID, rsOld!fldWeekdayID)
rsNew.AddNew
rsNew!fldSessionDayID = NewDayID
rsNew!fldStart = rsOld!fldStart
rsNew!fldEnd = rsOld!fldEnd
rsNew.Update
'Get the old and new sessiondaytimeid
OldSessionDayTime = rsOld!fldSessionDayTimesID
NewSessionDayTime = DMax("fldSessionDayTimesID", "jtblSessionDayTimes")
CopyTimeStudents OldSessionDayTime, NewSessionDayTime
rsOld.MoveNext
Loop
End Sub
Public Sub CopyTimeStudents(OldSessionDayTime As Long, NewSessionDayTime As Long)
Dim strSql As String
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
strSql = "SELECT jtblSessionWkdayStudent.fldSessionDayTimesID, jtblSessionWkdayStudent.fldStudentID " & _
"FROM jtblSessionWkdayStudent " & _
"WHERE jtblSessionWkdayStudent.fldSessionDayTimesID = " & OldSessionDayTime
Debug.Print strSql
Set rsOld = CurrentDb.OpenRecordset(strSql)
Do While Not rsOld.EOF
strSql = "INSERT INTO jtblSessionWkdayStudent ( fldSessionDayTimesID, fldStudentID ) " & _
"values(" & NewSessionDayTime & ", " & rsOld!fldStudentID & ")"
Debug.Print strSql
CurrentDb.Execute strSql
rsOld.MoveNext
Loop
End Sub
Public Function GetNewDayID(NewSessionID As Long, WeekdayID As Long)
GetNewDayID = DLookup("fldSessionDayID", "jtblSessionWeekday", "fldSessionID = " & NewSessionID & " AND fldWeekdayID = " & WeekdayID)
End Function
Public Sub CopySessionAndChildren(OldSessionID As Long)
Dim NewSessionID As Long
CopySession OldSessionID
NewSessionID = GetlatestSession
CopySessionWeekDays NewSessionID, OldSessionID
CopySessionTimes NewSessionID, OldSessionID
End Sub