ACCESS/VBA: Insert Into with autonumbering field returns annoying warning

Ok, here's the situation

Let's have Table1(A,B,C)

A is an autonumbering field.

I'm feeding the table via vba.

Since A is autonumbering, I'm ignoring it like so:

SQL = INSERT INTO TABLE1(B,C) VALUES ('something','something else')
DoCmd.RunSQL SQL

This works ok, access gives me a 1st warning that I'll be creating a new row.

Which is ok to me. However just after that I get this:

Microsoft Access can't add all the records in the update or append query.

It set 1 field(s) to Null due to a type conversion failure.

blahblahblah click ok to run the query anyway

Which doesn't prevent it from working if I click ok, but I don't want my user to see that warning.

And anyway why does i开发者_C百科t pop up ? Isn't it normal to leave the autonumbering field blank ?

Is there another procedure I don't know about ? What am I missing ?

I looked around google and here but couldn't find an answer :/

(I don't want to setwarnings to false since I want the first warning of adding a field and any other eventual error to be visible.)

Microsoft Access can't add all the records in the update or append query. It set 1 field(s) to Null due to a type conversion failure.

That error has nothing to do with your autonumber field. Check the data types of fields B and C.

In this example, field C is set to number, so I get the same error as you, and the INSERT succeeds but with Null in field C:

DoCmd.RunSQL "INSERT INTO TABLE1(B,C) VALUES ('something','something else')"

However, inserting a numeric value into field C works fine.

DoCmd.RunSQL "INSERT INTO TABLE1(B,C) VALUES ('something',99)"

Edit: This one also works in spite of supplying a text value for field C. The difference is the text value is one the database engine can convert to a valid number:

DoCmd.RunSQL "INSERT INTO TABLE1(B,C) VALUES ('something','27')"

Is it clearly due to the autonumbering field? Once the record is added (running the query clicking on 'run anyway'), only the autonumber field is blank?

This error shouldn't be happening for Autonumber; at least, I've never seen it before.

It sounds like you are passing in an empty string to one of the values and it's setting that column to NULL.

When you say

Which doesn't prevent it from working if I click ok

Do all of the fields get populated correctly? Including the Autonumber?