ADODB.AddNew not generating incremental ID

[KeithIT]

Hi all.

I have a form which, when the user clicks "Save" generates (or is supposed to generate) a new record in a series of tables using the AddNew method in ADODB.Recordset.

My code is as follows:

Set rstInformation as New ADODB.Recordset
With rstInformation
     .Open "tblInformation", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
     .AddNew
     .Fields(1) = Me.txtFirstName
     .Fields(2) = Me.txtLastName
     .Update
     .Close
End With

Set rstInformation = Nothing

When I run the code, I get an error on the ".Update" line that says that the action would cause a duplicate value in an index. When I run a message box to show the ID, it does in fact create a record with the same ID as a previous record.

In my tables, the only indexed field is the unique ID, which is set to autonumber, and is the first column (hence, no .Fields(0)).

I am using this method because there is in fact more information gathered on the form and entered into multiple tables, otherwise I'd simply have the user navigate to a new record using the MS default code (DoCmd.GoToRecord...).

If anyone has had any experiences like this with the AddNew method, or notices an error in my code or my logic, I would most appreciate the assistance.

Thanks!

 

[KeithIT]

Foolish me. For anyone who found this post as a result of having the same problem, I'll post the resolution.

Create a new table with the same structure, and then copy and paste the records from the first table into the second table (or if you are query proficient, and don't want to waste the time creating a new table manually, use the CreateTable query).

I tried adding a new record to the table directly (i.e.: go to the very last row and start typing). Apparently there was something amiss with the table itself because the new record I created in the table had a repeat UniqueID, which of course is incorrect. After moving the records to a new table and allowing the new table to add UniqueIDs to the records (had to re-number some of the records in my other tables, but there's queries for that type of stuff) all my code worked correctly.