Order of Events for Sum?

Access03/WinXP

While it's not generally recommended, I have had to take a calculated sum 
from a popup form and store it in a field on a main form (there are many 
issues involved here; suffice it to say too many people have access to 
information and the queries involved in arriving at some of the totals are 
too complicated for them - they only need to query the main form's table and 
get to the total).

In the subform footer, I have an unbound text box, txtTotalPayments set to 
=Sum([PmtAmt]).  In the records of the subform is a command button which 
automatically inserts a default amount (based on an invoice amount and 
payment schedules), payment date (as current date), etc.  In the OnClick on 
this command button I have the following:

Me.PaymentDate = Date()
Me.PaymentAmount = {a function to calculate is referenced here}
Me.EnteredBy = CurrentUser()
Me.Refresh
Forms!frmSales.txtAllPayments = Me.txtTotalPayments

(txtAllPayments is a bound field)

When the main form's field txtAllPayments was not updating properly when a 
new payment record was input, I added MsgBox me.txtTotalPayments after the 
Refresh to see what total was being represented.  The msgbox displayed the 
total before the new record was added, even though I have Me.Refresh before 
the msgbox.

What do I need to do to save the record and have txtTotalPayments 
recalculate appropriately BEFORE the main form's txtAllPayments is updated?  
I've tried requerying and repainting the subform and have not had the correct 
result.

Thanks!
0
Utf
10/24/2007 4:38:02 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
797 Views

Similar Articles

[PageSpeed] 38

Pendragon,

Try it like this:

Me.PaymentDate = Date()
Me.PaymentAmount = {a function to calculate is referenced here}
Me.EnteredBy = CurrentUser()
DoCmd.RunCommand acCmdSaveRecord
Me.Recalc
Forms!frmSales!txtAllPayments = Me.txtTotalPayments

-- 
Steve Schapel, Microsoft Access MVP

Pendragon wrote:
> Access03/WinXP
> 
> While it's not generally recommended, I have had to take a calculated sum 
> from a popup form and store it in a field on a main form (there are many 
> issues involved here; suffice it to say too many people have access to 
> information and the queries involved in arriving at some of the totals are 
> too complicated for them - they only need to query the main form's table and 
> get to the total).
> 
> In the subform footer, I have an unbound text box, txtTotalPayments set to 
> =Sum([PmtAmt]).  In the records of the subform is a command button which 
> automatically inserts a default amount (based on an invoice amount and 
> payment schedules), payment date (as current date), etc.  In the OnClick on 
> this command button I have the following:
> 
> Me.PaymentDate = Date()
> Me.PaymentAmount = {a function to calculate is referenced here}
> Me.EnteredBy = CurrentUser()
> Me.Refresh
> Forms!frmSales.txtAllPayments = Me.txtTotalPayments
> 
> (txtAllPayments is a bound field)
> 
> When the main form's field txtAllPayments was not updating properly when a 
> new payment record was input, I added MsgBox me.txtTotalPayments after the 
> Refresh to see what total was being represented.  The msgbox displayed the 
> total before the new record was added, even though I have Me.Refresh before 
> the msgbox.
> 
> What do I need to do to save the record and have txtTotalPayments 
> recalculate appropriately BEFORE the main form's txtAllPayments is updated?  
> I've tried requerying and repainting the subform and have not had the correct 
> result.
> 
> Thanks!
0
Steve
10/24/2007 6:21:05 PM
F A N T A S T I C !!!!!!!!!!  I'm so happy I could cry - I've spent days 
going through so many permutations of this and that.

Bless you!

"Steve Schapel" wrote:

