How To Create a POPUP Message in Form Before update

[Mark1982]

I Create a Budget Plan For a Year. I have 3 field in my Form.
1: Annual Budget Amount : 80000
2: Total use Amount: 60000
3: Remining Budget Balance: 20000

Now what I want, if I enter the amount more the annual budget plan its should give me a popup message.

Thanks.

 

[pbaldy]

Along the lines of:

If Me.OneTextbox > Me.OtherTextbox Then
  MsgBox "It's greater!"
End If

FYI, I moved your code out of the moderated forum it was in.

 

[arnelgp]

Say your Total used amiunt textbox is named txtTotalUsed:

Private Sub txtTotalUsed_BeforeUpdate()
Cancel=(txtTotalUsed]>[txtBudget])
If Cancel Then
Msgbox "Exceeded the budget"
End if
End sub

 

[Mark1982]

I Try But Its not Working.
I try the following:


1. Private Sub TotalActualAmount_BeforeUpdate(Cancel As Integer)
If Me.TotalActualAmount.Value > Me.AnnualBudget.Value Then
MsgBox "It's greater!"
End If
End Sub

2. Say your Total used amiunt textbox is named txtTotalUsed:

Private Sub txtTotalUsed_BeforeUpdate()
Cancel=(txtTotalUsed]>[txtBudget])
If Cancel Then
Msgbox "Exceeded the budget"
End if
End sub

 

[pbaldy]

Set a breakpoint so you can see what values are contained. If they are text rather than numeric values, you'll get unexpected results.

 

[arnelgp]

Look at my sample again carefuly and see what are you missing.

 

[Mark1982]

Dear arnelgp,

Thanks A lot.

But I try the Way you Type but i am getting Error.

and One thing more in this form i have subform also.
Here its is:

Form Fields:
1: Annual Budget Amount 80000
2:Total Used:60000
3:Remining Budget:20000

SubForm Fields:
1 : Date
2 : Description
3 : Expenses Amount

So i Enter My Daily Data in SubForm Field(Expenses Amount) and it automatically subtract from the Annual Budget Amount.

now what i want if Expenses Amount Reach to Annual Budget amount.it should give me Po Pup Message . so the user knows that the budget is finish.

Please help me.

 

[arnelgp]

Private Sub TotalActualAmount_BeforeUpdate(Cancel As Integer)
Cancel=(nz(Me.TotalActualAmount, 0)+DSum("[Expenses Amount]", "[TableName]")) > Me.Parent!AnnualBudget.Value
If Cancel=True Then
MsgBox "It's greater!"
End If
End Sub


Sub the table name in subform to TableName in DSum() function.

 

[Mark1982]

Private Sub TotalActualAmount_BeforeUpdate(Cancel As Integer)
If Cancel=(nz(Me.[ActualAmount], 0)+DSum("[ActualAmount]", "[Expenses 33102 Subform]")) > Me.Parent!AnnualBudget.Value
If Cancel = True Then
MsgBox "It's greater!"
End If
End Sub


I am Receiving Error Message(Compile Error: Expected: Then or Goto)

 

[arnelgp]

Remove the extra If before Cancel.

Also add code to AfterUpdate event:

Private Sub TotalActualAmount_AfterUpdate()
If DSum("[Expenses Amount]", "[TableName]") >= Me.Parent!AnnualBudget.Value Then
MsgBox "Budget exhausted"
End If
End Sub

 

[Mark1982]

Sir Arnel,

I am Still got the Error.

Private Sub TotalActualAmount_BeforeUpdate(Cancel As Integer)
If Cancel=(nz(Me.[ActualAmount], 0)+DSum("[ActualAmount]", "[Expenses 33102 Subform]")) > Me.Parent!AnnualBudget.Value
Cancel = True Then
MsgBox "It's greater"
End If
End Sub


Can I send You screenshot or send my file so you can much understand.
Thanks a Lot.

 

[Mark1982]

My Data in Form Feilds:

Total Budget Amount [80000]
Used Budget Amount [60000]
Remaining Amount [20000]

SubForm Fields:
Date:12-07-2018
Description: electricity bill
Expenses Amount:21000

Now if you look i have Remaining Amount only 20000 and today is my expenses amount 21000 more then my Remaining Amount.
so i want to put a pop up message as a warning that show me that your budget is not enough.

 

[Mark1982]

My Data in Form Feilds:

Total Budget Amount [80000]
Used Budget Amount [60000]
Remaining Amount [20000]

SubForm Fields:
Date:12-07-2018
Description: electricity bill
Expenses Amount:21000

Now if you look i have Remaining Amount only 20000 and today is my expenses amount 21000 more then my Remaining Amount.
so i want to put a pop up message as a warning that show me that your budget is not enough.

 

[Mark1982]

Hello Every one.

Is Anybody is there is help me?

My Data in Form Feilds:

Total Budget Amount [80000]
Used Budget Amount [60000]
Remaining Amount [20000]

SubForm Fields:
Date:12-07-2018
Description: electricity bill
Expenses Amount:21000

Now if you look i have Remaining Amount only 20000 and today is my expenses amount 21000 more then my Remaining Amount.
so i want to put a pop up message as a warning that show me that your budget is not enough.

 

[arnelgp]

here is a sample of try adding some
records in the subform.

try deleting some records to the subform.

try adding new budget for different year and
add transaction to it.

 

[Pat Hartman]

Whenever you have a validation rule that references more than one control, you need to put the code in the FORM's BeforeUpdate event. If you put the code in a control's BeforeUpdate event, WHICH control? Do you put it in both? Obviously one field gets filled before the other so that method also requires other logic to suppress errors caused because one field is present but not the other. All in all, it gets very complicated for NO reason. Just use the correct event and the validation is simplified.

IF Me.[Total use Amount] & "" = "" Then
    Cancel = True
    Me.[Total use Amount].SetFocus
    Msgbox "Total use Amount is required.", vbOKOnly
    Exit Sub
End If
If Me.[Annual Budget Amount] & "" = "" Then
    Cancel = True
    Me.[Annual Budget Amount].SetFocus
    Msgbox "Annual Budget Amount is required.",vbOKOnly
    Exit Sub
End If
If Me.[Total use Amount] > Me.[Annual Budget Amount] Then
    Cancel = True
    Me.[Total use Amount].SetFocus
    Msgbox "Total use Amount may not be greater than Annual Budget Amount.",vbOKOnly
    Exit Sub
End If

FYI - Best practice is to not use embedded spaces or special characters in any object names.

 

[Mark1982]

Thanks a lot Sir Arnel,

you help me a lot. thank you so much. the file you send me its help me a lot.but sir still i cant apply it in my file.

Sir Arnel i am sending you my file can you add those code in my file and send me back.

Thank you for helping me sir arnel.

God Bless you and your family.

 

[arnelgp]

its lunch time and wont be back till 2 hours.

 

[arnelgp]

you have Normalization problem on your table.

you can make all those budgets into 1 table.
all those expenses into one table.

anyway, here is your database.

they all have the same codes (in vba).
so i suggest you browse to the codes.
(while in form design view, press Alt-F11).

goodluck.

btw, which project are you working on.
i am in jizan, we are in one of Aramco project.
material controller is my job (smp).

 

[Mark1982]

Good day Sir Arnel.

Thank you so much for helping me.. I really appreciate your assistance.

Sir Arnel, the file you sent me, there is a problem in 44101 form and subform because the calculations is not working. Can you check & fix it please.

I am currently working this project for Petromin Jeddah.