db.OpenRecordset(strRecSrc, DB_OPEN_DYNASET)

January 5th, 2006, 05:03 AM

Hi, I am baffled as to why a routine will not update all the records.
Below is a snapshot of the code I am using, I have not included it all because it involves 84 months and would be too long
What I am trying to do is update a field called "CurrentBudget" but some reason it will not update about 90 records out of 1000. When I create a new table and only have a few records it does update them.
For info I am using Access 2000
The code is below:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strRecSrc
Dim i

strRecSrc = "tblPremium_Budget"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strRecSrc, DB_OPEN_DYNASET)

On Error GoTo err_rsmoveError

'move to last record this establishes how records there are in the recordset
rs.MoveLast

'move to first record

rs.MoveFirst

'do loop through all records until end is reached
Do While Not rs.EOF

' value of elapsed is calculated in the query and is the difference between
' the start date of the contract and the system date in months

intElapsed = rs("elapsed")

'the following variables are filled from the table tblPremium_Budget
'recordsets and are used to calculate the current allocated budget
'by deciding which month value to use dependant and how many onths
'have elapsed since the start of the contract

Month(1) = rs("Month01")
Month(2) = rs("Month02")
Month(3) = rs("Month03")
Month(4) = rs("Month04")
Month(5) = rs("Month05")
Month(6) = rs("Month06")
Month(7) = rs("Month07")
Month(8) = rs("Month08")
Month(9) = rs("Month09")
Month(10) = rs("Month10")

' the value of elapsed is now compared to each of the conditions below and then
' a corresponding value is assigned to the month variable and txtResult

rs.Edit

If intElapsed < 1 Then
        curResult = Month(1)
        rs("CurrentBudget") = curResult
ElseIf intElapsed >= 1 And intElapsed < 2 Then
        curResult = Month(1)
        rs("CurrentBudget") = curResult
ElseIf intElapsed >= 2 And intElapsed < 3 Then
        curResult = Month(2)
        rs("CurrentBudget") = curResult
ElseIf intElapsed >= 3 And intElapsed < 4 Then
        curResult = Month(3)
        rs("CurrentBudget") = curResult
ElseIf intElapsed >= 4 And intElapsed < 5 Then
       curResult = Month(4)
        rs("CurrentBudget") = curResult
ElseIf intElapsed >= 5 And intElapsed < 6 Then
        curResult = Month(5)
        rs("CurrentBudget") = curResult
endif

'update recordset
rs.Update
'move to next record
rs.MoveNext
Loop

err_rsmoveError:
If Err.Number = 3021 Then
    MsgBox "You have moved outside the recordset"
    Resume Next
End If

'close recordset

rs.Close

'close database

db.Close
Thanks for any help
Regards
Peter

JpJoe · January 5th, 2006, 06:51 AM

Hi Peter,

I Can't see any reason why the records would not be updated. The only thing I can think of is that when your conditions are being tested, some of your records do not meet your conditions set and so do not get updated. Try adding an 'Else' at the end of your If block to capture records that don't meet your conditions. Add a value something like '999999' so that you can easily find it and distinguishes it from other records. Then check your recordset for that value after the routine has run. e.g.

Code:

ElseIf intElapsed >= 4 And intElapsed < 5 Then
       curResult = Month(4)
        rs("CurrentBudget") = curResult
ElseIf intElapsed >= 5 And intElapsed < 6 Then
        curResult = Month(5)
        rs("CurrentBudget") = curResult
Else
        rs("CurrentBudget") = 999999
endif

Let us know how you get on!

J

January 5th, 2006, 07:46 AM

Hello Jon
Thanks for your reply
I have tried that without success
What I have tried is recreating the table with 6 of the records that would not previously update and then run the procedure and it works okay
It appears that the code that is allocating the rs - Month(1) = rs("Month01") is not picking all the values up
I have checked the table and the values are there
Seems very strange to me
Regards
Peter

JpJoe · January 5th, 2006, 08:13 AM

Hi Peter,

Oh Dear!! If you think that the months part of the routine is not working then lets test it using the code below. (I Guess that the part

Quote:

quote:Month(1) = rs("Month01")
Month(2) = rs("Month02")
Month(3) = rs("Month03")
Month(4) = rs("Month04")
Month(5) = rs("Month05")
Month(6) = rs("Month06")

is an array being populated from the recordset.

Code:

Dim arrMonths(80) 'Month Array to hold rst data (Option Base 0??)
Dim e 'Array Element
Dim x as integer

    'Load the Months array
    Do While x < 81 'You have 81 months/81 fields ?
        If x < 10 Then
            arrMonths(x) = rs("Month0" & x + 1)
        Else
            arrMonths(x) = rs("Month" & x + 1)
        End If
        x =x + 1
    Loop

    'Now TEST the array for values
    For Each e In arrMonths()
        If arrMonths(e) > 0 Then
            'OK
        Else
            'If this msg is shown then this is the cause of your problems
            MsgBox "Element empty"
        End If
    Next e

Replace your code

Quote:

quote:Month(2) = rs("Month02")
Month(3) = rs("Month03")
Month(4) = rs("Month04")
Month(5) = rs("Month05")

etc. with the snippet above. Also the loop part is going to be easier to view than 81 lines of Month(x) = rs("MonthX")! Let us know what happens

January 5th, 2006, 11:18 AM

Hello Jon
Well we are getting there the code above worked fine I just had to alter the <10 part to <9.
It has highlighted what I think was the main problem. I noticed that a lot of the month fields were zero and when i deleted all these zero fields the others field updated okay have you any idea why a zero field would cause the routine not run correctly
Otherwise I will need to look closely and see if we need these zero records and automatically delete them
Regards
Peter