Stop that user! Help?

[Pennie]

Hi all, more questions from me (I'm beginning to get the bug )

If I want to put a maximum currency amount that can be entered against a set database table - i.e. no overspend - how would I set that up so that the user was not allowed to enter the data? Say I have £7,500 available. The user has to submit requests/invoices that amount to this maximum. I need my database to tell me when the maximum amount has been reached and to stop me from making any more data entries. At the moment I have a running total of the amount spent as each request gets entered. How do I check that running total against the maximum available to spend? and how do I stop the user entering more requests?

Thank youuuuuuuuuu

 

[Fizzio]

You would need to use vba to acheive this. You have made it easier on yourself by having a running total, makes the vbacode easier!
Is the £7,500 a static value or can it be changed and if it can be changed, is is different for each client?

If it is static, on the Form_Current event of your form (I presume you have the requests / invoices on a subform)

If me.NameofTotal control <= 7500 then
me.allowadditions = True
else me.allowadditions = False
end if

If the maximum spend value can change, you can do it a couple of ways
a) Include the client's maximum spend in the query populating the form (if you use a table, is the value in there?)
then in the Form_Current

If Me.NameofTotal control <= Me![NameOfMaxSpendField] then
me.allowadditions = True
else me.allowadditions = False
end if

b) Use Dlookup

If Me.NameofTotal control <= Dlookup("[NameOfMaxSpendField], "CllientTable","[ClientID] = " & Me![ClientID]) then
me.allowadditions = True
else me.allowadditions = False
end if

HTH

edit - corrected my Incorrect On_Current (Again!) to Form_Current

 

[Pennie]

Thanks again

Thanks for the code samples, am off to try 'em