For each... Next ???? Not working

[stevebanks]

Hi,

I have a bit of code which is forecasting through a recordset and updating records with stock data from the previous weeks sales/returns/cancellations forecast...

It's working, but not quite how i need it to! Hence the question!
The code is:

Function updfcaststocklevel()

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("fcast_details")

rs.MoveFirst

Do Until rs.EOF
i = i + 1
If i = 1 Then
stklevel = rs("fcaststocklevel")
Else
stklevel = stklevel - rs("fcastdemand") + rs("fcastreturns") - rs("fcastcancellations")
End If
rs.Edit
rs("fcaststocklevel") = stklevel
rs.Update
rs.MoveNext
Loop
MsgBox ("Done!")


End Function

Sorry if the code is a bit messy, i'm relatively new to this.
My trouble is, the "fcast_details" table contains data for all products. I need the stock levels to update for each product separately. I have been trying to use:

Dim itemcode As Variant
itemcode= rs("itemcode")

For each itemcode In rs

    blah blah blah

Next

But it's not working and causing an error. Trouble at the moment is I'm getting stock data between products mixed up..

Please can someone help!!!

Thanks ever so much

Steve

 

[Crowley16]

a recordset isn't exactly a collection so you can't use for each inside it

you can order your code to have a nested loop, i.e.

select distinct all item types in table
while not eof
select all items in table of current item type
while not eof
update...
loop
loop

--------------------
Procrastinate Now!

 

[stevebanks]

Sorry.. not quite sure how i would implement could you explain a little more please?

Thanks

 

[MajP]

Maybe

Function updfcaststocklevel()

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("fcast_details")
Dim stkLevel As Long

rs.MoveFirst
'First record
stkLevel = rs.Fields("fcaststocklevel")
rs.MoveNext
'The other records
Do While Not rs.EOF
  stkLevel = stkLevel - rs.Fields("fcastdemand") + rs.Fields("fcastreturns") - rs.Fields("fcastcancellations")
  rs.Edit
  rs.Fields("fcaststocklevel") = stkLevel
  rs.Update
  rs.MoveNext
Loop
MsgBox ("Done!")
End Function

You never said what was not working, and what was the error.

 

[JerryKlmns]

I think he "says" that there are many itemcode of the same value that he needs to calculate over the group of the itemcode?????

Function updfcaststocklevel()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim PrevItem As Long
Dim CurItem As Long

Set db = CurrentDb()
strSQL = "Select NZ([fcastdemand]), NZ([fcastreturns]), NZ([fcastcancellations]), itemcode  From fcast_details Order By itemcode"
Set rs = db.OpenRecordset(strSQL)
With rs
   If Not (.EOF And .BOF) Then
      .MoveFirst
      PrevItem = -1
      Do Until .EOF
         CurItem = .Fields("itemcode")
         If PrevItem = CurItem Then
            stklevel = stklevel - .Fields("fcastdemand") + .Fields("fcastreturns") - .Fields("fcastcancellations")
         Else
            stklevel = .Fields("fcaststocklevel")
         End If
         .Edit
         .Fields("fcaststocklevel") = stklevel
         .Update
         .MoveNext
         PrevItem = CurItem
      Loop
   End If
   .Close
End With

Set rs = Nothing
Set db = Nothing

MsgBox ("Done!")

End Function

 

[stevebanks]

That's correct, exactly what i'm trying to do!

I'm getting a type mismatch on this line of your code Jerry:

         CurItem = .Fields("itemcode")

Any ideas?

Thanks

 

[JerryKlmns]

Dim CurItem As Long

Is .Fields("itemcode") a numeric data type of long or text?

 

[stevebanks]

My itemcodes are 1234XYZ format if that would make a difference?

 

[stevebanks]

This is the code i have now:

Function updfcaststocklevel()

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("fcast_details")

With rs
    .MoveFirst
        demand = 0
        returns = 0
        cancellations = 0
    Do Until .EOF
        itemcode = .Fields("itemcode")
        i = 0
    Do Until .Fields("itemcode") <> itemcode Or .EOF
        i = i + 1
    If i = 1 Then
         stklevel = rs("fcaststocklevel")
    Else
         stklevel = stklevel - demand + returns - cancellations
    End If
    .Edit
    .Fields("fcaststocklevel") = stklevel
    .Update
    demand = .Fields("fcastdemand")
    returns = .Fields("fcastreturns")
    cancellations = .Fields("fcastcancellations")
    stklevel = .Fields("fcaststocklevel")
    .MoveNext
    Loop
    .MoveNext
    Loop
    MsgBox ("Done!")
