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

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
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdvsaProject financeAuthor Commented:
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
PatHartmanCommented:
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
pdvsaProject financeAuthor Commented:
Pat, thank you...could you help with the code?  I am not a programmer.


thank you
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
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
ReneD100Commented:
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
pdvsaProject financeAuthor Commented:
Ok but Type is not a number...its text  It is correct as so?
Me.Type / Me.Amount > .6
0
PatHartmanCommented:
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
pdvsaProject financeAuthor Commented:
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
PatHartmanCommented:
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
pdvsaProject financeAuthor Commented:
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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
that was it.  thank you.
0
pdvsaProject financeAuthor Commented:
thank you for working with me....
0
PatHartmanCommented:
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
pdvsaProject financeAuthor Commented:
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
PatHartmanCommented:
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
pdvsaProject financeAuthor Commented:
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
PatHartmanCommented:
If you want to colorize controls on a form or report, use the Conditional Formatting tool.
0
pdvsaProject financeAuthor Commented:
made a mistake:
<I cant do something like this in excel.
meant to say I can (not cant)
0
PatHartmanCommented:
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
pdvsaProject financeAuthor Commented:
Yes, I see the conditional formatting.  thanks.  it was only an example to show I can do it in excel.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.