Field valiadation  Where and how to set it up on a form.

I seem to be getting problems all over the place.  I just feel that I might be looking at this from the wrong angle. Here goes

I have a table with a subform set-up on it as a continuous form.  The user can then click either the add button from the main form or double click on a row in the subform.  This brings up a popup form.  With a list of items that can be selected.  Once selected, the items are added to the subform, and the popup form is then closed.  This works pretty well.  

Once the item appears in the first field of the row, all the other fields on that row need to be typed or selected, before the user can leave the record/Subform.  Can some one explain to me where the best place is, to stick my validation code?  So that it checks all required field null values plus a few other checks before they can leave the record or the subform?

I have had the following problems with my code:-

1) Allowing me to leave the form when the record is not complete
2) Trying to check for null values on newrecord row
3) Allowing you to press the esc which is ok but then allowing me to leave the form where before I could not

I am sure that I am just doing something stupid with my code.  Here is an example of the type of code I am using:-

'This checks the quantity field to make sure it not 0
If Me.Quantity = 0 Then
    MsgBox "To remove an item double click the item. Otherwise the quantity must be 1 or more.", vbOKOnly + vbInformation, "Error"
    Cancel = True
    Me.Quantity = 1
    Me.Quantity.SetFocus
end if

OR*******************

'This checks that serial number is present if requires serial number field is set true
ElseIf IsNull(Me.SerialNumber) = True And Me.RequiresSerialNumber = True Then
    MsgBox "You must enter the serial number for the item selected.", vbOKOnly + vbInformation, "Error"
    Cancel = True
    Me.SerialNumber.SetFocus
   
OR*******************

'This checks that location is not null
ElseIf IsNull(Me.Location) = True Then
    MsgBox "You must selected the location of this item.", vbOKOnly + vbInformation, "Error"
    Cancel = True
    Me.Location.SetFocus

Start Free Trial