How to add a new record with 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.


(P.S. I asked this question also in the modulesdaovba.ado news group a few
days ago with no response so far)

 

[Douglas J Steele]

I've never used the Array functionality, so I'm not sure whether there are
any limitations with it.

However, the following should work:

rst.AddNew
rst!field2 = Me.field2
rst!field3 = Me.field3
' etc.
rst.Update

 

[Rob Hofkens]

Thank Douglas !
Hopefuly this works

I've never used the Array functionality, so I'm not sure whether there are
any limitations with it.

However, the following should work:

rst.AddNew
rst!field2 = Me.field2
rst!field3 = Me.field3
' etc.
rst.Update