RecordSet Not Advancing on .MoveNext

Hi Experts,

Below, I have included my code.  This code is excuted by another function running on a loop.  The loop is working, but the recordset is not advancing.  The variable int_C is the Cycle Index that proves that the loop code is working.  

The first cycle should not .MoveNext and it doesn't.  The second cycle is when it should begin advancing.  It does advance 1 time - but it stops after the 2nd time through the cycle.  It only returns ths recordset for the second cycle, for the remaining cycles through the loop.

Private Sub cmdFB_Select_Click()
Dim rs_BATCH As DAO.Recordset
Dim str_BATCH As String
Dim x As Integer

lstSplash.AddItem ("Creating recordset.")

' [SQL_VBA_A2]'
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)

'MsgBox rs_BATCH.RecordCount'
lstSplash.AddItem ("RecordCount: " & rs_BATCH.RecordCount)
lstSplash.AddItem ("Cycle Index: " & int_C)

If int_C = 1 Then
    'do nothing right now'
Else
    rs_BATCH.MoveNext
    lstSplash.AddItem ("MOVE NEXT: " & int_C & "    " & rs_BATCH.AbsolutePosition)
    
End If

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

End Sub

Open in new window


Thank you!

-Wendee
pwdellsAsked:
Who is Participating?
 
Scanman999Commented:
If you want to optimize a bit the code, use a while/wend loop where you can make it error proof by also testing the End Of File, in case your record set you do your loop on runs out of records.

Because your int_CYCLES variable is counting records from another recordset based on a different SQL query, which is supposed to tell you how many records to retrieve from the next recordset.

    .....
    Set rs_BATCH = CurrentDb.OpenRecordset(str_BATCH)
    
    int_C = 0

    while not (rs.eof) and int_C<= int_CYCLES

	int_C =int_C +1

        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
    Wend

Open in new window

0
 
Scanman999Commented:
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


0
 
pwdellsAuthor Commented:
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Scanman999Commented:
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.
0
 
pwdellsAuthor Commented:
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

Open in new window

0
 
pwdellsAuthor Commented:
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.
0
 
Scanman999Commented:
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
0
 
pwdellsAuthor Commented:
Scanman999's latest answer is the best answer.  That should be the solution.  I apologize for any inconvenience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.