vba field validation

[ptsm]

hi friends, I am a beginner of access. I would like to get help for vba field validation rule for the following situation.

I got a form with fields-"item" and "qty issued". (item is combo box)
In my query-"qrybalance" there is a field named "stock balance".

If I enter a value in "qty issued" field greater than the value in "stock balance"
I should get a message.

Any help is highly appreciated

 

[Ranman256]

you really shouldnt name fields/text boxes with spaces inbetween, but,
validate the values in the boxs after the user enters them:

sub qty_issued_Afterupdate()
ValidateQty
end sub

sub stock balance_Afterupdate()
ValidateQty
end sub

sub ValidateQty()
if qty_issued > stock_balance then msgbox "Qty Issue > Stock Bal"
end sub

 

[sonic8]

you really shouldnt name fields/text boxes with spaces inbetween, ...

Correct. However, you could have mentioned that it is required to enclosed such fields with square brackets to deal with them anyway.
E.g.: ... [qty issued] > [stock balance] ...

 

[MajP]

I would say 95% of validation is done in the before update and not the after update. The before update allows you to cancel and correct a mistake. With the after update the value is committed to the table. The only time you would do this in the after update if it was informational or you are going to apply code to change the value.

 

[sonic8]

The before update allows you to cancel and correct a mistake. With the after update the value is committed to the table.

You should distinguish between the control's After Update event and the form's. In the control's After Update, which Ranman256 was suggesting, the data is not committed to the table yet.

 

[Pat Hartman]

the data is not committed to the table yet.

True but unless you also provide validation in the Form's BeforeUpdate event, it WILL be.

Events are not arbitrary and even though you may be able to get by if you put code in the wrong event by putting code in multiple events, you will find that your validation is more robust and has no holes if you use the Form (or in some cases - control) level BeforeUpdate event.

I use the control's BeforeUpdate event ONLY if I want the validation to happen immediately. For example, if the user has to type in a unique identifier such as SSN, I will usually validate that immediately so that the user doesn't waste any further time if that SSN already exists. I also code additional validation in the Form's BeforeUpdate event. For example, if a field is required, the only place it makes sense to check for this is the Form's BeforeUpdate event because control level events do not even fire if the control never obtains the focus. The other problem with ranman's specific example is that 50% of the time, the error will be raised when it shouldn't be. If one value depends on another, how can you compare them before they are both entered? The answer is, you can't so don't even try unless you want to write unnecessarily complicate code. Just wait until the Form's BeforeUpdate event. At that point in time BOTH values should have been entered so you would check to make sure they were entered and then compare them. That gives you the best error message without unduly annoying the user.