sum iif using subform fields in parent form...

[andy_dyer]

Ok,

I've managed to use all of these independently before but when I put them together I seem to be missing something...

I have a subform (Invoice) with two fields on that are relevant...

txtInvoiceAmount and chkSent

I want to be able to on my parent form have an unbound field that totals the invoice amount where chksent = true

My current code in my textbox control source is:

=Sum(IIf(me!Invoice.Form!chksent=True,me!Invoice.Form!txtinvoiceamount,0))

What is going wrong??

 

[Access Hero]

My current code in my textbox control source is:

=Sum(IIf(me!Invoice.Form!chksent=True,me!Invoice.Form!txtinvoiceamount,0))

What is going wrong??

You didn't mention the symptom. But you can't do a sum on "0". You can verify this statement by using this:

=Sum(0)

This should give you an error.

Try something more like this:

=IIf(me!Invoice.Form!chksent=True,Sum(me!Invoice.Form!txtinvoiceamount),0)

If that doesn't do it for you, post back with a lot more information about what happens when you try this.

 

[andy_dyer]

I'm getting a #Name? message in the textbox...

I'm not sure what other info you refer to may be useful...

The subform form name is frmInvoice but the name for the control is just Invoice...

I was using the 0 and the result if the iif statement was false as what i wanted to display not to be included in the sum - sorry if i got this the wrong way round...

 

[pbaldy]

"Me" is invalid outside VBA. Just tested a textbox with "=Sum(0)" and it worked fine, as expected. I would have a textbox that summed the values in the subform footer, and then refer to that textbox from the main form.

 

[Access Hero]

Of course, the experienced gentleman with an excellent taste in reading material got it right. Microsoft has a nice little tutorial on his recommended method on the Office/Access training site.

On my version of Access here (2003), I get a "#Error" message when I use "=Sum(0)". Any idea why there's a discrepancy?

 

[pbaldy]

Not sure; I have 2000 here, and it worked in both a form and a report. I wouldn't think it would work differently in 2003, but who knows? You didn't include the quotes, did you? I used this as a control source:

=Sum(0)

You would also want it in the form/report footer, not the page footer.

 

[Access Hero]

No quotes. I'll try it when I get home tonight in a different version.

Agreed on the rest.

 

[andy_dyer]

Still no joy...

I've created two textboxes in the subform footer:

1: =IIf([chksent]=True,Sum([txtinvoiceamount]),0)

2: =Sum(IIf([chksent]=True,[txtinvoiceamount],0))

Both return #Error

I'm assuming if I can't get one of these working then trying to get a textbox on the parent form to display the figures is a no go...

What's going wrong??

 

[pbaldy]

Is txtinvoiceamount a field in the data, or a control on the form? You can't sum a control, just a field:

http://support.microsoft.com/kb/207763

 

[andy_dyer]

Ahhh...

The fields involved are:

Name: txtInvoiceAmount
Control Source: Invoice Amount (NET)

Name: chkSent
Control Source:Invoice Sent?

I'm back to thinking about the best way to do this...

I've got all kinds of text boxes all over the place trying to just display the value for the single record if the chk box is checked and then another trying to total it, others trying to do it all in one go...

I have a sea of #Error....



Arrrggghhhhh!!!

It really shouldn't be this complicated....

I simply need two things a text box that totals up all the invoice amounts so we can check it matches the project value and another text box that totals up only those invoices that have been sent...

 

[andy_dyer]

Ok... I thought I was being clever...

I created queries for these two fields and added them to the query that's the control soruce for the form... this stopped the entire form opening just white space (no error)

I then took this out of the control source query and inserted dlookups into the textboxes to look up the numbers from the queries...

This appears to work fine for existing records, but as the queries are looking up data in a subform when a new record is created I get a error:

"Run-time error '2427':

You entered an expression that has no value."

When i debug it takes me to the following line in my code that refers to the subform:

If Not Me!Invoice.Form.NewRecord And Me!Invoice.Form!chkSent = False And Me!Invoice.Form!chkPaid = False Then
Me!Invoice.Form!cmdPlanned.Visible = True
Me!Invoice.Form!cmdClearPlanned.Visible = True
Me!Invoice.Form![Planned Invoice Date].Visible = True
Me!Invoice.Form!cmdActual.Visible = False
Me!Invoice.Form!cmdClearActual.Visible = False
Me!Invoice.Form![Actual Invoice Date].Visible = False
Me!Invoice.Form![invoice number].Visible = False
Me!Invoice.Form!chkPaid.Visible = False
Me!Invoice.Form![Payment Received Date].Visible = False
Me!Invoice.Form!cmdPayment.Visible = False
Me!Invoice.Form!cmdClearPayment.Visible = False
End If

Which ever way i try this I come against a brick wall...

Is there a way of not asking the query to populate my two new fields until my subform is dirty...

 

[pbaldy]

Going back to the original method, it appears you're trying to sum the textbox, rather than the field in the data. Does this work?

=Sum(IIf([Invoice Sent?]=True,[Invoice Amount (NET)],0))

By the way, the spaces and symbols in the field names are not a good idea. You will always have to remember to bracket them.

 

[andy_dyer]

Ok...

We're getting close...

txtInvoiceValue
=Sum([Invoice Amount (NET])

txtAmountInvoiced
=Sum(IIf([Invoice Sent?]=True,[Invoice Amount (NET)])

Worked!! And I amended field on the main form to look up these textboxes which are in the subform footer... BUT my main form still hangs with the error message to with the subform:

"Run-time error '2427':

You entered an expression that has no value."

I found some code to try and avoid this and have tried:

txtInvoiceValue
=IIf(Form.Recordset.RecordCount>0,Sum([Invoice Amount (NET]),0)

txtAmountInvoiced
=IIf(Form.Recordset.RecordCount>0,Sum(IIf([Invoice Sent?]=True,[Invoice Amount (NET)],0)))

But still no success and still unable to open my form to add new records without an error and then if I click End my subform is blanked out...

It feels close.... what am i missing??