Retrict User to enter data if Dsum exceeds total

[blues763]

I need to be able to restrict users enter a value in the text box (on Form B) callled "FTE Assigned" if Dsum of a field called "FTE Allocated" in another form A is less than what is going to be sum of FTE Assigned after the value is entered.
Both these forms are used by users to enter data in the 2 seperate tables which are linked together through a join.
Master table - having FTE Allocated values and secondry table having FTE Assigned values.

bassically
a message box would do if Dsum (of FTE used) is coming out higher than FTE allocated.

Any help would be greatly appreciated

 

[llkhoutx]

Check the value for validity on the control's "BeforeUpdate" event. If it's invalid set Cancel=True and possibly generate a message to user.

 

[blues763]

Hi,

Thanks for replying on this. I'm still having dificulties to work this out. Please see the attachment of a screen shot of my form.

WTE Appointed is where I need to update the value that gets stored in table (tblStage2)

At the bottom of the form there are 2 text boxes:-
Total_WTE_VA is my allocated WTE value that comes from another table.
Total_WTE_S2 is where I have Dsummed the WTE from tblStage2

In the vba Code before update event of the WTE Appointed text box I have the following code:-

if DSum("[WTE Appointed]","[tblStage2]","[Job Reference Number] = '" & [Forms]![frmStage2]![JobRef_S2] & "'") > WTE then
msgbox("WTE value Exceeded in tblStage2")
me.undo

cancel = true

End if

End Sub



Above is not working.
Fairly new to the access VBA
Please help?

 

[JHB]

If [Job Reference Number] is a number fieldtype in table [tblStage2] then remove the '.

DSum("[WTE Appointed]","[tblStage2]","[Job Reference Number] = [B][COLOR=Red]'[/COLOR][/B]" & [Forms]![frmStage2]![JobRef_S2] & "[COLOR=Red][B]'[/B][/COLOR]")
When number:
DSum("[WTE Appointed]","[tblStage2]","[Job Reference Number] = " & [Forms]![frmStage2]![JobRef_S2] & "")

 

[blues763]

Its a text field.
On the form my Dsum is getting calculated fine. Its just when user update a new record on in tblStage2 via frmStage2 data entry form. I don't want users to proceed when they enter a WTE value that results in more WTE than the Dsum. This mean I would also require my Dsum to get refreshed after the new WTE value being entered.

So in essence:

We have 3 apples kept in bucket.
1 apple is given to Tom and 1 to Jerry
when I try to give 2 more apples to the 3rd person I should get a message saying bucket has only got 1 apple left.