> Pendragon,
> 
> Try it like this:
> 
> Me.PaymentDate = Date()
> Me.PaymentAmount = {a function to calculate is referenced here}
> Me.EnteredBy = CurrentUser()
> DoCmd.RunCommand acCmdSaveRecord
> Me.Recalc
> Forms!frmSales!txtAllPayments = Me.txtTotalPayments
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
> Pendragon wrote:
> > Access03/WinXP
> > 
> > While it's not generally recommended, I have had to take a calculated sum 
> > from a popup form and store it in a field on a main form (there are many 
> > issues involved here; suffice it to say too many people have access to 
> > information and the queries involved in arriving at some of the totals are 
> > too complicated for them - they only need to query the main form's table and 
> > get to the total).
> > 
> > In the subform footer, I have an unbound text box, txtTotalPayments set to 
> > =Sum([PmtAmt]).  In the records of the subform is a command button which 
> > automatically inserts a default amount (based on an invoice amount and 
> > payment schedules), payment date (as current date), etc.  In the OnClick on 
> > this command button I have the following:
> > 
> > Me.PaymentDate = Date()
> > Me.PaymentAmount = {a function to calculate is referenced here}
> > Me.EnteredBy = CurrentUser()
> > Me.Refresh
> > Forms!frmSales.txtAllPayments = Me.txtTotalPayments
> > 
> > (txtAllPayments is a bound field)
> > 
> > When the main form's field txtAllPayments was not updating properly when a 
> > new payment record was input, I added MsgBox me.txtTotalPayments after the 
> > Refresh to see what total was being represented.  The msgbox displayed the 
> > total before the new record was added, even though I have Me.Refresh before 
> > the msgbox.
> > 
> > What do I need to do to save the record and have txtTotalPayments 
> > recalculate appropriately BEFORE the main form's txtAllPayments is updated?  
> > I've tried requerying and repainting the subform and have not had the correct 
> > result.
> > 
> > Thanks!
> 
0
Utf
10/24/2007 6:37:01 PM
Reply:

Similar Artilces:

Sum in the Footer
In the Detail section of a report, I have a TextBox Iff /Then expression that either renders the word "Off", or a calculated number. In the footer section, how can I sum the number of times that the word "Off" (the True part of the IIf /then statement) is the resultant? Thanks! You use something similar to the control source in the detail section. Change the "Off" to the number 1 and sum it. -- Duane Hookom Microsoft Access MVP "AttackIP" wrote: > In the Detail section of a report, I have a TextBox Iff /Then expression that > either ren...

Public folder1101 event viewer messages
We have Exchange 2000 and outlook 2000. We are getting public folder error messages - error occured on message (xxx) during a background cleanup on database first storage group/public folders. These are safe to ignore however I want to get rid of the messages so that the Exchange server event viewer is clean. How do I find which messages are faulty and how do I remove them from the public folder database. I have been told about message searching but do not know how to do it. thanks Kath On Wed, 5 Apr 2006 18:08:02 -0700, Kaddie <kathied@tweed.nsw.gov.au(donotspam)> wrote: >...

quantities on order by Item by store from HQ
We are currently on 1.20157 of RMS. I have 5 locations on Store operations and HQ. We need to be able to see quaintly on order by store, by item. Currently RMS lets you see the total on order for an item, but not how much is on order for each location. Is it possible to get this information? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft...

How to modify the EXPORT xml file of a purchase order...
Hi, When I view a recipt there are a option EXPORT AS XML... is there a way to modify the resulting xml file? I just need it to have one more aditional field... is that possible? Thanks If this is a one time deal just open using Notepad. If you need the additional info on all your PO's then you can modify the XML template using Notepad. These are located in: C:\Program Files\Microsoft Retail Management System\Store Operations\ReceiptTemplates Rob "Aldo" <Aldo@discussions.microsoft.com> wrote in message news:A5674420-8E79-4DFF-9721-4901735197ED@microsoft.com... >...

Setting Tab order for custom forms
In CRM 1.2, I modified the form I use to fill in a lead. Which after publishing worked fine, except that the default tab order (the next field the cursor goes to after pressing the tab key) isn't how I want it. Is there a way to set the tab order of the fields? -- Scott H There is no way to change the default tab order -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Scott H" <ScottH@discussions.microsoft.com> wrote in message news:90B03910-2811-4508-AEEC-3B5E1D0A430A@microsoft.com... > In CRM 1.2, I modified the form I use to fill in a lead. Whic...

Order of selecting unprotected cells
I was wondering if anyone could assist me with an Excel 2003 problem I am trying to solve? I have a worksheet which has majority of it's cells protected. There are only 5 cells which the user is allowed to select and enter information. The first 2 cells are directly underneath each other eg: C6 + C7. I have another 2 cells which are unprotected B8 + B9. The final cell which is unprotected is D5. The end user needs to enter information in cells C6 and C7 first, then enter information into B8 and B9 second, then finally enter information into D5. While using the enter key, cells C6 a...

Event ID 9211
I'm receiving a 9211 Event every 10 minutes on my Exchange 2003 SP1 Server. This is after trying to send an external "echo" message via X400. The message appears to go out to the external body but does not reply. This system worked fine through 5.5 and when I revert back to the old 5.5 system, the "echos" filter through. The 9211 error reads: Event Type: Warning Event Source: MSExchangeMTA Event Category: Operating System Event ID: 9211 Date: 10/12/2004 Time: 11:48:13 User: N/A Computer: HQSGAT03 Description: A sockets error 0 on a bind() call was detected. The...

