|
Subject:
|
db.OpenRecordset(strRecSrc, DB_OPEN_DYNASET)
|
|
Posted By:
|
ppenn
|
Post Date:
|
1/5/2006 4:03:23 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
|
|
Reply By:
|
JpJoe
|
Reply Date:
|
1/5/2006 5:51:57 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.
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
1/5/2006 6:46:22 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
|
|
Reply By:
|
JpJoe
|
Reply Date:
|
1/5/2006 7:13:24 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:
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.
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:
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
1/5/2006 10:18:00 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
|
