Looping through records will not pick up the correct numbers

[Christine Vollberg via AccessMonster.com]

I have this report that is run from a cmd button. The click event contains
code that is suppose to determine whether or not the sentence is concurrent
or consecutive. If concurrent sets the TotalYears to the largest number of
years. If there are also consecutive sentences it needs to add the years
to the largest concurrent sentence. It works fine for concurrent sentences
but not for consecutive. I am not getting any errors either. Any help
would be greately appreciated. Here is the portion of the code that is
suppose to do the calculation:

Private Sub cmdUCD_Click()

Dim TotalMonths As Long
Dim SubTotal As Long
Dim TotalDays As Long
Dim Sent As Long
Dim rstDefCharge As Recordset
Dim rstConCons As Recordset
Dim db As DAO.Database
Dim TotalYears As Integer

Set db = DBEngine(0)(0)
Set rstConCons = db.OpenRecordset("Select * from
tblConcurentConsecutiveTable where PrimaryCaseNo = '" & Replace(Me!CaseNo,
"'", "''") & "' and ((DefendantId) = " & Str(Me!DefendantId) & ")",
dbOpenSnapshot)
Set rstDefCharge = db.OpenRecordset("Select * from tblDefChargesSentence
where CaseNo = '" & Replace(Me!CaseNo, "'", "''") & "' and ((DefendantId) =
" & Str(Me!DefendantId) & ")", dbOpenSnapshot)

SubTotal = 0
With rstConCons
Do Until .EOF

If rstConCons![ConConsecCaseNo] = rstDefCharge![CaseNo] Then
If rstDefCharge!ConcurrentSentence = True Then

TotalMonths = rstDefCharge!SentYrs * 12
TotalDays = rstDefCharge!SentDays / 30
If TotalMonths > rstDefCharge!SentMos Then
If TotalMonths > TotalDays Then

Sent = TotalMonths / 12
If Sent > SubTotal Then
SubTotal = Sent
End If
End If
End If
End If
End If

..MoveNext
Loop

Do Until .EOF

If rstConCons!ConConsecCaseNo = rstDefCharge!CaseNo Then
If rstDefCharge!ConsecutiveSentence = True Then

SubTotal = rstDefCharge!SentYrs + SubTotal

End If
End If

..MoveNext
Loop
End With
Me!TotalYears = SubTotal

 

[Tim Ferguson]

If concurrent sets the TotalYears to the largest number of
years. If there are also consecutive sentences it needs to add the
years to the largest concurrent sentence.

if concurrent = true then
sentencelength = DMax("sentencelength", "judgements", _
"CaseID = " & currentcasenumber)

else ' must be consecutive
sentencelength = DSum("sentencelength", "judgements", _
"CaseID = " & currentcasenumber)

end if


A word about code: you are presenting strangers with over forty lines of
code, including up to five levels of nested 'if' structures, and not a
single comment line anywhere. This stuff is, frankly, illegible to us
now and will be to you too in six months' time; never mind the poor
***ger who has to de*** it after you. Sensible indentation is mandatory,
but helpful commenting is really necessary too: aim for a minimum of
50:50 but for an ideal of 70:30 (that's comment to code, not the other
way round). For everybody's sake, not least your own.

All the best


Tim F

 

[Christine Vollberg via AccessMonster.com]

sorry about no comments, I had them in there but took them out when I posted
the code because it was so long, not even thinking that you would need them
too. I will make sure there is comments in the future.

 

[Tim Ferguson]

sorry about no comments, I had them in there but took them out when I
posted the code because it was so long, not even thinking that you
would need them too. I will make sure there is comments in the
future.

I didn't mean to sound too snitty -- must have been a late night! Just as
a general point, though, one of the things that persuades people to take
time to work through a difficult answer is the feeling that the original
poster has already taken steps to help him or herself.

One of these is how code is presented. Usually it is possible to pick out
two or five lines of code that illustrate the problem -- a very short
fragment with ReallyDescriptiveVariableNames. Not only does this process
often highlight the error and avoid the posting altogether, it also
reassures everyone that it's likely to a more "interesting" problem than
"Oh, I missed the 's' off tblEvents"...

On busy days, you have to compete with lots of other questioners for the
attention of a volunteer. You need something that will leap out as
something that someone wants to spend time out. An appropriate subject,
decent paragraphing (full marks so far) and a succinct description of the
problem will at least get the posting read.

All of which pretentious nonsense is way off what you were asking about,
of course. Did you get anywhere with your problem or are you still stuck?

All the best


Tim F