Determine subform total before code runs

I asked this question before, but it was in another thread.  The only person 
replying in that thread did not post in response to my follow-up question 
after several days, so I am posting again.

I have a typical Purchase Order database (Access 2003) in which the main PO 
information is in the PO record (and on the main PO form) and the line items 
are in a related table (and appear on a continuous subform).  There is more, 
such as a Products table, but it is not relavant to the question at hand.

Every PO is approved by several departments.  When the line items total for 
a single purchase order exceeds $2000 an extra level of approval is needed. 
In that case I want to show the appropriate text box for entering the 
approval; otherwise that text box will be hidden.  This is a simplified 
version of a public function I am using.  It is simplified in that there are 
more controls involved in the actual code.

Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
    Case Is < 2000
      Forms!frmPO!txtApproval.Visible = False
    Case Else
      Forms!frmPO!txtApproval.Visible = True
  End Select

txtTotal is the text box in the subform footer that contains the calculation 
=Sum([Quantity]*[UnitPrice]). I can call the function from either the main 
form's Current event or the subform's After Update event.  It works properly 
when run from the form's Current event (that is, txtApproval is either 
visible or not depending on the line items total).  However, I would like 
the function to run in the Line Items subform as soon as the total exceeds 
2000.  More to the point, probably, is that a message will be generated as 
soon as the total exceeds 2000, advising the user that the extra level of 
approval is needed.  The only user-editable controls/fields on the subform 
are the Product (selected from a combo box based on the Products table) and 
the Quantity.  I have tried saving the record as soon as the Quantity text 
box is updated, and calling the hide/unhide function in the subform's After 
Update event, but it is working with the old value in txtTotal rather than 
the updated value.

To summarize, I need the Sum([Quantity]*[UnitPrice]) value to be available 
to the function as soon as Quantity is updated, but the code seems to be 
using the previous value. 


0
BruceM
11/20/2007 8:59:15 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
794 Views

Similar Articles

[PageSpeed] 36

BruceM wrote:

>I asked this question before, but it was in another thread.  The only person 
>replying in that thread did not post in response to my follow-up question 
>after several days, so I am posting again.
>
>I have a typical Purchase Order database (Access 2003) in which the main PO 
>information is in the PO record (and on the main PO form) and the line items 
>are in a related table (and appear on a continuous subform).  There is more, 
>such as a Products table, but it is not relavant to the question at hand.
>
>Every PO is approved by several departments.  When the line items total for 
>a single purchase order exceeds $2000 an extra level of approval is needed. 
>In that case I want to show the appropriate text box for entering the 
>approval; otherwise that text box will be hidden.  This is a simplified 
>version of a public function I am using.  It is simplified in that there are 
>more controls involved in the actual code.
>
>Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
>    Case Is < 2000
>      Forms!frmPO!txtApproval.Visible = False
>    Case Else
>      Forms!frmPO!txtApproval.Visible = True
>  End Select
>
>txtTotal is the text box in the subform footer that contains the calculation 
>=Sum([Quantity]*[UnitPrice]). I can call the function from either the main 
>form's Current event or the subform's After Update event.  It works properly 
>when run from the form's Current event (that is, txtApproval is either 
>visible or not depending on the line items total).  However, I would like 
>the function to run in the Line Items subform as soon as the total exceeds 
>2000.  More to the point, probably, is that a message will be generated as 
>soon as the total exceeds 2000, advising the user that the extra level of 
>approval is needed.  The only user-editable controls/fields on the subform 
>are the Product (selected from a combo box based on the Products table) and 
>the Quantity.  I have tried saving the record as soon as the Quantity text 
>box is updated, and calling the hide/unhide function in the subform's After 
>Update event, but it is working with the old value in txtTotal rather than 
>the updated value.
>
>To summarize, I need the Sum([Quantity]*[UnitPrice]) value to be available 
>to the function as soon as Quantity is updated, but the code seems to be 
>using the previous value. 


Right, that's a common question.  The "problem" is that text
box expression calculations are done in an asynchronous task
the runs at a lower priority than most other tasks,
especially VBA code execution.

In other words, if you want to use the result of a text box
calculation in a VBA procedure, then the calculation must
also be done in the VBA procedure.

Efficiency aside, this is not too difficult.  Just save the
changed data back to the table and use DSum to get the new
total:

Me.Dirty = False		'save record
newtotal = DSum("Quantity*UnitPrice", "tblPOdetails", _
					"PoID=" & Me.PoID)
Parent.txtApproval.Visible = (newtotal >= 2000)

-- 
Marsh
MVP [MS Access]
0
Marshall
11/21/2007 9:22:47 PM
I've been away for the past few days, and I forgot to flag this message 
before I left, so I did not discover your reply until just now.  Thanks for 
the tip.  It works.  I had tried DSum, but with a misguided approach, so it 
did not work (I was multiplying the DSum of Quantity by the DSum of Unit 
Price, which of course is mathematically untenable).  Thanks for pointing me 
in the right direction.

