RecordSet Not Advancing on .MoveNext

Hi,
If I understand well, the code shown is a Subroutine called multiple times by another routine.
And each time that othe routine calls this code it increments int_C ?

If that's the case, you problem is that your RESET your recordset each time in this code,line 15.
Therefore it will always show the second record whatever the value int_C being >1.

You should put your SQL query and recordset opening in the parent routine, outside/before the loop (lines 8 to 15).

Cheers,
Andy

I considered doing so today and something told me not to.  I will go back and try to think of why I did/could not.  I will repost later tonight.  Thank you, Andy.

Do you know how to goto a specific record w/in the RS?  (Besides First and Last?)

Wendee

You can use .FindFirst or .Seek to locate a record with the current recordset.

Have a quick look here : http://allenbrowne.com/ser-29.html

Maybe also post the calling routine as well (at least the loop part) so I can see how you call your subcode inside your loop ?

Cheers.

I took your recommendation and rewrote most of the code.  I was trying to stay away from putting so much code in the "parent" module, but couldn't get away with it for the time constraints I had.  This is the code I ended up with:  

Private Sub cmdStart_Batch_Click()
    Dim c, x As Integer
    Dim strSQL_FCST, strQAP_FCST, str_BATCH As String
    Dim rs_FCST, rs_BATCH As DAO.Recordset
    
    Call ClearTemp
    
    '[VBA_SQL_XX]'
    strQAP_FCST = "INSERT INTO TMP_FCST_BATCH " & _
        "(FCST_ID, BASIC_MAT, MPG, CALEN_PER, PRIORITY, ABC, COUNTRY, SREGION, " & _
            "FCST_QTY, SWX, [WHERE], PASS, BATCH_REC_COMP ) " & _
        "SELECT YAV_FORECAST.ID AS FCST_ID, YAV_FORECAST.BASIC_MAT, " & _
            "YAV_FORECAST.MPG, YAV_FORECAST.CAL_PER, YAV_FORECAST.COUNTRY_P, " & _
            "IIf(IsNull([ABC_REQ]),'B',[ABC_REQ]) AS QUALITY, YAV_FORECAST.COUNTRY, " & _
            "YAV_FORECAST.SR, YAV_FORECAST.WRK_TARGET, '000' AS SWX, " & _
            "'WHERE' AS [WHERE], False AS PASS, False AS BATCH_REC_COMP " & _
        "FROM YAV_FORECAST " & _
        "WHERE (((YAV_FORECAST.ID) = 49)) " & _
            " Or (((YAV_FORECAST.ID) = 294)) " & _
            " Or (((YAV_FORECAST.ID) = 483)) " & _
            " Or (((YAV_FORECAST.ID) = 202)) " & _
        "ORDER BY YAV_FORECAST.BASIC_MAT ASC, YAV_FORECAST.CAL_PER ASC, " & _
            "YAV_FORECAST.COUNTRY_P ASC, IIf(IsNull([ABC_REQ]),'B',[ABC_REQ]) DESC "
    'MsgBox (strQAP_FCST)'
    DoCmd.RunSQL (strQAP_FCST)
    lstSplash.AddItem ("Appending Forecast records to TMP_FCST_BATCH")
    
    '[VBA_SQL_XX]'
    strSQL_FCST = "SELECT YAV_FORECAST.ID " & _
        "FROM YAV_FORECAST " & _
        "WHERE (((YAV_FORECAST.ID) = 49)) " & _
            " Or (((YAV_FORECAST.ID) = 294)) " & _
            " Or (((YAV_FORECAST.ID) = 483)) " & _
            " Or (((YAV_FORECAST.ID) = 202)) "
    Set rs_FCST = CurrentDb.OpenRecordset(strSQL_FCST)
    
    int_CYCLES = rs_FCST.RecordCount
    lstSplash.AddItem ("Cycle Count: " & int_CYCLES)
    
    '[VBA_SQL_XX]'
    str_BATCH = "SELECT ID, FCST_ID, BASIC_MAT, MPG, CALEN_PER, " & _
        "PRIORITY, ABC, COUNTRY, SREGION, FCST_QTY, SWX, WHERE, " & _
        "PASS, BATCH_REC_COMP " & _
    "FROM TMP_FCST_BATCH " & _
    "ORDER BY BASIC_MAT, CALEN_PER, PRIORITY, ABC DESC , FCST_QTY "

    Set rs_BATCH = CurrentDb.OpenRecordset(str_BATCH)
    
    rs_FCST.MoveFirst
    For int_C = 1 To int_CYCLES
        lstSplash.AddItem ("*************************************************")
        'lstSplash.AddItem ("ID: " & rs_FCST.Fields(0))'
        int_ID = rs_BATCH.Fields(0)
        int_FCST_ID = rs_BATCH.Fields(1)
        str_BAS_MAT = rs_BATCH.Fields(2)
        str_MPG = rs_BATCH.Fields(3)
        str_CALEN_PER = rs_BATCH.Fields(4)
        str_PRIORITY = rs_BATCH.Fields(5)
        str_ABC = rs_BATCH.Fields(6)
        str_COUNTRY = rs_BATCH.Fields(7)
        str_SREGION = rs_BATCH.Fields(8)
        dbl_FCST_QTY = rs_BATCH.Fields(9)
        str_SWX = rs_BATCH.Fields(10)
        str_WHERE = rs_BATCH.Fields(11)
        bln_PASS = rs_BATCH.Fields(12)
        bln_BATCH = rs_BATCH.Fields(13)
        
        For x = 0 To 13
            lstSplash.AddItem ("rs_BATCH.Fields(" & x & ") = " & rs_BATCH(x))
        Next x
            
        Call cmdFB_CASE_Click
        
        rs_BATCH.MoveNext
    Next int_C
End Sub

Select allOpen in new window

I am not sure if this is the right thing to do.  My answer is the solution, but I do want to give credit to Scanman999.

Hi Wendee/pwdells,

Any reason why you absolutely want to use a sub routine and not want to have it all in the same module ?

Does it work now ? if yes, this means I was right and you had a flaw in your code, therefore you had to rewrite, I don't see what other better answer you could have had.

If you want to have the loop part put in a sub routine, all you have to do is put lines 51 to 70 in a sub routine and replace those lines in the main code by the subroutine Call.... but this is not "optimizing the code", this is "clarifying only", your routine will not run faster or better, it will maybe be clearer to read.

Priority in programming is to make your code work properly, then think in optimising it.
I work by the KISS principle ... Keep It Simple and Stupid ... so you have a working base before fooling around and try optimizing the code.

Cheers & Thanks,
Andy

Scanman999's latest answer is the best answer.  That should be the solution.  I apologize for any inconvenience.