Access 2010 VBA not saving changes to fields

Question

I am using VBA in Access 2010 to calculate some formulas with data from one table, and update the fields in another table. The problem I'm running into is that the fields from the second table are not updating as they should. I have inserted a breakpoint and stepped through each line of the VBA code, and the calculations are all performing correctly, and I receive no errors. Can anyone help with some suggestions on what to look for?

In the code provided, the final If Not rsPenetration.EOF block is the section not updating.

Private Sub Form_Load()
DoCmd.SetFilter WhereCondition:="[Hour] > 7"

Dim rsRecords As New ADODB.Recordset
Dim rsPenetration As New ADODB.Recordset
Dim rsProjected As New ADODB.Recordset
Dim rsHourlyDials As New ADODB.Recordset
Dim currentDate As Date

Dim goal8 As Double
Dim goal9 As Double
Dim goal10 As Double
Dim goal11 As Double
Dim goal12 As Double
Dim goal13 As Double
Dim goal14 As Double
Dim goal15 As Double
Dim goal16 As Double
Dim goal17 As Double
Dim goal18 As Double
Dim goal19 As Double
Dim goal20 As Double
Dim goal21 As Double
Dim goal22 As Double

Dim hourly8 As Double
Dim hourly9 As Double
Dim hourly10 As Double
Dim hourly11 As Double
Dim hourly12 As Double
Dim hourly13 As Double
Dim hourly14 As Double
Dim hourly15 As Double
Dim hourly16 As Double
Dim hourly17 As Double
Dim hourly18 As Double
Dim hourly19 As Double
Dim hourly20 As Double
Dim hourly21 As Double
Dim hourly22 As Double

Dim recordPct8 As Integer
Dim recordPct9 As Integer
Dim recordPct10 As Integer
Dim recordPct11 As Integer
Dim recordPct12 As Integer
Dim recordPct13 As Integer
Dim recordPct14 As Integer
Dim recordPct15 As Integer
Dim recordPct16 As Integer
Dim recordPct17 As Integer
Dim recordPct18 As Integer
Dim recordPct19 As Integer
Dim recordPct20 As Integer
Dim recordPct21 As Integer
Dim recordPct22 As Integer

Dim cumulativeGoal As Integer
Dim totalRecords As Integer
Dim projectedPenetration As Double

currentDate = Date

'Get daily records total from NewESDialsPerList table
rsRecords.Open "SELECT * FROM NewESDialsPerList WHERE CallDate = #" & currentDate & "#", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rsRecords.EOF Then
    totalRecords = rsRecords.Fields("CombinedRecords")
End If
rsRecords.Close

'Get hourly dials from ESDialsPerHour table and calculate hourly percent
rsHourlyDials.Open "SELECT * FROM ESDialsPerHour WHERE ESDate = #" & currentDate & "#", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rsHourlyDials.EOF Then
    hourly8 = (rsHourlyDials.Fields("Eight") / totalRecords)
    hourly9 = (rsHourlyDials.Fields("Nine") / totalRecords)
    hourly10 = (rsHourlyDials.Fields("Ten") / totalRecords)
    hourly11 = (rsHourlyDials.Fields("Eleven") / totalRecords)
    hourly12 = (rsHourlyDials.Fields("Twelve") / totalRecords)
    hourly13 = (rsHourlyDials.Fields("Thirteen") / totalRecords)
    hourly14 = (rsHourlyDials.Fields("Fourteen") / totalRecords)
    hourly15 = (rsHourlyDials.Fields("Fifteen") / totalRecords)
    hourly16 = (rsHourlyDials.Fields("Sixteen") / totalRecords)
    hourly17 = (rsHourlyDials.Fields("Seventeen") / totalRecords)
    hourly18 = (rsHourlyDials.Fields("Eighteen") / totalRecords)
    hourly19 = (rsHourlyDials.Fields("Nineteen") / totalRecords)
    hourly20 = (rsHourlyDials.Fields("Twenty") / totalRecords)
    hourly21 = (rsHourlyDials.Fields("TwentyOne") / totalRecords)
    hourly22 = (rsHourlyDials.Fields("TwentyTwo") / totalRecords)
End If

'Get projected penetration from ESProjectedPenetration table
rsProjected.Open "SELECT * FROM ESProjectedPenetration WHERE ProjectedDate = #" & currentDate & "#", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rsProjected.EOF Then
    projectedPenetration = rsProjected.Fields("ProjectedPenetration")
End If

goal8 = (totalRecords * projectedPenetration) * (hourly8 / totalRecords)
goal9 = (totalRecords * projectedPenetration) * (hourly9 / totalRecords)
goal10 = (totalRecords * projectedPenetration) * (hourly10 / totalRecords)
goal11 = (totalRecords * projectedPenetration) * (hourly11 / totalRecords)
goal12 = (totalRecords * projectedPenetration) * (hourly12 / totalRecords)
goal13 = (totalRecords * projectedPenetration) * (hourly13 / totalRecords)
goal14 = (totalRecords * projectedPenetration) * (hourly14 / totalRecords)
goal15 = (totalRecords * projectedPenetration) * (hourly15 / totalRecords)
goal16 = (totalRecords * projectedPenetration) * (hourly16 / totalRecords)
goal17 = (totalRecords * projectedPenetration) * (hourly17 / totalRecords)
goal18 = (totalRecords * projectedPenetration) * (hourly18 / totalRecords)
goal19 = (totalRecords * projectedPenetration) * (hourly19 / totalRecords)
goal20 = (totalRecords * projectedPenetration) * (hourly20 / totalRecords)
goal21 = (totalRecords * projectedPenetration) * (hourly21 / totalRecords)
goal22 = (totalRecords * projectedPenetration) * (hourly22 / totalRecords)

rsPenetration.Open "SELECT * FROM NewPIDGoals WHERE PenetrationDate = #" & currentDate & "#", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Not rsPenetration.EOF Then
    rsPenetration.Fields("Eight") = goal8
    rsPenetration.Fields("Nine") = goal9
    rsPenetration.Fields("Ten") = goal10
    rsPenetration.Fields("Eleven") = goal11
    rsPenetration.Fields("Twelve") = goal12
    rsPenetration.Fields("Thirteen") = goal13
    rsPenetration.Fields("Fourteen") = goal14
    rsPenetration.Fields("Fifteen") = goal15
    rsPenetration.Fields("Sixteen") = goal16
    rsPenetration.Fields("Seventeen") = goal17
    rsPenetration.Fields("Eighteen") = goal18
    rsPenetration.Fields("Nineteen") = goal19
    rsPenetration.Fields("Twenty") = goal20
    rsPenetration.Fields("TwentyOne") = goal21
    rsPenetration.Fields("TwentyTwo") = goal22
    'rsPenetration.Save
End If

End Sub

Answer 1

After assigning new rsPenetration.Fields() values you need to use the rsPenetration.Update method to commit the changes (write them back to the table).