"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:vp79k3l0edvjuk40kb6f7tjaqnnsu573kl@4ax.com...
> BruceM wrote:
>
>>I asked this question before, but it was in another thread.  The only 
>>person
>>replying in that thread did not post in response to my follow-up question
>>after several days, so I am posting again.
>>
>>I have a typical Purchase Order database (Access 2003) in which the main 
>>PO
>>information is in the PO record (and on the main PO form) and the line 
>>items
>>are in a related table (and appear on a continuous subform).  There is 
>>more,
>>such as a Products table, but it is not relavant to the question at hand.
>>
>>Every PO is approved by several departments.  When the line items total 
>>for
>>a single purchase order exceeds $2000 an extra level of approval is 
>>needed.
>>In that case I want to show the appropriate text box for entering the
>>approval; otherwise that text box will be hidden.  This is a simplified
>>version of a public function I am using.  It is simplified in that there 
>>are
>>more controls involved in the actual code.
>>
>>Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
>>    Case Is < 2000
>>      Forms!frmPO!txtApproval.Visible = False
>>    Case Else
>>      Forms!frmPO!txtApproval.Visible = True
>>  End Select
>>
>>txtTotal is the text box in the subform footer that contains the 
>>calculation
>>=Sum([Quantity]*[UnitPrice]). I can call the function from either the main
>>form's Current event or the subform's After Update event.  It works 
>>properly
>>when run from the form's Current event (that is, txtApproval is either
>>visible or not depending on the line items total).  However, I would like
>>the function to run in the Line Items subform as soon as the total exceeds
>>2000.  More to the point, probably, is that a message will be generated as
>>soon as the total exceeds 2000, advising the user that the extra level of
>>approval is needed.  The only user-editable controls/fields on the subform
>>are the Product (selected from a combo box based on the Products table) 
>>and
>>the Quantity.  I have tried saving the record as soon as the Quantity text
>>box is updated, and calling the hide/unhide function in the subform's 
>>After
>>Update event, but it is working with the old value in txtTotal rather than
>>the updated value.
>>
>>To summarize, I need the Sum([Quantity]*[UnitPrice]) value to be available
>>to the function as soon as Quantity is updated, but the code seems to be
>>using the previous value.
>
>
> Right, that's a common question.  The "problem" is that text
> box expression calculations are done in an asynchronous task
> the runs at a lower priority than most other tasks,
> especially VBA code execution.
>
> In other words, if you want to use the result of a text box
> calculation in a VBA procedure, then the calculation must
> also be done in the VBA procedure.
>
> Efficiency aside, this is not too difficult.  Just save the
> changed data back to the table and use DSum to get the new
> total:
>
> Me.Dirty = False 'save record
> newtotal = DSum("Quantity*UnitPrice", "tblPOdetails", _
> "PoID=" & Me.PoID)
> Parent.txtApproval.Visible = (newtotal >= 2000)
>
> -- 
> Marsh
> MVP [MS Access] 


0
BruceM
11/26/2007 9:03:24 PM
Reply:

Similar Artilces:

Error Running Inventory Site Combiner 7.5
Hello I am trying to run the Inventory Site Combiner in Great Plains Version 7.5 SP5. I am receiving the following error [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKPOP40800'. Cannot insert duplicate key in object 'POP40800'. I checked the ODBC connection setup and is correct. Any ideas? Thanks, Brian ...

rent received/balance owed/running balance spreadsheet
I am looking for a spreadsheet?? that shows me the rent owed then paid by the renter and balance if any per month, but do not know how to build one! A very simple balance sheet assuming a monthly rent: A1:D1 column headings: A1: Date B1: Rent C1: Payments D1: Under/Over Paid Then in cell A3 enter the formula: =IF(A2="","",IF(DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))<TODAY(),DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)),"")) and copy down as far as you need. All cells should remain with a blank display until a date (more then a month old) is entered in A2 then automaticall...

Erroring Openeing Subform
Hi, I have a mainform / subform which when the user double clicks on the subform, it opens a pop up form which displays the record slected in the subform so the user can edit data. I use the following to open the pop up form: DoCmd.OpenForm "frm_BkpExec_Ntepad", , , "id = " & me!id This works fine, except if there are no records in the subform, and the user still double clicks in that window, then an error gets returned. How can I have this so that when the user double clicks in this window and there are no records in the subform, the double click is just ignor...

sum calculation in subform based off of a query
I have a Main form that has date at the link to the subform. each record is a given DAY then the cleaning jobs are scheduled in the subform. Each cleaning job has an est. time. In the subform many people are scheduled to do many jobs. and same person is usually scheduled to do many jobs. Worked hard and this is the way the form subform - we want layed out. I would like to get a running total as each job is scheduled in the subform - sum of est. time by person(clockid). Have the subform sorted by person so they are all grouped together- tried putting sum of est time in footer. Many ...

