Control Validation

[RickRock]

I am having problems validating entry data on my order entry screen. I have an Access front end to a MS SQL Server back end. The entry is simple. I have the following controls on a sub-form:
cboProductID (a combo box for available products), txtProductName (a text box for the name of the selected ID), Quantity (a text box for quantity of the selected product), Price (price of selected product) and LineTotal (quantity times price). I want to validate that a quantity is entered. I tried to use the control validation on the Access form for the Quantity text box stating ">0" as the Validation Rule and "You must enter a quantity" as the Validation Text. It is my understanding that you couldn't tab out of the control until the entry passes the validation rule. If I hit tab or enter, the cursor jumps to the next control, even though it didn't meet the validation criteria. I can't see that I am doing anything wrong.

I have also tried the following code in the LostFocus event:

    If Me.Quantity = 0 Or IsNull(Me.Quantity) Then
        DoCmd.Beep
        MsgBox "You must enter a quantity.", vbOKOnly
        Me.Quantity.SetFocus
        GoTo Proc_Exit
    Else
        Forms![frmInvoiceOrders]!cmdSaveRec.Enabled = True
    End If

This code generates the MsgBox message, but the cursor jumps to the next control when clicking OK on the message box, even though I have the SetFocus statement in the code.
I would like to get either of these options to work.

[NeoPa]

Validation Rules determine what entries are valid. They only apply when there is an entry to check. Your answer lies elsewhere :-(

The code looks OK to me but you do use the same name for the Control as you use for the Field so that's likely to give you issues. I'd correct that first.

If you were using an Access BE you could use the ValidationRule property of the table itself, which is somewhat under used due to it being so much harder to discover than that for the Fields (& the Controls too of course). I just looked for SQL Server tables and it's there too. You may not like the idea of setting it as a table property but it's there if you do.

Otherwise, the code's just a snippet. It looks fine as far as it goes but if it still doesn't work for you then the whole module might be worth posting (Properly in [Tags] of course ;-) )

[isladogs]

Validation tests are best done in the Form_BeforeUpdate event as that always runs before saving changes and the change can be cancelled if it fails the validation test.

You could also use

If Nz(Quantity,0)=0 Then

[NeoPa]

Nice one IslaDogs. I should have included that but I went blank at the time :-(
Form_BeforeUpdate() is indeed where you can, and would probably want to, handle such an issue at the Form level.

There is a whole nother discussion about where best to handle such matters within a database but before going there you need to know the available options.

[isladogs]

Hi @NeoPa
Its very easy to overlook that event, In fact I developed in Access for several years before using it for the first time.

Failing that the next best choice is the Before_Update event of the actual control ...but there is no real advantage to doing so...unless there is additional code to be run in the same event.
In addition why use validation code in multiple control events when one event can handle it all!

[NeoPa]

IslaDogs:
In addition why use validation code in multiple control events when one event can handle it all!

That is required sometimes. It comes down to whether you'd like to catch problems immediately, and to disallow any invalid data before being allowed to move on even to the next Control, or whether you, as the developer or client, prefer to let them get all the information in before reporting on any issues. Sometimes both can be required where some validations can be done at a Control level, which can be done in isolation, where others can only be determined by comparison with multiple values within the same Form.

In this case of course, the Form_BeforeUpdate() is definitely required as it's a check that can't be adequately handled at the Control level.

[isladogs]

All true but (I'm partly playing devil's advocate here) ...
If there are several fields being updated, the control before update event will of course, only cancel the change in that one control, whereas the form before update event will reverse all change made to that record.

It could easily get very confusing if both types of event are used in the same form.
More for interest than anything else, I've attached a very basic example using before update events in both a control (Input1) and the form. Both have message boxes so its easy to see which event is which. In this case, there is no validation involved.
The control event is triggered first on leaving that field then the form event on leaving the record. Even if the control field change is confirmed and then the user changes another field before moving to another record, both changes can be reversed in the form event

BTW: The Almost Impossible DB name is not an indication that it is complicated. Its the username of someone I was assisting at the time!

[NeoPa]

IslaDogs:
If there are several fields being updated, the control before update event will of course, only cancel the change in that one control, whereas the form before update event will reverse all change made to that record.

Yes indeed. This is very much indicative of how the <ESC> key works when in a Form & Control/Field with updates. <ESC> #1 reverses the changes made at the Control/Field level if there are any in the current Control/Field, otherwise (If no changes to undo.) it will affect the Form (Record) instead. <ESC> #2, assuming the #1 reversed the Control/Field, will reverse the whole record of the Form.

I sometimes have fun trying to ensure that <ESC> is only ever used for triggering a CommandButton Control (Property Cancel=Yes.) when there are no changes to be reversed just so it doesn't interfere with expected usage within Access of the <ESC> key.