?
Solved

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

Posted on 2014-11-03
21
Medium Priority
?
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 38

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

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 38

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 38

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
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 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 38

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 38

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 38

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 38

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

800 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