Executing code in the background
Is there workaround or atleast a future possibility in Microsoft CRM of executing some code in the background without the opening of a window? ie I have a requirement that I need to do some background processing when a button is clicked and the user does not need a message.Since I link the button to a URL (an aspx page) it opens a window, executes some code and closes the window.The end result is an ugly but quick flashing of the screen. Thanks Vidya You will actually be able to invoke script directly on the buttons you add via ISV.config in the next version without having to pop a window...

Conditional format to determine if last character in a cell is a number or a letter
I have a spreadsheet where I enter weights. The weights can be any whole number 1-99999. I would like to be able to flag suspicious weights using conditional formatting by putting a letter as the last character, such as 123a, or 57b, 4471c, etc.What conditional formatting formula can I use to accomplish this? Thanks, Tonso Assuming your weights are in column A, starting with A2, highlight the cells in that column with A2 as the active cell, and use this formula in the CF dialogue box: =3DAND(CODE(UPPER(RIGHT(A2)))>=3D65,CODE(UPPER(RIGHT(A2)))<=3D90) Then click on the Format button a...

Smarlist Totals
GP V. 10 - when in a smarlist that has columns with numbers I used to have a section on the bottom of the GoTo window called Smarlist Totals - it would open a seperate window that showed totals for any column with a number in it. All of a sudden it is no longer there and I am not sure how to get it back. I am logged in as a Poweruser. -- Thanks Knavas I don't recall this ever being a functionality available in SmartList or SmartList Builder, but hey, I may be wrong. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximu...

Help with cell totals
Hi all, My problem is that I have two rows, one contains the manufacturers name, and the other the amount that manufacturer supplies. What I need to do is get the sum of the amount supplies for each manufacturer. Example: Company 1 8 Company 2 5 Company 2 7 Company 1 2 Totals: Company 1 10 Company 2 12 -- blackie ------------------------------------------------------------------------ blackie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15223 View this thread: http://www.excelforum.com/showthread.php?thre...

Total daily e-mail count?
This might seem like an odd question, but my CIO is asking for a total e-mail count for messages both sent and/or received by our organization on any given day. I'm currently using Exchange Server 2003. Can this information be found within System Manager, or will a third party utility be needed to accumulate such information? Thanks. Perfmon will give you some basic metrics. Otherwise have a look at MessageStats from http://quest.com/messagestats. Fully-functional 30-day eval on the site. -- David Sengupta M.T.S., B.Sc., MVP, MCSE, MCSE 2000, CCA Ottawa, Canada Exchange Rep...

how 2 create and run a macro
i want to try record a new macro. can you teach me the way to create and run it. i am facing the problem Hi a good introduction to macros can be found at: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Frank Kabel Frankfurt, Germany Lee Sok Har wrote: > i want to try record a new macro. can you teach me the way to create > and run it. > i am facing the problem ...

Will my Office OS X updated to 10.1.9 run on my new Intel nased Mac
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have Office for Mac OS X, uppdated to 10.1.9., on my old 10.3.9 G4. I am taking delivery of an Intel based Macbook Pro running 10.5 Will my old Office run on Leopard? If not, is the Home/Student Version equivalent, or do I have to buy the much more expensive Office 2008? Can you get rebates? You've actually raised several issues here, so see the in-line responses: On 6/28/09 12:27 PM, in article 59b773f8.-1@webcrossing.caR9absDaxw, "Sparkswj72@aol.com@officeformac.com" <Sparkswj72@aol.com@officeform...

Ability to create formula to calculate benefit or deduction code
Would like to use a benefit or deduction based on a formula to calculate the amount instead of choosing only a flat amount or % as it is currently set up in GP version 9. ---------------- 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 Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Commun...

Why does code Loop
When I click on a command button to show a report I have a break point on the command_click. Upon clicking the command button it opens a report. DoCmd.OpenReport stDocName, acPreview As I step through the code I noticed that as I come to the end sub I expect the code to stop. But It starts back at the report_activate event and loops through the code again then opens the form. Thanks in advance for you help If I had to take a guess, opening a report causes more than one event to fire, and one of those events calls another... Does this sound like what's happening?...

Why doesn't the code create a column stacked chart?
Hi again...im sorry to ask for help so much, but charts vba is tricky ... I try to create a column stacked chart of three bars containing each three different values, much like Andy Pope's http://andypope.info/charts/StackColTotal.htm The CHT_21QSBA_S, CHT_21QSBA_B and CHT_21QSBA_A ranges are each three different cells (name CHT_21QSBA_S = A1,B5,Q3) and I thought this together with stating charttype=xlcolumnstacked should create a stacked chart - but it make 9 bars instead... Why? .... Set cht = ActiveSheet.ChartObjects("R_Q_" & Sheet1.Range("RAPP_TILLF").Value ...

