My records are being inserted in the MIDDLE of the recordset!

Hi there,

I've struggled with this issue for a couple of months now, and have altered my code several times in an effort to solve the problem, but to no avail.  So here I am. :-)

I have developed a fairly simple VB/Access application that is installed on an employee machine.  The employee enters data through the application - it goes into his local database, and then at the end of the day he uploads his records to a database on the server.

The whole process seems to work quite well the majority of the time.  However, about once or twice a week, something weird happens.  His new records end up going into the server database in the wrong order - for example, the order of IDs of the new records might be 1275, 1276, 1277, 1278, 1272, 1273, 1274. I want them to go into the server DB in the same order that they're in his local database - but it sometimes puts the last few records in the middle of the other new records.  I thought that the .AddNew method always added the records to the end of the recordset.  Do I need to .MoveLast before I add the new records?  What I can't understand is that it works fine 80% of the time!

Here is the code in my upload routine...I've removed some irrelevant code, so if there's a missing End If or something, that's the reason why. :-)

Does anyone have any ideas why my code sometimes inserts the new records into the middle of the recordset?  I've tried stepping through in code but it always works fine then - of course! :)

Thanks!!!!  Christy :-)


--------------------------------------------------------------------------------------------------------
Private Sub mnuTUploadNew_Click()
Dim i As Integer
Dim bDone As Boolean
Dim iID As Integer

'upload all new records
    datServerDB.Refresh
   
'sets the ID of the new record
    If Not datServerDB.Recordset.EOF Then
        datServerDB.Recordset.MoveLast
        iID = datServerDB.Recordset("ID") + 1
    Else
        iID = 1
    End If

'refreshes datNew - the database that contains the ID of all the new records to be uploaded
    datNew.Refresh
     
    datNew.Recordset.MoveFirst

    Do While Not datNew.Recordset.EOF
   
'take the ID from datNew, then go to the main table and find the rest of the info for the
'record that is being uploaded
        datAudit.Refresh
        datAudit.Recordset.MoveFirst
        bDone = False
       
        Do While Not datAudit.Recordset.EOF
       
            If datAudit.Recordset("ID") = datNew.Recordset("ID") Then

                datServerDB.Refresh
                datServerDB.Recordset.AddNew
                datServerDB.Recordset("ID") = iID
                datServerDB.Recordset("Date") = datRollAudit.Recordset("Date")
                datServerDB.Recordset("User") = datRollAudit.Recordset("User")
                datServerDB.Recordset("Roll") = datRollAudit.Recordset("Roll")
                datServerDB.Recordset("DocType") = datRollAudit.Recordset("DocType")
                datServerDB.Recordset("Municipality") = datRollAudit.Recordset("Municipality")
                datServerDB.Recordset("Comments") = datRollAudit.Recordset("Comments")
                datServerDB.Recordset.Update
                iID = iID + 1
                datNew.Recordset.Delete
                bDone = True
                If Not datNew.Recordset.EOF Then datNew.Recordset.MoveNext
                Exit Do
            End If
            datRollAudit.Recordset.MoveNext
        Loop
   
        datRollAudit.Recordset.MoveNext
    Loop
   
    MsgBox "Your new records have been uploaded to the server. Your local database will now be refreshed with the records from the server.", vbOKCancel, "Upload Successful!"
                                                           
    Call RefreshRecords

End Sub
---------------------------------------------------------------------------------------------------------