DSum and DMax procedure trouble

[Christine Vollberg via AccessMonster.com]

Hi all again, I have this report with a subform, in the subform it prints
out the number of years sentenced for each charge running either
concurrently or consecutively, if concurrently I want it to give me the
largest number for TotalYears sentenced, if consecutive I need it to give
me the sum of all years. I cannnot get this to work, I have tried for 6
straight days looking at this, starting to make me nuts. Here is my code:

Private Sub TotalYears_AfterUpdate()
If Me!ConcurrentSentence = False And Me!ConsecutiveSentence = False Then
DoCmd.CancelEvent
Else

If Me!ConcurrentSentence = True Then
Me!TotalYears = DMax("[Years]", "tblDefChargesSentence", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])


Else
If Me!ConsecutiveSentence = True Then
Me!TotalYears = DSum("[Years]", "tblDefChargesSentence", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])


End If
End If
End If
End Sub

Any help will be greatly appreciated.

Thanks in advance

 

[Steve Schapel]

Christine,

What result are you getting with this? And how is this different from
what you expect? Can you give an example?

The only thing I can see which looks wrong is that this code is on the
After Update event of the TotalYears control, and yet it is trying to
set the value of this very control. Needs to be on another event, I
think. If the form is being used to add a new record to the
tblDefChargesSentence table, you will probably also need to save the
record before trying to use a DMax or DSum function against the table data.

 

[Christine Vollberg via AccessMonster.com]

This subform is on a report, if the defendant is sentence to 2, 3, 7 years
concurrently I want it to display total years as 7, if consecutively 13.

 

[Christine Vollberg via AccessMonster.com]

sorry forgot to add that currently it gives me nothing, with no errors

 

[Steve Schapel]

Christine,

You can't have a subform on a report. You can have a subform on an
form, and a subreport on a report, so I guess it is one of these.

Did you consider my earlier comments about saving the record, and using
a different event rather than the After Update event of the TotalYears
control?

 

[Christine Vollberg via AccessMonster.com]

I have tried it on all the events and nothing works. The subform on the
report works just fine, if I just use sum[years]. the code is in the
subform not the report. Not sure what to do about this one.