?
Solved

RecordSet Not Advancing on .MoveNext

Posted on 2011-04-18
8
Medium Priority
?
666 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
0
Comment
Question by:pwdells
  • 4
  • 4
8 Comments
 
LVL 4

Expert Comment

by:Scanman999
ID: 35420375
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
 

Author Comment

by:pwdells
ID: 35420681
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
 
LVL 4

Expert Comment

by:Scanman999
ID: 35422172
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:pwdells
ID: 35424441
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
 

Author Comment

by:pwdells
ID: 35424482
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
 
LVL 4

Expert Comment

by:Scanman999
ID: 35430904
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
 
LVL 4

Accepted Solution

by:
Scanman999 earned 2000 total points
ID: 35430950
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
 

Author Comment

by:pwdells
ID: 35435194
Scanman999's latest answer is the best answer.  That should be the solution.  I apologize for any inconvenience.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question