Updating Next Record

[jwboreman]

I am trying to update records in an Access 2007 database.The database tracks meals served for a summer food program. On the attached form there is a subform displaying meals served for the week at a particular site. The "Left" (LeftOver) field are meals that are left over for that day. All meals that are leftover should be transferred to the next day. The Prev field is were this value should display. This works most of the time, but for some reason the Left field will not calculate properly. The Calculation happens on After Update on Del, Trans, Rec, 1st, and 2nd fields are updated. The code is listed below and a screen shot is attached. The Tables are stored on an SQL server. Can you see what I am doing wrong?





Private Sub UpdateMeal()
On Error GoTo Err_UpdateMeal

Dim conn As ADODB.Connection 'Connection to the database
Dim rst As ADODB.Recordset 'Recordset to get/hold the Data
Dim strSQL As String
Dim msDate As Date
Dim nxtDate As Date
Dim SumFoodLnk As Integer
Dim lftOver As Integer
Dim ActCont As String
Dim MealKey As Integer
Dim ServDay As Integer

ActCont = Me.ActiveControl.Name
MealKey = Forms!frm2DailyMealService!frm3DailyMealService!MealServiceKey

SumFoodLnk = Forms!frm2DailyMealService!SummerFoodKey

lftOver = Forms!frm2DailyMealService!frm3DailyMealService!LeftOver
msDate = Forms!frm2DailyMealService!frm3DailyMealService!ServDate

UpdateTotalServed

If DatePart("w", msDate) = 6 Then 'Do not add meals to Saturday
nxtDate = msDate
' Exit Sub
Else:
nxtDate = msDate + 1
End If

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset

strSQL = "SELECT dbo.tblYear.DefaultYear, dbo.tblMealPeriod.DefaultPeriod, dbo.tblMealService.MealServiceKey, dbo.tblSummerFood.SummerFoodKey, dbo.tblMealService.LeftOver, dbo.tblMealService.ServDate, dbo.tblMealService.PreviousDay, dbo.tblMealService.Delivered, dbo.tblMealService.Received, dbo.tblMealService.Transferred, dbo.tblMealService.TotalServed, dbo.tblMealService.Damaged, dbo.tblMealService.Disallowed, dbo.tblMealService.AdultsServed FROM dbo.tblMealPeriod INNER JOIN dbo.tblYear RIGHT OUTER JOIN dbo.tblSummerFood ON dbo.tblYear.YearKey = dbo.tblSummerFood.YearLink INNER JOIN dbo.tblMealService ON dbo.tblSummerFood.SummerFoodKey = dbo.tblMealService.SummerFoodLink ON dbo.tblMealPeriod.MealPeriodKey = dbo.tblMealService.MealPeriodLink WHERE (dbo.tblYear.DefaultYear = 1) AND (dbo.tblMealPeriod.DefaultPeriod = 1) AND ((dbo.tblMealService.SummerFoodLink) = " & SumFoodLnk & ");"

rst.Open strSQL, conn, adOpenStatic, adLockOptimistic 'Opens the recordset

rst.MoveFirst
rst.Find ("ServDate = " & msDate), , adSearchForward

Do While DatePart("w", nxtDate) <> 2
lftOver = ((rst!Delivered + rst!PreviousDay + rst!Received - rst!Transferred) - rst!TotalServed - rst!Damaged - rst!Disallowed - rst!AdultsServed)
rst!LeftOver = lftOver
UpdateTotalServed
rst.Update
rst.MoveNext
If rst.EOF Then
Exit Do
End If
nxtDate = rst!ServDate
If DatePart("w", nxtDate) <> 2 Then
rst!PreviousDay = lftOver
End If
Loop

rst.Requery

rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing

Forms!frm2DailyMealService!frm3DailyMealService.Form.Requery

DoCmd.GoToRecord , , acNext

Forms!frm2DailyMealService!frm3DailyMealService.Form.Requery
Forms!frm2DailyMealService!frm3DailyMealService!MealServiceKey.Visible = True
Forms!frm2DailyMealService!frm3DailyMealService!MealServiceKey.BackColor = -2147483643
Forms!frm2DailyMealService!frm3DailyMealService!MealServiceKey.SetFocus
DoCmd.FindRecord MealKey

Select Case ActCont
Case "Ordered"
Forms!frm2DailyMealService!frm3DailyMealService!Delivered.SetFocus
Case "Delivered"
Forms!frm2DailyMealService!frm3DailyMealService!Transferred.SetFocus
Case "Transferred"
Forms!frm2DailyMealService!frm3DailyMealService!Received.SetFocus
Case "Received"
Forms!frm2DailyMealService!frm3DailyMealService!FirstServed.SetFocus
Case "FirstServed"
Forms!frm2DailyMealService!frm3DailyMealService!SecondServed.SetFocus
Case "SecondServed"
Forms!frm2DailyMealService!frm3DailyMealService!AdultsServed.SetFocus
Case "AdultsServed"
Forms!frm2DailyMealService!frm3DailyMealService!Damaged.SetFocus
Case "Damaged"
Forms!frm2DailyMealService!frm3DailyMealService!Disallowed.SetFocus
Case "Disallowed"
Forms!frm2DailyMealService!frm3DailyMealService!Comments.SetFocus
End Select

Forms!frm2DailyMealService!frm3DailyMealService!MealServiceKey.BackColor = -2147483643
Forms!frm2DailyMealService!frm3DailyMealService!MealServiceKey.Visible = True

Exit_UpdateMeal:
Set rst = Nothing
Set conn = Nothing
Exit Sub
Err_UpdateMeal:
MsgBox Err.Description
Resume Exit_UpdateMeal

End Sub

 

[jwboreman]

I resolved this issue by sorting the SQL statement by the date.