Update and Refresh table problem

[stevebanks]

Hi guys, can someone help? I have a table of stock forecast. I want to be able to update the stock with forecasted delivery information based on demand/returns forecasts. I need to go to a certain record when the stock hits a low value, fill a value in, then recalculate all the stock/demand/returns forecast data, however when i do this, the data doesn't seem to update so i keep getting stuck on the first line of data which needs updating.

This is my code

Function insertfcastonorder()

Dim rs  As DAO.Recordset

Dim db  As Database
Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT * FROM fcast_details")
        
        Do Until rs.EOF     
         If rs("stocklowlevel") > rs("fcaststocklevel") Then
                With rs
                    .Edit
                    rs("fcastonorder") = rs("stocklowlevel")
                    .Update
                    MsgBox ("Just updating" & rs.AbsolutePosition)
                 End With
                    rs.MoveFirst
                        Do Until rs.EOF

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


End Function

Hope that all makes sense??

Thanks!!

 

[Flippertje]

Try changing this:

With rs
.Edit
.Fields![fcaststocklevel] = stkLevel
demand = .fields![fcastdemand]
returns = .fields![fcastreturns]
cancellations = .fields![fcastcancellations]
.update
End With

thecode = 2