Textbox Validation based on a query (1 Viewer)

[Cowlers]

Hi All,

I have a textbox on a subform to allow a delivery qty to be entered (to allow me to cater for part deliveries). I have a query which totals up the values of this field (so that I have a total of all the deliveries for this record) and compares that value against another field (to check that the total deliveries I have entered do not total more than the ordered qty). I am using this query within the "Validation Rule" of the textbox. My problem is that when I enter the values for the first time this works fine because the figures are not yet part of the query and therefore the check works fine, however if I try to modify a figure I've already entered it triggers the validation each time because it tries to add the new value to the query total. Is there a way that I can avoid this problem?

Many thanks,

Dan

 

[spikepl]

Use the text box's BeforeUpdate event - much more freedom to do anything. Cancel the update if unhappy with input. Look the event up in the docs.

 

[Cowlers]

Thanks for your reply spikepl,

Because I am looking at the sum of the particular field I am entering I believe I need to capture the old value and the new input as I need to deduct the old value and then add the new value to prevent the query from taking both into account. Does this sound correct? If so is there a way to capture the old value in the before update event?

Many thanks,

Dan

 

[Cowlers]

Found the answer in the end, bit of coding from Microsoft after searching on google,

Cheers,

Dan