Hi, I am using the addnew funti

[jpb]

Hi,

I am using the addnew funtion associated with a recordset object to add data to a Table in my database...
The table has an ID field which of the type auto number
so each new record added is supposed to be added at the end of the table.

However this is not happening..Instead each new record is being given the same ID value and overwriting the record that already had that ID value..ie.When I add a new record
which should be given an ID of 20( Next value of autoincrement) but instead it is being given the value of the first record in the database in my case 2 and inserting this new record into position 2 in the table..overwriting what was originally there..

Has this ever happened to anyone before and idf so please reveal how you fixed it..

thanks in advance

 

[RickSpr]

I suspect you're not calling the Update method for each record, but rather only doing one call to the Update method after your record adding loop. The Update call should be within the loop. For example:
For Each record to be added
rst.AddNew
rst!Field1 = Value1
rst!Field2 = Value2
...etc...
rst!Update
Next record

The new record is not actually written to the database until the Update method is called. If you don't have Update inside your loop, then each time you do the AddNew method, you're erasing the previous fields and resetting the autonumber field to the same value. When you finally exit the loop and then do the Update call, the final record is written to the database, but none of the preceding ones have been written. Rick Sprague

 

[jpb]

Hi Rick,
Tried that , but my problems seems to be purely with the Addnew method , I stepped trough my program and found that the update was being cancelled .UPDATE CANCELLED is my error
what could be causing this does any one know..I have the same code in a few places in my code and it works fine.

Thanks in advance

jpb

 

[RickSpr]

I couldn't find any such error message. What is the error number? Rick Sprague

 

[jpb]

Thanks Rick,

Here is the error number. " -2147217842"
Any ideas would be great as It has me very puzzled

Thanks in advance
jpb

 

[jpb]

Rick,

I have found what was wrong with my code. Before I called the AddNew method I was assigning the text properties of my text boxes to "" so when I pulled up the form on screen the boxes would be blank . But since the addNew method does this already then I was simlpy doing it twice which was giving me my error..

Thanks for your help anyway though it was much appreciated.

jpb