ACCESS FEED: RE: [MS_AccessPros] Invoice totals

Rabu, 11 Februari 2015

RE: [MS_AccessPros] Invoice totals

Hi Bill,

instead of this in your ControlSource on the mainform:

=[Forms]![f_InvoiceCreation]![q_InvoiceItems].[Form]![txtSumOfItems]

use a relative reference such as this:

= [subform_controlname].Form.txtSumOfItems

WHERE

subform_controlname is the Name property of your subform control (is this really called q_InvoiceItems?)
txtSumOfItems is the control Name containing what you want to display

Also, since you are referencing a control name as opposed to a fieldname, use dot (.) instead of bang (!)

Warm Regards,
Crystal

Learn Access -6- Create a Continuous Form in Microsoft Access
https://www.youtube.com/watch?v=norAB9QvIls&list=PL1B2705CCB40CA4CA
- includes many tips for designing forms efficiently

~ have an awesome day ~
--------------------------------------------
On Wed, 2/11/15, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Subject: RE: [MS_AccessPros] Invoice totals
To: MS_Access_Professionals@yahoogroups.com
Date: Wednesday, February 11, 2015, 10:39 AM

John

I already have a box that calculates the sum on the
continuous form. It calculated the total of the items
and qty on the continuous for and it works correctly.

When I used the expression builder to try to find the
location of the totals box on the sub form
(txtSumOfItems) I end up with this..

=[Forms]![f_InvoiceCreation]![q_InvoiceItems].[Form]![txtSumOfItems]

And when I go to form view I get … #Error

Do I have to calculate the sum in the
query   q_InvoiceItem?   .
q_InvoiceItems actually does not have txtSumOfItems in it.

I do not understand why txtSumOfItems is listed under
q_InvoiceItems.

Thank you very much for your patience.

Bill

Minnesota.

Ph: 763-754-8898

Toll Free: 877-902-8898

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com]

Sent: Wednesday, February 11, 2015 10:07 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Invoice totals

Ah, an oldie but goodie!

Bill Minnesota-

The technique is to use a Continuous form for the
subform. It can have a default view of Datasheet if
you prefer that look. In the form's footer put a text
box with a control source:

=Sum([itmQty] * [itmPrice])

Then on the outer form put a text box that has a control
source of:

=Forms![f_InvoiceCreation]![q_InvoiceItems].Form![txtSumOfItems]

=[Forms]![f_InvoiceCreation]![q_InvoiceItems].[Form]![txtSumOfItems]

=Forms!f_invoiceCreation![name of subform
control].Form![name of the above text box]

No need to refresh at all in that case!

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 11, 2015, at 4:54 PM, wrmosca@comcast.net
<mailto:wrmosca@comcast.net>
[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
> wrote:

Bill, The old Northwind (2003 version) Orders form has a
subform with a hidden total text box and the main form has a
text box that has that hidden control as its ControlSource.

If your main form is not updating it might be due to a bug
in Access 2010. Hot fix is at   <http://support.microsoft.com/kb/2899528> http://support.microsoft.com/kb/2899528

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
, <Bill.Singer@at-group.net
<mailto:Bill.Singer@at-group.net>
> wrote :

John,

I knew that. What was I thinking? I am a
Knucklehead.

Now the field works on the initial calculation when I open
to the record the first time.   I can't
seem to get it to repaint as I update the sub form.

On my subform ( sf_InvoiceItems) I have a field
called ItmQty .   I would like
the field we have been working on (IncSubTotal on the form
f_InvoiceCreation) to update each time I exit the ItmQty
field.

I attempted to put a macro in. No
luck.   I attempted some code and it bombed.

I tried this as well as a few other things.
   DoCmd.Requery
(Forms![f_InvoiceCreation]![IncSubTotal])

I am not sure if the Requery method is the correct way to do
this since the field is not based on a query.

What do you suggest to get that field to repaint?

Thanks

Bill

Minnesota

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com]

Sent: Wednesday, February 11, 2015 4:02 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>

Subject: Re: [MS_AccessPros] Invoice totals

Bill-

NEVER NEVER NEVER store the result of a calculation in a
table. If you're intending to store this in the
invoice table, the value will be wrong as soon as anything
changes in t_invoiceitems. Just re-calculate it when
you need it.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 10, 2015, at 11:31 PM, 'Bill Singer' Bill.Singer@at-group.net
<mailto:Bill.Singer@at-group.net>
[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
> wrote:

John,

This worked. See below.   Yea.

DSum("[ItmQty] *
[ItmPrice]","t_InvoiceItems","ItmIncID=[Forms]![f_InvoiceCreation]![IncIncID]")

Now that I have the field calculating I just have to figure
out how to get it to fill the proper field on the
table.

Since I put this in Control Source I do not think the value
is getting back to my field IncSubTotal.

I will see what I can do. I am thinking a hidden field
tied to the table/query which is equal to the field above.

Thanks,

Bill

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com]

Sent: Tuesday, February 10, 2015 3:35 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>

Subject: Re: [MS_AccessPros] Invoice totals

Bill-

That has to go in the Control Source. Yes, it has to
be refreshed whenever something changes in the subform -
that's why I originally recommended using the AfterUpdate
and AfterDelConfirm events of the form inside the subform.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 10, 2015, at 10:29 PM, 'Bill Singer' Bill.Singer@at-group.net
<mailto:Bill.Singer@at-group.net>
[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
> wrote:

John,

Am I correct in assuming that this will not have be
repainted each time the sub form is updated? I read
the post from Adam and you mentioned repainting. I
would rather figure out how to do it so that is just
automatically updates if the sub form is changed.

This is what I came up—

= DSum("[ItmQty] * [ItmPrice]", "t_InvoiceItems", "ItmIncID
= " & [IncIncID])

Where ItmIncID is the field on the table
t_InvoiceItems and IncIncID is the field on the form.

I tried to put it in the default value of the field on the
form. That must be wrong because it was not
accepted.   I received an error "The
expression you entered has an invalid string"

Either it does not go in Default value or I have done
something wrong.

Bill

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com]

Sent: Friday, February 6, 2015 3:17 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>

Subject: Re: [MS_AccessPros] Invoice totals

Bill-

On the outer form, put a text box named txtTotal with a
Control Source something like:

= DSum("[ItmQty] * [ItmPrice]", "t_InvoiceItems", "ItmIncID
= " & [IncIncID])

=DSum("[Qty] * [Price]", "tblInvoiceItems", "InvoiceID = "
& [InvoiceID])

In the AfterUpdate and AfterDelConfirm events of the form in
the subform control put this:

Me.Parent.txtTotal.Recalc

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 6, 2015, at 10:05 PM, 'Bill Singer' Bill.Singer@at-group.net
<mailto:Bill.Singer@at-group.net>
[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
> wrote:

I am attempting to put together an invoice. The
invoice will be the main form. The items, including
the price, will be on a continuous sub form.

I would like invoice to calculate the total and taxes
automatically. However I am having a hard time
figuring out how to get the total from a the sub form to
transfer on to the main form.

I am still putting my tables together for this but I know
this is where I will get stuck.

Maybe there is a sample database I can look at?

Thanks for steering me in the right direction.

Bill

Minnesota.

[Non-text portions of this message have been removed]

------------------------------------
Posted by: "Bill Singer" <Bill.Singer@at-group.net>
------------------------------------

------------------------------------

Yahoo Groups Links

MS_Access_Professionals-fullfeatured@yahoogroups.com