Error 3346: The number of query fields and destination fields are not the same

[hturgut]

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.

 

[ted.martin]

These 3 fields

AttendanceDate, AttendanceDuration, AttendanceNote

need to be included in the SELECT statement. At the moment your have 4 fields to INSERT but on ONE in the SELECT.

To ensure you don't make errors like this, I often create my SQL code from a standard Query using the QBE grid, then go to the SQL view, copy the code and 'tweak' it; especially if variables are involved. That way all your fieleds will match.

 

[hturgut]

Thanks for your relpy Ted.

The problem is just there. Because The fields
AttendanceDate, AttendanceDuration, AttendanceNote are not coming from where CoursesTakenByTeachers_ID comes.

I have a form which user enters students and teacher attendance easily. In that form User needs to input data into AttendanceDate, AttendanceDuration, AttendanceNote fields. But CoursesTakenByTeachers_ID is coming from the table tblCoursesTakenByTeachers where the condition in SELECT statement matches.

Therefore I tried to update 3 fields after entering SELECT statement. But I stuck there.

Any suggestions?

 

[ted.martin]

So if your form holds these fields, AttendanceDate, AttendanceDuration, AttendanceNote, then they need to be part of the Select statement.

In your SQL statement for the above fields try "SELECT CoursesTakenByTeachers_ID, #" & Me.AttendanceDate & "# , " & Me.AttendanceDuration & ", " & Me.AttendanceNote & "


If this doesn't work, then try posting a (zipped) copy of your db here and I will take a look.

 

[hturgut]

Thanks Ted,

I did the following code

Private Sub UPDATETEACHER_Click()

Dim strSql As String


strSql = "INSERT INTO tblTeachersAttendance (CoursesTakenByTeachers_ID, AttendanceDate, AttendanceDuration, AttendanceNote)" _
& "SELECT CoursesTakenByTeachers_ID SELECT CoursesTakenByTeachers_ID, #" & Me.AttendanceDate & "# , " & Me.AttendanceDuration & ", '" & Me.AttendanceNote & "' FROM tblCoursesTakenByTeachers WHERE tblCoursesTakenByTeachers.Teacher_ID = " & Me.cbo_Teacher_Name & " AND tblCoursesTakenByTeachers.Course_ID = " & Me.cbo_Course_Name & " , #"


Debug.Print strSql
DoCmd.RunSQL strSql


End Sub

Somehow it gives Compile error: Method or data member not found for Mr. Attendance Duration.

Sorry but I am not professional I don't know why it didn't appear for Attendancedate.

What do you suggest?

I really appreciate for your help.

 

[ted.martin]

Almost there - just double-check you have the forms control names correct. Your reply above has a space between Attendance and Duration where your SQL does not. (not to mention the Mr instead of Me).

 

[hturgut]

It is my mistake. Everthing is correct. There is no space and yes it is Me.AttendanceDuration. still same.

I tried to attach but 2MB limit. It is a bit big file. more than 10 MB.

 

[ted.martin]

Zip the file or just send the tables and the key form you are using that calls the SQL

 

[hturgut]

I am sorry but I don't know how to send only tables. How can I?

 

[ted.martin]

As you don't have a 'split' database then just copy the whole file, open it and delete all the bits I don't need and then zip and send that. All I need is the two tables you refer too and also the Form where the 3 troublesome fields are stored.

 

[ted.martin]

Just a thought, is Duration a Date/Time field? if so, it will need the #" & Me.DurationDate & "

 

[hturgut]

it is General number field

 

[ted.martin]

Ok - send file as suggested and I will take a look at it for you.

 

[hturgut]

Thanks for your help.

I attached the tables and forms.

Form : frmClassAttendanceEntry

In this form. At the bottom, Update Teacher Attendance button has got problem.

Thanks

 

[ted.martin]

Try (Copy and Paste) this

strSql = " INSERT INTO tblTeachersAttendance ( CoursesTakenByTeachers_ID, AttendanceDate, AttendanceDuration, AttendanceNote )" & _
"SELECT tblCoursesTakenByTeachers.CoursesTakenByTeachers_ID, #" & Me.AttendanceDate & "#, '" & Me.Attendance_Note & "' " & _
"FROM tblCoursesTakenByTeachers"

 

[hturgut]

I did that but it is the same. I think you've made a mistake that you typed twice AttendanceDate twice instead of AttendanceDuration.

I tried yours and changed the second one AttendanceDuration but it is the same error.

I read something on forums now that Forms can be corrupted. I've checked everthing on Form (whether there are same name or something) nothing is there.

Thanks

 

[ted.martin]

Thanks = yes i did. I also noticed that in the first line when you break the string, you omitted the &. Leave it with me a few more minutes. We will crack it.

 

[hturgut]

Yesss I did it.

Private Sub UPDATETEACHER_Click()

Dim strSql As String

strSql = "INSERT INTO tblTeachersAttendance (CoursesTakenByTeachers_ID, AttendanceDate, AttendanceDuration, AttendanceNote)" _
& "SELECT CoursesTakenByTeachers_ID, #" & Me.AttendanceDate & "#, " & Me.AttendanceDuration & " , '" & Me.Attendance_Notes & "' " _
& "FROM tblCoursesTakenByTeachers WHERE tblCoursesTakenByTeachers.Teacher_ID = " & Me.cbo_Teacher_Name & " AND tblCoursesTakenByTeachers.Course_ID = " & Me.cbo_Course_Name & " ;"

Debug.Print strSql
DoCmd.RunSQL strSql


End Sub

Thank you very much for your help.

 

[ted.martin]

You beat me to it.

As I suggested, when you are having trouble with SQL, design it initially in the QBE grid.

See my example Query

Well Done

 

[hturgut]

I understand it. It is really much easier. Thanks for everthing.