SQL insert

[Steve_in_Kent]

trying to add 38 values using vba into a table. i'm ok with 1 value, but any more, and i get the error message ''number of query values and destination fields are not the same"

I think the first field in the table is causing it, its an Autonumber

Code: Select all

strSql = "INSERT INTO STI_Table_Main ( raiser ) Values (" & Chr(34) & myname & Chr(34) & ","
strSql = strSql & Chr(34) & raisedate & Chr(34)
strSql = strSql & ");"
CurrentDb.Execute strSql, dbFailOnError

I tried using the 1st field i want the data to start from (raiser), in the above, but that didn't seem to work. I'm presuming its the Autonumber field in the above, which is causing the error.

how can i add 38 fields, but let the autonumber do its job properly.

[HansV]

You don't have to include the AutoNumber field in the SQL - Access will populate it automatically. But you do have to list all the fields that you want to fill, e.g.:

Code: Select all

    strSQL = "INSERT INTO STI_Table_Main (LastName, SomeDate, NumberField) VALUES (" & _
        Chr(34) & myname & Chr(34) & ", " & Format(mydate, "#yyyy-mm-dd#") & ", " & mynumber & ")
    CurrentDb.Execute strSql, dbFailOnError

If no errors occur when you run this code, the AutoNumber field will be filled in automatically even though it isn't mentioned in the SQL string.

[Steve_in_Kent]

ahh thats what threw me..

i did a google search and found a page, but it didn't mention that all the fields you want to fill, have to be explicitly mentioned !!!

thanks hans