Record that won't update

[David RH]

Hello

I have a loop that loads a number into a field in a table then moves to the next record and does is again. It works flawlessly until it gets to a particular record. This record the field does not load the number, it stays 0(the default value for all the number fields).

I'm working with an ADO record set built from an Access database file.

All the other records load the field just fine but this one.

The trouble record is not the first, last or only record in the table.

I'm poised at the open window with my computer held high over my head, gazing at the street far below.

Any ideas?

Thanks

David

[techgnome]

Well.... maybe.... can you post the code?
It'd be easier to see what is/isn't happening.

[David RH]

Ok I figured out what was happening.

Because of the goto that skips the part that normally moves the record pointer (causing it to update) I wasn't getting an update on this record. so I inserted the line "rsLinefactors.Update" and this corrected that problem.
But it now that that is fixed I see another record that is doing almost the same thing . This one updates most but doesn't get the last update. This one hit's 6 of the "TripNumber#" blocks each adding 50 to the "Trip#Factored" variable. This is then added to the record in the field - rsLinefactors.("ShowTimeFactor").value.
This should add up to 300 and it does if you step through the code lines. But the record only gets 250 in it.

 Do While rsLines.EOF = False
        TripsTypesCounter = 0
        TotalTrips = 0
        If rsLines.Fields("Trip1Total").Value = 0 Then
        	GoTo SkipForReserveLine
        Else
            TotalTrips = rsLines.Fields("Trip1Total").Value
            TripsTypesCounter = 1
        End If
        If rsLines.Fields("Trip2Total").Value <> 0 Then
            TotalTrips = TotalTrips + rsLines.Fields("Trip2Total").Value
            TripsTypesCounter = TripsTypesCounter + 1
        End If
    
~Snip~ Cut for Space – It’s just more of the same but 3-8
        
        If rsLines.Fields("Trip9Total").Value <> 0 Then
            TotalTrips = TotalTrips + rsLines.Fields("Trip9Total").Value
            TripsTypesCounter = TripsTypesCounter + 1
        End If
        
        If rsLines.Fields("Trip10Total").Value <> 0 Then
            TotalTrips = TotalTrips + rsLines.Fields("Trip10Total").Value
            TripsTypesCounter = TripsTypesCounter + 1
        End If

        If rsLines.Fields("TripNumber1").Value = "" _
            Or rsLines.Fields("TripNumber1").Value = " " _
            Or rsLines.Fields("TripNumber1").Value = 997 Then
            GoTo SkipForReserveLine
        Else
            rsLineFactors.MoveFirst
            LineNumber = rsLines.Fields("LineNumber").Value
            rsLineFactors.Find "LineNumber = '" & LineNumber & "'"
            
            rsTrips.MoveFirst
            TripNumber1 = rsLines.Fields("TripNumber1").Value
            Trip1Total = rsLines.Fields("Trip1Total").Value
            rsTrips.Find "TripNumber = '" & TripNumber1 & "'"
            Trip1ShowTime = rsTrips.Fields("ReportTime").Value
            If Trip1ShowTime < ShowTimeBefore Then
                If TripsTypesCounter = 1 Then
                    Trip1Factored = 30 * ShowTimePriority
                Else
                    Trip1Factor = Trip1Total / TotalTrips
                    Trip1Factored = (30 * Trip1Factor) * ShowTimePriority
                End If
                rsLineFactors.Fields("ShowTimeFactor").Value = Trip1Factored
            End If
        End If
        
        If rsLines.Fields("TripNumber2").Value = "" _
            Or rsLines.Fields("TripNumber2").Value = " " _
            Or rsLines.Fields("TripNumber2").Value = 997 Then
            GoTo SkipForReserveLine
        Else
            TripNumber2 = rsLines.Fields("TripNumber2").Value
            Trip2Total = rsLines.Fields("Trip2Total").Value
            rsTrips.MoveFirst
            rsTrips.Find "TripNumber = '" & TripNumber2 & "'"

            Trip2ShowTime = rsTrips.Fields("ReportTime").Value
            If Trip2ShowTime < ShowTimeBefore Then
                Trip2Factor = Trip2Total / TotalTrips
                Trip2Factored = (30 * Trip2Factor) * ShowTimePriority
                rsLineFactors.Fields("ShowTimeFactor").Value = _
                    rsLineFactors.Fields("ShowTimeFactor").Value + Trip2Factored
            End If
        End If

~Snip~ Cut for Space – More of the same but 3-8
        
        If rsLines.Fields("TripNumber9").Value = "" _
            Or rsLines.Fields("TripNumber9").Value = " " _
            Or rsLines.Fields("TripNumber9").Value = 997 Then
            GoTo SkipForReserveLine
        Else
            TripNumber9 = rsLines.Fields("TripNumber9").Value
            Trip9Total = rsLines.Fields("Trip9Total").Value
            rsTrips.MoveFirst
            rsTrips.Find "TripNumber = '" & TripNumber9 & "'"
            Trip9ShowTime = rsTrips.Fields("ReportTime").Value
            If Trip9ShowTime < ShowTimeBefore Then
                Trip9Factor = Trip9Total / TotalTrips
                Trip9Factored = (30 * Trip9Factor) * ShowTimePriority
                rsLineFactors.Fields("ShowTimeFactor").Value = _
                    rsLineFactors.Fields("ShowTimeFactor").Value + Trip9Factored
            End If
        End If

        If rsLines.Fields("TripNumber10").Value = "" _
            Or rsLines.Fields("TripNumber10").Value = " " _
            Or rsLines.Fields("TripNumber10").Value = 997 Then
            GoTo SkipForReserveLine
        Else
            TripNumber10 = rsLines.Fields("TripNumber10").Value
            Trip10Total = rsLines.Fields("Trip10Total").Value
            rsTrips.MoveFirst
            rsTrips.Find "TripNumber = '" & TripNumber10 & "'"
            Trip10ShowTime = rsTrips.Fields("ReportTime").Value
            If Trip10ShowTime < ShowTimeBefore Then
                Trip10Factor = Trip10Total / TotalTrips
                Trip10Factored = (30 * Trip10Factor) * ShowTimePriority
                rsLineFactors.Fields("ShowTimeFactor").Value = _
                    rsLineFactors.Fields("ShowTimeFactor").Value + Trip10Factored
            End If
        End If

SkipForReserveLine:

        rsLineFactors.Update
        rsLines.MoveNext
 Loop
 End If