Row calculations on Columnar Subform based from Query

  • Thread starter Thread starter Gray Sadler via AccessMonster.com
  • Start date Start date
G

Gray Sadler via AccessMonster.com

Hello everyone, Here's my problem:

I have a columnar subform, that's a child to a main Form. The subform is
based from a query that has certain criteria and the matches appear on my
subform. That all works like a charm, however here's where things get
weird. On the sub form there are 4 bound controls and one unbound control
which has an expression that calculates amounts that are in the 4 bound
objects. Here's my code:
<<code start>>
Dim total As Long
total = 0

If Not IsNull([Forms]![frmEditQsr]![subRepPay]!qtr1) Then
total = total + 1
End If
If Not IsNull([Forms]![frmEditQsr]![subRepPay]!qtr2) Then
total = total + 1
End If
If Not IsNull([Forms]![frmEditQsr]![subRepPay]!qtr3) Then
total = total + 1
End If
If Not IsNull([Forms]![frmEditQsr]![subRepPay]!qtr4) Then
total = total + 1
End If

[Forms]![frmEditQsr]![subRepPay]![qtrs] = total
<<code end>>

This code starts when a command button is clicked. After this code
executes, I get the same 'total' for every row, even though the figures are
different in each row. For example:
[should]
row [qtr1] [qtr2] [qtr3] [qtr4] [total] [ be ]
1 1 1 1 1 4 4
2 1 1 4 2
3 1 4 1
4 4 0

I know this is something so simple, but I'm drawing a blank. Any help would
be greatly appreciated!!!
 
I think you could get the effect you want by setting your [total] textbox's
ControlSource like this:
= Nz([qtr1],0) + Nz([qtr2],0) + Nz([qtr3],0) + Nz([qtr4],0)
 
Well your codes helps with another area of my database, but not in this
particular case. Here's another example of what I'm trying to do. I'm
trying to total the amount of controls are not null. My example was a
little misleading once I looked over it again. Here's another:

Row [qtr1] [qtr2] [qtr3] [qtr4] [total] [should be]
1 100 500 600 700 4 4
2 500 4 1
3 500 400 300 4 3

With the code I provided earlier, I get the total amount for each row that
is found on row one. It's like the subform doesn't want to make the
calculation for each row, but takes the total from row one and makes that
the total for all rows.
 
Yes, that's what you'll get with the programmatic approach you're
attempting.

I'd suggest that instead you think in terms of using a controlsource.
Something like this might work:
=iif(isnull([qtr1]),0,1)+ iif(isnull([qtr2]), 0, 1) + iif(isnull([qtr3]),
0, 1) + iif(isnull([qtr4]), 0, 1)

If those blanks are not actually nulls, you may have to use a different
test.

HTH
 
Back
Top