Hi everyone
I am trying to insert several data into a table. (simply appending students and teacher attendance) I can do students but I have got problems with teacher attendance
The following code works fine
Private Sub UPDATETEACHER_Click()
Dim strSql As String
Dim strSql_2 As String
strSql = "INSERT INTO tblTeachersAttendance (CoursesTakenByTeachers_ID)" _
& "SELECT CoursesTakenByTeachers_ID FROM tblCoursesTakenByTeachers WHERE tblCoursesTakenByTeachers.Teacher_ID = " & Me.cbo_Teacher_Name & " AND tblCoursesTakenByTeachers.Course_ID = " & Me.cbo_Course_Name & " ;"
strSql_2 = "INSERT INTO tblTeachersAttendance (AttendanceDate, AttendanceDuration, AttendanceNote)" _
& " Values(#" & Me.AttendanceDate & "#, " & Me.AttendanceDuration & ", '" & Me.Attendance_Notes & "') ;"
Debug.Print strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql_2
End Sub
But as you may guess, it creates 2 different rows. I just need to insert those values into single row. I use the following code but error 3346 comes up. I don't what is wrong.
Private Sub UPDATETEACHER_Click()
Dim strSql As String
strSql = "INSERT INTO tblTeachersAttendance ([CoursesTakenByTeachers_ID], AttendanceDate, AttendanceDuration, AttendanceNote)" _
& "SELECT CoursesTakenByTeachers_ID FROM tblCoursesTakenByTeachers WHERE tblCoursesTakenByTeachers.Teacher_ID = " & Me.cbo_Teacher_Name & " AND tblCoursesTakenByTeachers.Course_ID = " & Me.cbo_Course_Name & " " _
& " Values(#" & Me.AttendanceDate & "#, " & Me.AttendanceDuration & ", '" & Me.Attendance_Notes & "') ;"
Debug.Print strSql
DoCmd.RunSQL strSql
End Sub
I really appreciate for your help. I already tried INSERT INTO SELECT and UPDATE second part (I mean AttendanceDate, AttendanceDuration, AttendanceNote) but i couldn't I don't which reference I should use in UPDATE code to put values in the same row.
I am trying to insert several data into a table. (simply appending students and teacher attendance) I can do students but I have got problems with teacher attendance
The following code works fine
Private Sub UPDATETEACHER_Click()
Dim strSql As String
Dim strSql_2 As String
strSql = "INSERT INTO tblTeachersAttendance (CoursesTakenByTeachers_ID)" _
& "SELECT CoursesTakenByTeachers_ID FROM tblCoursesTakenByTeachers WHERE tblCoursesTakenByTeachers.Teacher_ID = " & Me.cbo_Teacher_Name & " AND tblCoursesTakenByTeachers.Course_ID = " & Me.cbo_Course_Name & " ;"
strSql_2 = "INSERT INTO tblTeachersAttendance (AttendanceDate, AttendanceDuration, AttendanceNote)" _
& " Values(#" & Me.AttendanceDate & "#, " & Me.AttendanceDuration & ", '" & Me.Attendance_Notes & "') ;"
Debug.Print strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql_2
End Sub
But as you may guess, it creates 2 different rows. I just need to insert those values into single row. I use the following code but error 3346 comes up. I don't what is wrong.
Private Sub UPDATETEACHER_Click()
Dim strSql As String
strSql = "INSERT INTO tblTeachersAttendance ([CoursesTakenByTeachers_ID], AttendanceDate, AttendanceDuration, AttendanceNote)" _
& "SELECT CoursesTakenByTeachers_ID FROM tblCoursesTakenByTeachers WHERE tblCoursesTakenByTeachers.Teacher_ID = " & Me.cbo_Teacher_Name & " AND tblCoursesTakenByTeachers.Course_ID = " & Me.cbo_Course_Name & " " _
& " Values(#" & Me.AttendanceDate & "#, " & Me.AttendanceDuration & ", '" & Me.Attendance_Notes & "') ;"
Debug.Print strSql
DoCmd.RunSQL strSql
End Sub
I really appreciate for your help. I already tried INSERT INTO SELECT and UPDATE second part (I mean AttendanceDate, AttendanceDuration, AttendanceNote) but i couldn't I don't which reference I should use in UPDATE code to put values in the same row.