Loop ending in wrong place

[Teresa via AccessMonster.com]

I have a table linked to an exchange folder containing emails
(tblCallsToBeLogged).
I am importing some fields into another table (tblLoggedCalls) and then
deleting the corresponding record in tblCallsToBeLogged.
Here's the problem: it imports all but one record.
Where am I going wrong?
Thanks!

------------------------------------------------------------------------------
--------------------------------

Public Sub Import()

Dim dbs As Database
Dim rstExchange As Recordset, rstUsers As Recordset
Dim Receiver As Variant, postIt As Integer
Dim Requester As Variant, Received As Variant
Dim Recorded As Variant, Subject As Variant
Dim Details As Variant, Attachments As Variant

Set dbs = CurrentDb
Set rstExchange = dbs.OpenRecordset("tblCallsToBeLogged")
Set rstUsers = dbs.OpenRecordset("tblLoggedCalls", DB_OPEN_TABLE)

rstExchange.MoveFirst

Do Until rstExchange.EOF ' Begin loop.

On Error Resume Next
rstUsers.AddNew ' Create new record.

rstUsers("Requester") = rstExchange!From
rstUsers("Receiver") = rstExchange!To
rstUsers("Received") = rstExchange!Received
rstUsers("Recorded") = rstExchange![Creation Time]
rstUsers("Subject") = rstExchange![Normalized Subject]
rstUsers("Details") = rstExchange!Body
rstUsers("Attachments") = rstExchange![Has attachments]

rstExchange.Delete
rstUsers.Update ' Save changes.

On Error GoTo errCmdUserDetails


rstExchange.MoveNext ' Locate next record.

Loop ' End of loop.

exitCmdUserDetails:

rstExchange.Close ' Close table.
rstUsers.Close

Set dbs = Nothing

Exit Sub
errCmdUserDetails:

GoTo exitCmdUserDetails

End Sub

 

[Tom van Stiphout]

On Wed, 16 Jan 2008 12:26:18 GMT, "Teresa via AccessMonster.com"

First of all take out the "On error resume next". You want to be
notified if some unexpected error occurs.
Next add a line inside of the loop to print some data to the Immediate
window:
debug.print rstExchange!<some_unique_field>
This will help you find out if one is being skipped, etc.
Also, print the rstExchange.RecordCount
Another technique is to temporarily comment out the .Delete line.
Perhaps deleting in an ongoing loop is affecting the record pointer?
Especially since you haven't MoveNext yet. Then delete the items at
the end of your loop with a Delete query.

Otherwise your code looks good.

-Tom.

 

[Klatuu]

The problem you are having is how the loop is operating. Here is the scenerio:

You open the table and you are pointing at the first record.
After adding the data to the other recordset, you delete the record.
Now the record that was #2 in the recordset is record #1.
Then you MoveNext. Now you have bypassed #2 and moved to #3.

I would suggest you not do the delete in the loop, but when the loop is
done, close the recordset, then use a Delete query to delete the records from
tblCallsToBe Logged:

CurrentDb.Execute("DELETE * FROM tblCallsToBeLogged;"), dbFailOnError

 

[ttansell via AccessMonster.com]

Thanks and very well explained.
It all seems so obvious when you put it like that.
Works like a charm.
Teresa

The problem you are having is how the loop is operating. Here is the scenerio:

You open the table and you are pointing at the first record.
After adding the data to the other recordset, you delete the record.
Now the record that was #2 in the recordset is record #1.
Then you MoveNext. Now you have bypassed #2 and moved to #3.

I would suggest you not do the delete in the loop, but when the loop is
done, close the recordset, then use a Delete query to delete the records from
tblCallsToBe Logged:

CurrentDb.Execute("DELETE * FROM tblCallsToBeLogged;"), dbFailOnError

I have a table linked to an exchange folder containing emails
(tblCallsToBeLogged).

[quoted text clipped - 58 lines]

End Sub