Looping through recordset stops after 1st iteration even though recordcount correct

[Margarita]

Hello,
I'm trying to update a table called EmployeeTask_FTE with insert statements via vba. I have two recordsets based on queries:

rs_FTEforupdate based on:

SELECT employeetask_FTE.lastname, employeetask_FTE.firstname, servicesubcat, Worktimetype, FTE, FTEEntryType
FROM employeetask_FTE INNER JOIN FTE_outyears_TEMPselectnames ON (employeetask_FTe.firstname= FTE_outyears_TEMPselectnames.firstname) AND (employeetask_FTe.lastname= FTE_outyears_TEMPselectnames.lastname)
WHERE FTEmonthstart= #6/1/2012#;

where FTE_outyears_TEMPselectnames selects names based on some criteria I want.

rs_datesforupdate is based on a query that selects some dates out of a table.

These queries run ok. The vba code that uses these two recordsets is below. The problem is that the first Do While loop stops after the first iteration- the entries are inserted only for one person (though the dates loop runs ok).
Debug.print of the recordcounts of the two recordsets gives the correct number. Can someone help me to see why my loop is stopping short?
Thank you!

Code:

Sub Insert_outyear_FTE()
 
Dim qdf_FTEforupdate As QueryDef
Set qdf_FTEforupdate = CurrentDb.QueryDefs!FTE_outyears_TEMPS_subcatsFTEs_to_insert
Dim rs_FTEforupdate As DAO.Recordset
Set rs_FTEforupdate = qdf_FTEforupdate.OpenRecordset
Dim qdf_datesforupdate As QueryDef
Set qdf_datesforupdate = CurrentDb.QueryDefs!FTE_outyears_TEMPS_datesforupdate
Dim rs_datesforupdate As DAO.Recordset
Set rs_datesforupdate = qdf_datesforupdate.OpenRecordset
 
rs_FTEforupdate.MoveLast
rs_datesforupdate.MoveLast
Debug.Print rs_FTEforupdate.RecordCount
Debug.Print rs_datesforupdate.RecordCount
rs_FTEforupdate.MoveFirst
rs_datesforupdate.MoveFirst
DoCmd.SetWarnings False
Do Until rs_FTEforupdate.EOF = True
Do While Not rs_datesforupdate.EOF = True
DoCmd.RunSQL "Insert into EmployeeTask_FTE (servicesubcat, lastname, firstname, worktimetype, FTE, " & _
"FTE_last_modified_date, FTEMonthStart, FTEMonthEnd, FTEEntryType) values " & _
"('" & rs_FTEforupdate!ServiceSubcat & "', '" & rs_FTEforupdate!LastName & "', '" & rs_FTEforupdate!FirstName & "', '" & _
rs_FTEforupdate!worktimetype & "', " & rs_FTEforupdate!FTE & ", #" & Now() & "#, #" & _
rs_datesforupdate!startdatepar & "#, #" & rs_datesforupdate!enddatepar & "#, '" & rs_FTEforupdate!FTEEntryType & "');"
rs_datesforupdate.MoveNext
Loop
rs_FTEforupdate.MoveNext
Loop
DoCmd.SetWarnings True
End Sub

 

[Rx_]

Opened yours by mistake, funny, I just troubleshot the same thing, first record only.
You are generating an error is my guess.
Open up your debug window and set a condition - err.number and set the break on change.
Seeing the debug.print statements - add a couple of
Debug.Print "My Error Number is: " & Err.Number & " " & Err.Description
before and after your docmd.runsql

Also,
Create a string variable named SQLString and assign your "Insert into...." to it
Then use DoCmd.RunSQL SQLString
This way you can Debug.Print Sqlstring
Copy the string and past it into a New Query - Bet you find the error in the Query window - it just takes one character

 

[Margarita]

Opened yours by mistake, funny, I just troubleshot the same thing, first record only.
You are generating an error is my guess.
Open up your debug window and set a condition - err.number and set the break on change.
Seeing the debug.print statements - add a couple of
Debug.Print "My Error Number is: " & Err.Number & " " & Err.Description
before and after your docmd.runsql

Also,
Create a string variable named SQLString and assign your "Insert into...." to it
Then use DoCmd.RunSQL SQLString
This way you can Debug.Print Sqlstring
Copy the string and past it into a New Query - Bet you find the error in the Query window - it just takes one character

Hi Rx, thanks for your suggestions. Just tried to debug.print the error number like you said and the error is 0, which I guess means there is no error. And the code is behaving as if there was no error, really. The insert statement runs and successfully appends the desired set of records for the first name in the rs_FTEforupdate set and then ends the loop. It's behaving as if the FTEforupdate recordset has only one record. But it's very odd that it's doing that since the it's printing the recordcount correctly- 189 records.
It has to be something on the inside of that loop...

Thanks!

 

[jdraw]

Did you try to step through the code using F8?
Follow the loop and see where it actually stops--- you can look att the Locals as wekk to see some values.

 

[Margarita]

Did you try to step through the code using F8?
Follow the loop and see where it actually stops--- you can look att the Locals as wekk to see some values.

Thanks for your suggestion to step through (I've always been resistant to the stepping through feature for some reason). Using it, I found my silly mistake. I neglected to move back to the first record after the inner dates loop was done running for each name. That's why it didn't insert anything for the rest of the names. Very silly.
Thank you for your suggestion.