End With


End Function

But on the record after the itemcode changes resets the stock level to zero and then continues as normal... Is there something i'm missing?

 

[JerryKlmns]

A very BIG one!

Change
Dim PrevItem As Long
Dim CurItem As Long
PrevItem = -1

To
Dim PrevItem As String
Dim CurItem As String
PrevItem =

 

[stevebanks]

It is producing wild results... nothing like the actual ones...
fcastdemand fcastreturns fcastcancellations fcaststocklevel
2 0 1 -71
3 0 1 -80
2 0 0 -82
2 0 0 -84
2 0 0 -86
2 0 1 -89
0 0 0 -89
0 0 0 -89
0 0 0 -89
2 0 1 -92
3 0 1 -96
2 0 0 -138
2 0 0 -102
2 0 0 -76
2 0 1 -106
2 0 1 -109
3 0 1 -113
2 0 1 -116
2 0 0 -118
2 0 0 -120
2 0 1 -123
3 0 1 -127
3 0 1 -131
2 0 1 -134
2 0 0 -136
2 0 1 -40
3 0 1 -33
3 0 1 -100
3 0 1 -5
2 0 1 -8
2 0 0 -10
2 0 0 -12
2 0 1 -15
0 0 0 -15
4 0 1 -20
4 0 1 -25
3 0 1 -29
2 0 1 -74
3 0 1 -37
3 0 1 0
3 0 1 -44
4 0 1 -49
3 0 1 -53
2 0 1 -56
3 0 1 -60
3 0 1 -64
1 0 0 -65
1 0 0 -66
1 0 0 -67
1 0 0 -68
1 0 0 -1
1 0 0 -103
5 0 1 -6
5 0 2 -152
4 0 1 -157
3 0 1 -161
3 0 1 -165
4 0 1 -170
1 0 0 -171
1 0 0 -172
1 0 0 -173
4 0 1 -178
5 0 2 -185
3 0 1 -265
3 0 1 -196
3 0 1 -145
4 0 1 -204
5 0 1 -210
5 0 2 -217
4 0 1 -222
3 0 1 -226
4 0 1 -231
4 0 1 -236
5 0 2 -243
6 0 2 -251
4 0 1 -256
4 0 1 -261
4 0 1 -76
5 0 1 -64
5 0 2 -192
5 0 2 -13
4 0 1 -18
3 0 1 -22
4 0 1 -27
4 0 1 -32
1 0 0 -33
7 0 2 -42
7 0 2 -51
5 0 2 -58
5 0 1 -141
5 0 2 -71
6 0 2 0
6 0 2 -84
7 0 2 -93
5 0 2 -100
4 0 1 -105
5 0 1 -111
5 0 2 -118
3 0 1 -122
3 0 1 -126
2 0 1 -129
2 0 1 -132
2 0 1 -135
2 0 1 -199

 

[JerryKlmns]

The code you 've posted doesn't match with mine!

 

[stevebanks]

no, using your code, that's what i get...!!

 

[JerryKlmns]

Modified

Function updfcaststocklevel()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim PrevItem As String
Dim CurItem As String

Set db = CurrentDb()
strSQL = "Select NZ([fcastdemand]), NZ([fcastreturns]), NZ([fcastcancellations]), itemcode  From fcast_details Order By itemcode"
Set rs = db.OpenRecordset(strSQL)
With rs
   If Not (.EOF And .BOF) Then
      .MoveFirst
      PrevItem = .Fields("itemcode")
      Do Until .EOF
         .MoveNext
         CurItem = .Fields("itemcode")
         If PrevItem = CurItem Then
            stklevel = stklevel - .Fields("fcastdemand") + .Fields("fcastreturns") - .Fields("fcastcancellations")
            .Edit
            .Fields("fcaststocklevel") = stklevel
            .Update
         End If
         PrevItem = CurItem
      Loop
   End If
   .Close
End With

Set rs = Nothing
Set db = Nothing

MsgBox ("Done!")

 

[stevebanks]

Getting this now ---> any ideas?

