Problems with Recordset MoveNext not moving!

[HedgePig]

I'm having a strange problem with some VBA code: when reading through a table, the Recordset.MoveNext command doesn't always appear to be moving to the next record.

Basically, I have a record set pointing to a table in a database. For each record I read in, I do some processing and create up to 15 output records in a different table. The first input record reads in fine and the output records look good. However, when I process the second input record and try and create a new output record, I get an update error saying I'm trying to create duplicate records. This shouldn't happen because the output records will be unique if the input records are - and I believe they are. However, stepping through the debugger shows that the second input record read in appears to be the same as the first. So it seems that the recordset pointer hasn't actually moved on to the next record. If I remove the unique indices constraint in the output table, then the second set of output records is just the same as the first.
This has me baffled for several reasons
- the program has been working for a couple of years, without any problems. Although it's not run frequently, it has processed hundreds of input files in the past
- when I'm debugging, and checking RecordSet.AbsolutePosition immediately after RecordSet.MoveNext, I can see that the absolute position has increased by one.
- usually the second time around, MoveNext works although sometimes it works first time.

I don't know if this is relevant but in case it is:
- both the input and output tables are actually tables in a different Access database. In the database I am running this from, they are linked in at run time.
- We recently switched from XP/Office 2007 to Windows 7/2010. I thought this would be the culprit. But it turns out the user who reported the problem is still running this on an XP/Office 2007 machine!

Please enlighten me!


This is a great simplification of the code

Sub Main()
'/Lots of stuff here
   Set SourceInfoRS = CurrentDb.OpenRecordset(str)
   Call CalcResults2(SourceInfoRS, EndDate)
'/Lots more stuff
End Sub

Sub CalcExposure2(SourceInfoRS As Recordset, EndDate As Double)
Dim TargetResRS  As Recordset              'Table where results will be posted to
   Set TargetResRS = CurrentDb.OpenRecordset(loctgtExpRname)
   'Loop through Source Info table
   Do While Not SourceInfoRS.EOF
      currdate = TargetResRS!startdate
     
      Do While currdate < EndDate
        
         'Perform some calculations based on SourceInfoRS and other info
         '///
        
         'Add a new target record
         TargetResRS.AddNew
         TargetResRS.Field1 = Calc1
         TargetResRS.Field2 = Calc2
         TargetResRS.Field3 = Calc3
         TargetResRS.Update
        
         currdate = currdate + 365    'A simplification just for illustration!
      
      Loop
     
      SourceInfoRS.MoveNext '**** This doesn't seem to "work" every time.
   Loop
 
End Sub

 

[HedgePig]

Update.....problem solved. Sort of.
The problem was not with the MoveNext at all but with the table containing the input records - there ARE duplicate entries oops which is causing the apparent problem. I still have to work out why this is happening.