Error 3346: The number of query fields and destination fields are not the same (1 Viewer)

hturgut · Dec 18, 2010

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 · Dec 18, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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

hturgut · Dec 19, 2010

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

ted.martin · Dec 19, 2010

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 · Dec 19, 2010

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

hturgut · Dec 19, 2010

it is General number field

ted.martin · Dec 19, 2010

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

hturgut · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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 · Dec 19, 2010

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

Error 3346: The number of query fields and destination fields are not the same (1 Viewer)

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Similar threads

Users who are viewing this thread