We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

RecordSet Not Advancing on .MoveNext

Medium Priority
686 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

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


Author

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
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.

Author

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

Author

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.
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
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Scanman999's latest answer is the best answer.  That should be the solution.  I apologize for any inconvenience.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.