need to total occurances of Long distance Phone Number
I have created a filter, and I need to determine if a call was a long distance phone number, the column name is ToPhoneNumber. If the phone number starts with a 1 and has more than 5 digits its a long distance phone number. I need to exclude 800 and 877 non toll calls in my calculation. Once I am able to perform the above, I will then be able to calculate the duration of the long distance phone calls. Hi try using a helper column for filtering this. enter the following formula (if col. A stores your phone numbers): =IF(AND(LEN(A1)>5,LEFT(A1,1)="1"),"Long Distance&quo...

multi criteria code
hello every one I have this code from Northwind sample. Private Sub ReviewProducts_Click() ' This code created in part by Command Button Wizard. On Error GoTo Err_ReviewProducts_Click Dim strMsg As String, strTitle As String Dim intStyle As Integer Dim strDocName As String, strLinkCriteria As String ' If CompanyName control is blank, display a message. If IsNull(Me![CompanyName]) Then strMsg = "Move to the supplier record whose products you want to see, then press the Review Products button again." intStyle = vbOKOnly strTitle ...

xsd.exe and code generation
I have three xml schemas, one schema contains types that the other two referance. When generate code for each of the schemas, all three cs files contain the types that are part of the common schema. Currently I am going through the auto generated code and removing the common types, with the help of the compiler since they are all in the same namespace. Aside from listing all the types I want generated in each of the other two schemas, as I may add more in the future and don't want to forget to add to the list, is there a way to get XSD to not generate the common types for the other two ...

Can someone tell me what is wrong with this code?
With Sheets("Sheet1") .Cells.EntireColumn.AutoFit .Cells.Select .Selection.Copy .Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With You have a bad line break: use .Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ or .Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks _ "Ant" wrote: > With Sheets("Sheet1") > .Cells.EntireColumn.AutoFit > .Cells.Select > .Selection.Copy > .Sele...

There must be a better way to code this
I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 <> 0 Then 'error 1 check If error2 <> 0 Then 'error 1 & 2 found MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Else 'error 1 only found MsgBox error1 & &qu...

error code 80248009
I have a 32-based system and get this error when looking for updates. I followed all the instructions posted in thread dated 1/22/10 regarding this error (running multiple scans), except for the readiness tool as it won't work on my system. I still can't get updates and can't find anything on Microsofts website regarding this error. I searched for a readiness tool for 32 bit systems, but couldn't find anything. Any other suggestions? 0x80248009 2145091575 WU E DS MISSINGREF The data store is in an invalid state because data that should be present is missing....

totals do not display unless user clicks on text box
I am relatively new to access. I am using access 2007 to create a report that contains subtotals. When the report is displayed on the screen the subtotals are blank. When the user clicks on the subtotal text box then the value appears. The user has to click on each subtotal in order to make the value appear. Has anyone experience this problem? Is there a workaround or solution? Thanks Normally a sub-total in a report is achieved by means of an unbound text box in a group footer with a ControlSource property which is an expression summing a set of values from the detail sect...

can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

If, say, =SUM(I2:I10) totals "0", how to change formula to put a "0" in I11?
I know this is going to be simple but everything I've not got anything to work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0" in I11 if there aren't any values anywhere in I2 to I10? Thanks. :oD If there are no values to sum the formula should already be returning 0. What result are you getting? Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:OV$7dJLnHHA.3704@TK2MSFTNGP02.phx.gbl... >I know this is going to be simple but everything I've not got anything to >work. How can we change f...

VBA coding to retrieve data from Access to Excel :S
Hey everyone, This is what i have so far. I have an excel workbook with 3 worksheets, SETUP, DATA and DISPLAY. I have been using a pre-coded program to bring data from a database where the program using the SETUP sheet from the excel workbook as its critaria and then populates the DATA sheet. I then can use the DATA sheet to display data in the DISPLAY sheet in which ever way i want. Complication is that this pre-coded program is limited to the amount of tables it can hit at a time. Basically its programmed to read up to 3 tables. I now need data from 5 tables all together. The first t...

Is RAID1 good enough for a local office database/file server (running Windows 2008 Server)? Or RAID5? Options
Hi Our IT suppliers are telling us that hard disks are now so reliable, that RAID1 good enough for a local office database/file server (running Windows 2008 Server)? Or should we go for RAID5 ? All feedback gratefully appreciated. With thanks Ship Shiperton Henethe Hi Shiperton, RAID 1 is at least equally reilable as RAID 5. Both configurations tolerate losing one disk. However, if you calculate statistically, chances are bigger that one drive will fail out of three (RAID 5) than one out of two (RAID 1). The same is statistically true that two drives will fai...