SQL Insert error msg:" number of query values and destination fields not the same?"

[gint32]

Can anyone tell me why the example1 on my forms after update event gives me the following error message "number of query values and destination fields not the same?"
Yet the 2nd example works and injects without a hitch into the table "attend".


All I have changed is to added another field to the list so as I can "catch/pickup/record" the logged on user that has updated/amended or appended a record via a form, does seem to be to difficult I would have thought!, but I am pulling my hair out on this one

Example1...

RecDetect = DLookup("[scrStudent]", "attend", "[attstudent] = " & Me![scrStudent] & " AND [Attdate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(RecDetect) Then
    strSql = "INSERT INTO Attend ( AttStudent, AttDate, LogOnUser ) " _
    & "SELECT " & Me![scrStudent] & " AS F1, #" & Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, #" & Me![txtUserId] & " AS F4;"
    DoCmd.RunSQL strSql

else...do something else

Example2...

RecDetect = DLookup("[scrStudent]", "attend", "[attstudent] = " & Me![scrStudent] & " AND [Attdate] = #" & Format(TDate, "mm/dd/yy") & "#")

If IsNull(RecDetect) Then
    strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
    & "SELECT " & Me![scrStudent] & " AS F1, #" & Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3;"
    DoCmd.RunSQL strSql

else...do something else

What I am I doing wrong, as I have no clue how remedy this one!
and have been staring at this for hours now(I have attached the example)
Many thanks in advance for any pointers towards a fix...

[pbaldy]

The message tells you the problem. You have 3 fields in the INSERT clause and 4 in the SELECT.

[gint32]

Thanks for the reply, Ok, I have now amended to have four of each and still gives the same message:
I pasted the wrong code so here it is again - corrected: see attachment sample1
So, if I use the code from Example1 is doesn't work, whereas Example 2 does, But of course I wish for Example1 to work so as I get that 4th field populated


I am attempting to add an additional field to the sql statement, which would be the logged on user.


'notworking below
example 1

'If IsNull(RecDetect) Then
   ' strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser ) " _
    '& "SELECT " & Me![scrStudent] & " AS F1, #" & Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, #" & Me![txtUserId] & " AS F4;"
   ' DoCmd.RunSQL strSql

'working
example2

  
    If IsNull(RecDetect) Then
    strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
    & "SELECT " & Me![scrStudent] & " AS F1, #" & Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3;"
    DoCmd.RunSQL strSql

[CJ_London]

you seem to have a surplus # the first query- should be

....& "# AS F2, " & TypeAttend & " AS F3, " & Me![txtUserId] & " AS F4;"

[gint32]

thanks, but changed to suit as below, unfortunately I still get the same message

If IsNull(RecDetect) Then
   strSql = "INSERT INTO Attend ( AttStudent, AttDate, LogOnUser ) " _
    & "SELECT " & Me![scrStudent] & " AS F1, #" & Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " & Me![txtUserId] & " AS F4;"
    DoCmd.RunSQL strSql

[gint32]

   
  strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser ) " & _
           "SELECT " & Me![scrStudent] & " AS F1, #" & Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " & Me![txtUserId] & " AS F4;"
  Debug.Print strSql
  DoCmd.RunSQL strSql

[gint32]

best to have a look at the attachment

[gint32]

you seem to have a surplus # the first query- should be

....& "# AS F2, " & TypeAttend & " AS F3, " & Me![txtUserId] & " AS F4;"

output of debug...INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser ) SELECT 219477 AS F1, #03/07/16# AS F2, 1 AS F3, gerrynew1 AS F4;

[CJ_London]

usually fields with an ID as part of the name are numeric, but I see from your debug output it is text so you need to use single quotes

...." AS F3, '" & Me![txtUserId] & "' AS F4;"

what happens if you copy and paste the (revised) sql generated by the debug? Does it run?

The reason I ask is normally you need a FROM and a table if using a SELECT, but you say your second query in your initial post works (which also doesn't have a FROM) so it can't be that.

When updating tables with data not from another table I use the construct

INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser ) VALUES(219477, #03/07/16# , 1, 'gerrynew1')

[gint32]

many thanks for your efforts, yeh. ID doesn't in this instance refer to a numeric value...But good news is, I finally got it to inject using the following code
...I didn't know that datatypes were defined by using different quotes like ' or " or # I just assumed if it wouldn't go into the table it would throw a make table for entries that wouldn't insert...

' ...
  strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser ) VALUES (" & _
           Me![scrStudent] & ", #" & Format(TDate, "mm/dd/yy") & "#, " & TypeAttend & ", '" & Me![txtUserId] & "');"
  Debug.Print strSql
  DoCmd.RunSQL strSql
' ...