Calculation and NOt to exceed current balance in Access

[Datament]

I have three textboxes txt_BegingBal, txt_Pruchase and txtEndBal in a form f_Cash. What code can i use to make sure that purchasers online do not exceed the Begining Balance during a Purchase?

What i currently have is [txt_BeginBal]-[txt_Purchase]. It allows overage. I need to make sure that the current amount in txt_BeginBal is not exceeded during the purchase.

Many Thanks in advance for your asistance and suggestions.

[davegri]

If I understand correctly, then it would seem that

if txt_purchase > txtBeginBal

would do the trick.

[madpiet]

The easiest way might be to declare a variable to hold the result of [txt_BeginBal]-[txt_Purchase] and if that's negative, then you reject the insert. (So you'd check/put the code in the BeforeInsert event of the form).

something like

dim Difference as Currency

Difference = me.[txt_BeginBal]-me.[txt_Purchase]

if Difference < 0 then
Cancel = True
Msgbox "Overdrawing account"
end if

[Datament]

Thanks for the quick response.
I tried that initially, but I need the txtEndBal to be able to calculate the difference between txt_BeginBal and txt_Purchase, and at the same time flag a message if txt_Purchase is over the limit.

[madpiet]

Flag a message where/when? When you insert the record?

[CJ_London]

would think the 'flag' would be raised if txtEndBal<0

so perhaps something like

if txtEndBal<0 then

    msgbox "Overspent"
    txt_Purchase=0

end if

[señor_sriracha]

Use a validation rule on the txt_Pruchase field.

<=[txt_BegingBal]