Problem adding a record via ADODB with an AutoNumber field.

[Rob Hofkens]

Hello everyone,

Think this is a quick one for you professionals
I am just a starting programmer, so here is my question:
I have a table on a SQL Server that has an autonumber ID field thats bugging
me
I use linked tables via ODBC and this is the connection on my form (I
simplified it a bit):

strSQL = "SELECT * FROM MyTable"
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

I hope this is ok so far.

I created a sub that handles new and edited records and I created 2 arrays
with fields and their values
The table field names are the same as on the form and Field1 is the
autonumber field.
I made sure that the fields on the form are validated before I save them.
I use unbound fields.

aFields = Array ("field2", "Field3", etc)
aValues = Array (me.field2, me.Field3,etc)

If bNewRecord Then
rst.AddNew aFields, aValues
Else
rst.Update aFields, aValues
End If

When I edit an record and update it then I get no error.
When I try to add a record it doesn't work
This is the error I get (free translation to english):

The field MyTable.Field1 can not contain a NULL value because the property
Required for this field is set to True.
Enter a value for this field.

I suspect that ADO somehow tries to save the value of the autonumber field
and I know that's wrong.
That's why I didn't put it in the .AddNew field list !

So, any idea what I am doing wrong then ?

Thanx in advance !

Rob.