Problems with Recordset MoveNext not moving!


Board Regular
Jun 27, 2002
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.Field1 = Calc1
         TargetResRS.Field2 = Calc2
         TargetResRS.Field3 = Calc3
         currdate = currdate + 365    'A simplification just for illustration!
      SourceInfoRS.MoveNext '**** This doesn't seem to "work" every time.
End Sub

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back