CRM 4 Calendar error Event journaling invalid recipient
We have just upgraded to CRM 4 (from 3). In the web version of CRM, some of my calendar views display the error 'event journalling - invalid recipient type'. After experiementing, I have found that this happens on specific days. If I switch to view by month or week, they also fail if the range contains the day with the error. Otherwise everything works fine. I cant find any other view (eg. activity) or report that shows an event for the problem day. The server application event log contains the following type of event; Event Type: Warning Event Source: ASP.NET 2.0.50727.0 Event...

Sales order Processing and Invoicing Modules
Hello Folks, I have situation here. I am setting a sub-company from a parent company and have to move specific data( like cherry pick). While moving SOP tables, it looks like I have stumped over both the modules/series. When I open SOP , 'Sales Transaction Entry" it gives me an error message which says, "SOP and Invoicing Modules both installed. They do not share information. Transactions should be entered in one module only". How can I fix this issue or get rid of this error message ? Please help Regards, Run GP Utilities. One of the options is...

display sum from subform to mainform
I have a subform qryOrder subform under Form footer I have a txtTotal that sum the order amount field i want to display the result in main form how to accomplish it? thanks anyway Hi Revned, Try this tutorial: Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship http://www.access.qbuilt.com/html/subtotals.html Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Revned" wrote: > I have a subform qryOrder subform under > Form footer I h...

Service work order
Does anyone have a template for a service work order? More specifically, a technology work order would be great. Just something that states the date, client name, description of problem, date completed. geek Browse around in the Template Gallery. May find something? http://office.microsoft.com/en-us/templates/default.aspx Gord Dibben Excel MVP On Fri, 7 Jan 2005 06:59:03 -0800, schoolgeek63 <schoolgeek63@discussions.microsoft.com> wrote: >Does anyone have a template for a service work order? More specifically, a >technology work order would be great. Just something t...

Using Excel as a Gradebook:Calculating sum excluding blanks and ze
I am attempting to use Excel as a gradebook and I would like to total the values of a series of columns where the values are not blank and are greater than 0. Should I test each value in a range for blank or >0? If so, how would I go about doing that? I have basic programming skills based out of Java (very elementary), and would like to understand what I am doing. So if you reply, could you please explain? Hi! >I would like to total the values That means you want to add together or sum, correct? If so, the SUM function ignores blank cells and text entries, and any cells contain...

Chart automatic sum
I have a huge list of invoices in an excel table, and would like to create a chart that shows the cumulative number of invoices sent by month. What is the best way to do this? When I create a chart with the "Invoice Sent" column highlighted, that data becomes the data range, but what I actually want the data range to be is the row count of invoices sent. Also, when I try to switch the X and Y axes so that invoice sent date is on the X axis, it gives an error because there are too many values (max of 255). However, there should only be a few dozen x-axis values when I'...

Event 5895 Error: Can't clean up the following tables: Activity
I have CRM 1.2 and I have applied rollup 2, but it didn't fix this problem. Any suggestions? Thank you very much. When I do the crmdeletionservice.exe -runonce, it gives med 3 possiblities. Usage. -Install -Remove -Console But I can se in the eventviewer that it is the activity table which is the problem. My only problem is that I don't know how to use the Query Analyzer. Thanks for you answer, I'm sure thats the sollution. Br Steen Hey again I didn't know it was that simple to use the query analyzer :-) I did just as you write in your guide, and it worked perfec...

Order By not working when record source set by vba
I had a continuous form in my database which was bound to 1 query and filtered it based on combo box values. All the fields (apart from the combos) where locked and disabled to make them view only. The form also had labels for each of the columns and if you clicked it, vba code would order the form by that column's data, click another label and it would orderby that column instead. All was fine until i decided that it would be more efficent if the form used different queries for each of the criteria instead of holding all the records in memory and filtering them. That fea...

Calendar items "disappear" with Event id 8206, 8239, 8241
Dear all I am using outlook xp with windows2k, share a calendar with other staffs. "one of them will syn the calendar with the PPC". Now some of my calendar items disappear after a while. They are still there but the stat/end dates are lost I can still see them if I do an "advanced find" under my calendar items and search for all items. They appear in the list with no start or end date. And it occurs randomly. Thanks for support. KL "Kenny" <Kenny@discussions.microsoft.com> wrote: >I am using outlook xp with windows2k, share a calendar with oth...

