Check box controls subform control source formula

[Shannan Casteel via AccessMonster.com]

I have a subform for listing parts. It has fields including: ClaimID, ITEM,

NET PRICE, LIST PRICE, Quantity, Supplier, and a calculated field called Part
Total. The subform is based on a query. The Part Total field is calculated
as (=[tblParts].[NET PRICE]*[tblPartPerClaim].[Quantity]). Then there is a
hidden text box on my subform that calculates the sum of all the Part Totals
for each claim. There is another field on my main form set equal to this
field on the subform.

My question is: Can I have a check box on my main form that, if checked,
would apply a formula to use the LIST PRICE in the calculation instead of the
NET PRICE?

I tried using the IIF function in place of the above formula, but it would
only return #Name? What's wrong? The IIF function I used is as follows:

PartTotal=Quant ity * (IIF(Parent.chk CaseExtended=Ch ecked,[LIST PRICE],[NET PRICE]))

I also tried:

PartTotal=Quant ity * (IIF(frmClaim.c hkCaseExtended= Checked,[LIST PRICE],
[NET PRICE]))

frmClaim is my main form.
chkCaseExtended is my check box.
I appreciate any help.

Thanks,

Shannan
--
Message posted via http://www.accessmonster.com

[Pachydermitis]

Shannan,
I got a little confused trying to visualize the exact nature of your
setup. I hope that I understood the problem enough to at least point
you in the right direction.

Access evaluates checkboxes as either true(-1 or non 0) or false (0).
You should say Parent.chkCaseE xtended=-1. I imagine the form thinks
'Ch*ecked' in Parent.chkCaseE xtended=Ch*ecke d is a control - hence
the #name error.

Try:
PartTotal=Quant ity * IIF(frmClaim.ch kCaseExtended=-1,[LIST PRICE],
[NET PRICE])
or
PartTotal=Quant ity * IIF(frmClaim.ch kCaseExtended,[LIST PRICE],
[NET PRICE])
On the second example the =-1 is removed because in a conditional
statement, access will implicitly evaluate the value of a control as
true or false. The only thing to watch out for is to make sure that
chkCaseExtended is never null. you can do that by
PartTotal=Quant ity * IIF(nz(frmClaim .chkCaseExtende d,0),[LIST PRICE],
[NET PRICE])
HTH
Pachydermitis

[Shannan Casteel via AccessMonster.com]

Pachydermitis,

This at least returns something, but when I load my form it asks for the
Parameter Value ("frmClaim.chkC aseExtended), and if I don't enter anything,
it just uses [NET PRICE]. It's like the check box has no effect.

Thanks for the help, though,

Shannan

Pachydermitis wrote:

Shannan,
I got a little confused trying to visualize the exact nature of your
setup. I hope that I understood the problem enough to at least point
you in the right direction.

Access evaluates checkboxes as either true(-1 or non 0) or false (0).
You should say Parent.chkCaseE xtended=-1. I imagine the form thinks
'Ch*ecked' in Parent.chkCaseE xtended=Ch*ecke d is a control - hence
the #name error.

Try:
PartTotal=Quan tity * IIF(frmClaim.ch kCaseExtended=-1,[LIST PRICE],
[NET PRICE])
or
PartTotal=Quan tity * IIF(frmClaim.ch kCaseExtended,[LIST PRICE],
[NET PRICE])
On the second example the =-1 is removed because in a conditional
statement, access will implicitly evaluate the value of a control as
true or false. The only thing to watch out for is to make sure that
chkCaseExtende d is never null. you can do that by
PartTotal=Quan tity * IIF(nz(frmClaim .chkCaseExtende d,0),[LIST PRICE],
[NET PRICE])
HTH
Pachydermiti s

--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

I also tried making the PartTotal text box on the subform an unbound text box
and going to my main form and setting the After Update event of the check box
to...

Private Sub chkCaseExtended _AfterUpdate()
If chkCaseExtended = -1 Then
[subParts].[Form]![PartTotal] = ([tblPartPerClaim].[Quantity]*[tblParts].[LIST PRICE]
Else
[subParts].[Form]![PartTotal] = ([tblPartPerClaim].[Quantity]*[tblParts]
..[NET PRICE]
End If

End Sub
When I checked the check box it kept giving me an object required error, and
it left the PartTotal field in the subform blank on the form load.

Shannan Casteel wrote:

Pachydermiti s,

This at least returns something, but when I load my form it asks for the
Parameter Value ("frmClaim.chkC aseExtended), and if I don't enter anything,
it just uses [NET PRICE]. It's like the check box has no effect.

Thanks for the help, though,

Shannan

Shannan,
I got a little confused trying to visualize the exact nature of your

[quoted text clipped - 20 lines]

HTH
Pachydermit is

--
Message posted via http://www.accessmonster.com