fcastdemand fcastreturns fcastcancellations fcaststocklevel
2 0 1 -71
3 0 1 -80
2 0 0 -82
2 0 0 -84
2 0 0 -86
2 0 1 -89
0 0 0 -89
0 0 0 -89
0 0 0 -89
2 0 1 -92
3 0 1 -96
2 0 0 -138
2 0 0 -102
2 0 0 -76
2 0 1 -106
2 0 1 -109
3 0 1 -113
2 0 1 -116
2 0 0 -118
2 0 0 -120
2 0 1 -123
3 0 1 -127
3 0 1 -131
2 0 1 -134
2 0 0 -136
2 0 1 -40
3 0 1 -33
3 0 1 -100
3 0 1 -5
2 0 1 -8
2 0 0 -10
2 0 0 -12
2 0 1 -15
0 0 0 -15
4 0 1 -20
4 0 1 -25
3 0 1 -29
2 0 1 -74
3 0 1 -37
3 0 1 0
3 0 1 -44
4 0 1 -49
3 0 1 -53
2 0 1 -56
3 0 1 -60
3 0 1 -64
1 0 0 -65
1 0 0 -66
1 0 0 -67
1 0 0 -68
1 0 0 -1
1 0 0 -103
5 0 1 -144 (change of itemcode here to end)
5 0 2 -290
4 0 1 -295
3 0 1 -299
3 0 1 -303
4 0 1 -308
1 0 0 -309
1 0 0 -310
1 0 0 -311
4 0 1 -316
5 0 2 -323
3 0 1 -403
3 0 1 -334
3 0 1 -283
4 0 1 -342
5 0 1 -348
5 0 2 -355
4 0 1 -360
3 0 1 -364
4 0 1 -369
4 0 1 -374
5 0 2 -381
6 0 2 -389
4 0 1 -394
4 0 1 -399
4 0 1 -214
5 0 1 -202
5 0 2 -330
5 0 2 -151
4 0 1 -156
3 0 1 -160
4 0 1 -165
4 0 1 -170
1 0 0 -171
7 0 2 -180
7 0 2 -189
5 0 2 -196
5 0 1 -279
5 0 2 -209
6 0 2 0
6 0 2 -222
7 0 2 -231
5 0 2 -238
4 0 1 -243
5 0 1 -249
5 0 2 -256
3 0 1 -260
3 0 1 -264
2 0 1 -267
2 0 1 -270
2 0 1 -273
2 0 1 -337

 

[stevebanks]

This is my final code. Its working fine now APART from the at the end it's not actioning an EOF and is looping back round and causing an error... Can't seem to stop it??? Anyone got any suggestions?

Thank you

Function updfcaststocklevel()

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM fcast_details WHERE date > (Now()-7)")
   
                        
                        Do Until rs.EOF
                        thecode = 1
                        itemcode = rs.Fields("itemcode")
                        
                        
                        Do Until itemcode <> rs("itemcode")
                                If thecode = 1 Then
                                    stkLevel = rs("fcaststocklevel")
                                Else
                                    stkLevel = stkLevel - demand + returns - cancellations
                                End If
                                                    
                        rs.Edit
                        rs.Fields("fcaststocklevel") = stkLevel
                        rs.Update
                                demand = rs.Fields("fcastdemand")
                                returns = rs.Fields("fcastreturns")
                                cancellations = rs.Fields("fcastcancellations")
                                stkLevel = rs.Fields("fcaststocklevel")
                        thecode = 2
                        rs.MoveNext
                        Loop
                        Loop
                        MsgBox ("Done!")
        
       db.Close
End Function

It's the line

Do Until itemcode <> rs("itemcode")

causing the error : No current record

Thanks again!!

 

[Golom]

If you say it's "working fine" then OK ... but you're not assigning a value to the function name so nothing will be returned by this routine.

You are getting the "No Current Record" because of the two do loops. The outside one terminates on rs.EOF but the inside one doesn't and the rs.MoveNext is getting to EOF and then looping to try another record that doesn't exist.

I assume that stkLevel, demand, returns and cancellations are defined elsewhere.

Function updfcaststocklevel()

Dim rs                          As DAO.Recordset
Dim db                          As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset( _
    "SELECT * FROM fcast_details WHERE date > (Now()-7)")

Do Until rs.EOF

    If itemcode <> rs("itemcode") Then
        itemcode = rs.Fields("itemcode")
        thecode = 1
    Else
        If thecode = 1 Then
            stkLevel = rs![fcaststocklevel]
        Else
            stkLevel = stkLevel - demand + returns - cancellations
        End If
        With rs
            .Edit
            ![fcaststocklevel] = stkLevel
            .Update
            demand = ![fcastdemand]
            returns = ![fcastreturns]
            cancellations = ![fcastcancellations]
            stkLevel = ![fcaststocklevel]
            thecode = 2
        End With
    End If

    rs.MoveNext
Loop
MsgBox ("Done!")

db.Close
End Function

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[stevebanks]

Thank you ever so much Golom!