How to find set of numbers to sum nearest to a given total but not more?
How to find a set of numbers to sum the nearest by defect to a given total but not more? I have a set of numbers (invoice amounts) with decimals and want to find a set of numbers to sum the nearest by defect to a given total but not more. How to do it? For instance, if I had the numbers 1, 3, and 10, and I wanted the set to sum a total of 13 (or nearest one, not more), I would choose 3 & 10. If I wanted a total of 14, I would choose them all, and if I wanted, say, 9, I would choose 1 & 4 as the nearest one (not 10, since 10 is more than the total 9). Thanks! ...

Mouse Events with associated labels?
Hi all- I found parts of my answer in the archives, but am wondering if my solution really is a 'good' one. I have a form with a combo box (with enter and exit event procedures) and a non-associated label (this lets me set up a On Click event for the label.) (Several pairs, actually.) When I was setting this up I considered using a command button but settled on the unassociated label as it seemed to me to be "less complicated." Now, as I am putting the form into use I find that I'd like to set up shortcut key access to the combo box. The information I found in the ar...

Reversing Byte Order
I woud like to, as quickly as I can, reverse the byte order of a 32-bit value. I'd be happy to use inline assembler if it helps and I found the BSWAP instruction, which does exactly what I need. However, it is only supported on the 486 and higher. While the vast majority of my users will be using 486 and higher, I do believe it is possible that a small number of my users could be using 386s (is this possible for Win95?). I just wondered if anyone had any other suggestions on how to do this quickly. This is what I have so far, which hasn't been optimized at all. // Reverses the lowes...

Need macro to remove duplicate rows in a wksht with same order#
I have and extracted csv file tha i need be able to write a macro that will eliminate the duplicate rows with the same customer order number. Thanks, G2 Use Advanced filtering: Pull-down: Data > Filter > Advanced Filter… Click copy to another location Click List range, clear it, and highlight your table Clear Criteria range or leave it blank Click Copy to and select an un-used area in the worksheet Click unique records only -- Gary''s Student "G2 in AUS" wrote: > I have and extracted csv file tha i need be able to write a macro that will > eliminate the ...

Sum data in workbook 1 and write result in workbook 2
Hi, I need some help with excel macro! I have two workbooks. Now I need to sum all cells with some date criteria from first workook and result must be inserted in cell in second workbook (for example in cell A1) WorkBook1 data example Column B (dates) Column C (payroll) 12.05.2005 1200 12.05.2005 5000 14.05.2005 3100 14.05.2005 8800 17.05.2005 3550 .... For example: sum all values in column C where date in column B is 12.05.2005 and put result in workbook 2 in cell A1 Thanks for help. Martin You don'...

Outlook does not remind me of events.
Hello, I've noticed that Outlook doesn't remind me of an event until its past overdue. In some cases, 1 day overdue. I've missed important meetings because of this. Does anyone out there know how to fix this problem? Someone suggested using Cleanreminders but this didn't work. Thanks. ...

Manufacturing Order Processing
The problem we were running into was a rounding difference when the sum total of the value of all components used (and adjusted out of Work In Process inventory) is divided by the number of units received and rounded up to two decimal places, and then multiplied by the number of finished goods units received to come up with the value of the finished goods in inventory. That rounding difference was showing up in the journal entry for the transaction receiving the finished goods into inventory, but we could not determine which account field needed to be defined for that part of the jou...

Trying to alter the order of my legend in a report
Please someone explain to me how I can alter the order of the items in my legend in a report. It currently goes in alphabetical order, but I would like it to be ordered by biggest piece of the pie on the chart/number. Thanks Could you not use a translation table that has two fields - the text and a number - with the number order that which you want the text. Then in your query join on the text fields and sort on the number but not display. -- Build a little, test a little. "Rich" wrote: > Please someone explain to me how I can alter the order of the items i...

Removing Customer ID & Sales Orders with posted Invoices
Hi, I have a situation, may I know how do I remove a customer record and sales order with posted invoices. Will it affect my debtors aging report in anyway? Thanks. Regards, Andrew Andrew, What exactly do you mean by 'remove'? "Illion" <anonymous@discussions.microsoft.com> wrote in message news:2b31701c46815$1b645a60$a401280a@phx.gbl... > Hi, > > I have a situation, may I know how do I remove a customer > record and sales order with posted invoices. Will it > affect my debtors aging report in anyway? Thanks. > > Regards, > Andrew Sorry...