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]
Sent: Wednesday, February 11, 2015 4:02 AM
To: 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
(Paris, France)
On Feb 10, 2015, at 11:31 PM, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <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]
Sent: Tuesday, February 10, 2015 3:35 PM
To: 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
(Paris, France)
On Feb 10, 2015, at 10:29 PM, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <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]
Sent: Friday, February 6, 2015 3:17 PM
To: 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
(Paris, France)
On Feb 6, 2015, at 10:05 PM, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <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.
Posted by: "Bill Singer" <Bill.Singer@at-group.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar