Access VBA AddNew to recordset gives error on second time run in a loop

Question

I have a procedure in Access VBA that takes data from a csv file from a well known online auction site. I use Excel to present the data to Access, which references Excel to step through each row of data in the csv file with a For Next loop. Having gathered all the required data in variables I wish to transfer them into two tables in Access: tblSales (with the generic data for each Sale) and tblSalesItems (with the detail of each item sold within each sale). I use AddNew to a recordset for each of the tables. So for tblSales I have:

With rstSales
.AddNew
!SoldVia = strSoldVia
!Account = strAccount
...
...
!PostToCountry = strCountry
!PostToPhone = strPostToPhone
.Update
.Bookmark = .LastModified
lngSalesID = !SalesID.Value
End With

This works correctly all the time. There are then a number of “If” statements to find how many items there are, and for all the sales within the code up to the point where the problem occurs there is only a single item for each. So the next point in the code is effectively:
If ...
...
Else 'There is only one list item relating to the ebay item number
strListItemID = rstQryListIdFromEbayItemNumber!ListItemsID.Value
strStockItemID = rstQryListIdFromEbayItemNumber!StockItemsID.Value
End If

'Transfer data to tblSalesItems
With rstSalesItems
.AddNew
!SalesID = lngSalesID
!StockItemsID = CLng(strStockItemID)
!ListItemsID = CLng(strListItemID)
!EbayItemNumber = strEbayItemNumber
!SalePrice = curSalePrice
!PostageCost = curPostageCost
!NumberSold = intNumberSold
!DispatchDate = dteDispatchDate
!TrackingNumber = strTrackingNumber
!DeliveryService = strDeliveryService
.Update
End With

The First time that the code runs in the For Next loop everything works correctly. But for the second time around, the rstSales AddNew part runs correctly, but the rstSalesitems AddNew puts up an error when it gets to .Update.

The error is: Run-time error ‘3022’:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again.

The data in the csv file is test data, with every item being in exactly the same format, with a final number to differentiate between the fields within each record. I have checked the value of each variable using the Locals window and all the values are different from those in the first pass in the For Next Loop. Specifically ...ID variables are different and are correctly read from the csv file. The Primary Key in tblsalesItems (rstSalesItems) is autonumber. None of the fields is indexed except the PK. I tired removing a relationship between the ListItems table, again without success. There is a relationship between tblSales and tblSalesitems, but that is essential and cannot be removed.

I have followed the advice in the Microsoft Help pages, which suggests that there could be a snag with autonumber fields, without any success. If I enter all the data manually into tblSalesItems on the “New” record it accepts it without problem. I have tried resetting rstSalesItems between runs, again without success. It appears as if rstSalesItems is trying to assign to the new record the same autonumber as it did on the previous run, and I have tried combinations of MoveLast and Bookmarking to persuade it to move on, all without success.

It is particularly confusing that the first run around the loop works perfectly, but the second fails, and it fails only on the second recordset. I realise that I could try a DoCmd.RunSQL to append the data, but I would like to find what is going wrong in my current code. I would be grateful for any suggestions.

Answer 1

After nearly 3 weeks of trying I finally solved this problem by manually copying the offending table from scratch and then deleting the original. Clearly there was a corruption of some sort in the original despite my having followed all the Microsoft advice to deal with that eventuality.