Solved

Msg Box - if sum is > X then show msg box

Posted on 2014-11-03
21
144 Views
Last Modified: 2014-11-05
Experts,

I have a form with [Amount] and [Type]
If sum of [Amount] and [Type]="JBIC" is >60% of the aggregate [Amount] then show a message box "JBIC total funds cant be greater than 60% of the aggregate"

[Type] is a combo box and value list.  

thank you
0
Comment
Question by:pdvsa
  • 11
  • 9
21 Comments
 

Author Comment

by:pdvsa
ID: 40419483
If not mistaken, I think the event should be on the "After Update" of [Amount].  Possibly on the After Update of form.  Not sure which is better.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40419535
ALL edits need to be either in the BeforeUpdate event of the control or the BeforeUpdate event of the form.  When the edit is simple and relates only to the field being edited, the control's BeforeUpdate event works well since the user gets the error message immediately.  However, if you need to check for empty fields or do compound edits ( dateA > dateB), then the best place is the BeforeUpdate event of the FORM.

You need to use the BeforeUpdate event so you can cancel the update if an error is found.  The AfterUpdate event happens AFTER the bad data is saved so you can just ignore the error since it is too late to stop anything.  Think of it as closing the barn door after the horse has escaped.

This particular edit should go into the FORM's BeforeUpdate event since it involves two fields.  If you do the edit in the individual field's BeforeUpdate event, it needs to go in twice and you need to accommodate one or the other fields being null when you perform the edit.
0
 

Author Comment

by:pdvsa
ID: 40419574
Pat, thank you...could you help with the code?  I am not a programmer.


thank you
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40419645
Put this in the BeforeUpdate event of the form.

If Me.Type / Me.Amount > .6 Then
    MsgBox ""JBIC total funds cant be greater than 60% of the aggregate", vbOKOnly
    Me.Type.SetFocus
    Cancel = True
    Exit Sub
End if
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40419791
If Me.Type / Me.Amount > .6

Open in new window

And if Me.amount has a 0 or NULL you get a runtime error and the user is looking at the debug screen ;)
So you probably want to catch those scenarios first
0
 

Author Comment

by:pdvsa
ID: 40419953
Ok but Type is not a number...its text  It is correct as so?
Me.Type / Me.Amount > .6
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40420065
You only gave us two columns in your original statement.  No, you cannot divide a text field by a numeric one.  What is the purpose of the Type field and which two columns need to be used in the division calculation?  Are you saying that you need to sum the amount values for some group and then if the amount associated with type = "JBIC" is > 60% of the total, raise an error?  You'll need to specify how to identify the group and also specify if multiple instances of "JBIC" are allowed within a group.
0
 

Author Comment

by:pdvsa
ID: 40420104
Pat, yes, that is what I am after...

 if the sum of [amount] of all records that are for [type] = "JBIC"  then return the error msg if that sum is >60% of the sum of All records.

Like JBIC sum/ total sum and if >60% then raise error msg

You identify the group by type="JBIC"
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40420511
So you don't want ANY record of the group JBIC to be larger than 60% of the total amount of the group?

The problem is that it doesn't make sense to do this edit as you are doing data entry because as you add each row, the value for the group changes and therefore, the current records relationship to the group.  The first record is 100% of the group, the second may be 90%.  And what happens if you delete a row and a previously valid record now exceeds 60% of the group?

You need to think a little more about what exactly you are trying to do and when would be the best time to apply the edit.
0
 

Author Comment

by:pdvsa
ID: 40420644
Pat,

Maybe I should have said the sum of JBIC vs the aggregate.  Yes, you do have some valid points and I probably wont be deleting records so if there is a msgbox if the threshold is reached after a deletion then that is OK because it will still be a valid error.  

Maybe if you could just take your best shot at it and I will let you know if there needs to be a tweak.  

thank you very much....
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40421743
That's just it.  I can't even take a shot at it without knowing what needs to be done.  Writing code is the easiest part of the job.  Gathering requirements and understanding what needs to be done is the hard part and there is no point in writing code until you know exactly what you want to do.  I expanded the previous example a little to include something that might get the aggregate amount.  But writing code before you understand the requirements fully is the wrong thing to do.

Dim cTotal as Currency

cTotal = DSum("Amount", "YourTable", "Type = 'JBIC'")
If (cTotal + Me.txtAmount) / Me.txtAmount > .6 Then
     MsgBox ""JBIC total funds cant be greater than 60% of the aggregate", vbOKOnly
     Me.txtAmount.SetFocus
     Cancel = True
     Exit Sub
 End if 

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40422051
that was it.  thank you.
0
 

Author Closing Comment

by:pdvsa
ID: 40422053
thank you for working with me....
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40422064
Compiling isn't working and although this code executes, I don't believe it actually "works".  i.e. solves your problem.  Post back as you gain a better understanding of this code in various situations.
Good luck.
0
 

Author Comment

by:pdvsa
ID: 40422260
Pat, I did fiddle around with the code to try to make it work.  I changed the names of the Dim and added another Dim (my edits are bold).  I imagine I violated some rules but thought I could get it to work.  Maybe you see something else if after reading the below.  

Private Sub Form_BeforeUpdate(Cancel As Integer)

 Dim cTotalJBIC As Currency
 Dim cTotalNotJBIC As Currency

cTotalJBIC = DSum("Amount", "tblDisbursement_Amounts", "Type = 'JBIC'")
cTotalNotJBIC = DSum("Amount", "tblDisbursement_Amounts", "Type <> 'JBIC'")

If (cTotalJBIC + Me.txtAmount) / (cTotalNotJBIC) > 0.6 Then
     MsgBox "JBIC total funds cant be greater than 60% of the aggregate", vbOKOnly
     Me.txtAmount.SetFocus
     Cancel = True
     Exit Sub
 End If
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40422304
I didn't mean that the code itself wouldn't work.  I meant that the logic of what you are testing is flawed since logically, you can't test any value until all values have been entered.  Therefore, testing each new value as it is saved, will not necessarily produce the correct results because each new record changes the value of cTotalJBIC.  When you insert the first row, the value of cTotalJBIC is x, when you insert the second row, the value is y, when you insert the third row, the value is z, etc.  You are probably working with a populated set of data so you are not seeing the flaw in the logic.
0
 

Author Comment

by:pdvsa
ID: 40422635
Pat, thanks.  I did not mean that (code wouldnt work) though.  

You are right I just dont see how this couldnt be done. but alas I am not a programmer
I cant do something like this in excel.  Maybe you can take a peek.  

thank you...
EE---60pct.xlsx
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40424087
If you want to colorize controls on a form or report, use the Conditional Formatting tool.
0
 

Author Comment

by:pdvsa
ID: 40424205
made a mistake:
<I cant do something like this in excel.
meant to say I can (not cant)
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40424276
OK, but did you look for the conditional formatting option?

click on a control to select it.
Click on the format ribbon.
Click on the conditional formatting icon.
0
 

Author Comment

by:pdvsa
ID: 40424329
Yes, I see the conditional formatting.  thanks.  it was only an example to show I can do it in excel.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now