[AccessD] Error 3314 (required field) - trap in form_error

Gale,

    My sample db named Forms_CheckReqdAndDuplicates might be of interest to you. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.

    Brief description is given below. You could adapt the underlying approach suitably, for your specific needs.

Best wishes,
A.D.Tejpal
---------------

Forms_CheckReqdAndDuplicates (Sample Db)
(Description)
=====================================
1 - The sample db demonstrates incorporation of following safeguards at data entry stage.
(a) Ensuring that for a given record, mandatory fields get entered before permitting data entry in any other field.
(b) Ensuring that in case of unique composite index, if data being entered in any of the fields involved in such a combination would lead to creation of duplicates, a friendly message is provided, and the irregular entry is un-done.
2 - Mandatory Fields (Property settings in source table: Required = Yes, Allow zero length = No)
(1) Sole dependence upon form's Error event for generating a friendly message, is by itself not adequate (This event fires only when the user tries to save the record or move to another record).
(2) In order to ensure that even while staying at a given record, data entry in other fields becomes feasible only after the mandatory ones have been filled in, further programmatic support has been provided, some of the salient features being -
(a) Controls pertaining to mandatory fields are identified by setting their tag property = R (This facilitates super-check on blank/filled status of such controls).
(b) Once the form gets dirty, mandatory controls in blank state get highlighted in a special color.
(c) If any attempt is made to enter data in some other control while any of the mandatory ones is still blank, the focus moves back to the first blank mandatory control, after providing a suitable friendly message. 
(d) Two styles are demonstrated.
(i) Tabbed form (mandatory controls on first page, other controls on subsequent pages).
(ii) Datasheet form
3 - Preventing duplicates in fields comprising composite unique index or primary key.
(1) Here too, sole dependence upon form's Error event for generating a friendly message, is by itself not quite adequate, for reasons stated against 4.2 (1) above.
(2) The sample demonstrates provision of super-check in exit event of controls pertaining to composite unique index. If any entry is such as would lead to duplication of combined field value, the entry is un-done and the exit event gets cancelled, after providing suitable friendly message. The focus continues to remain on the control in question.
=====================================

  ----- Original Message ----- 
  From: Gale Perez 
  To: accessd at databaseadvisors.com 
  Sent: Saturday, May 26, 2007 04:54
  Subject: [AccessD] Error 3314 (required field) - trap in form_error


  Hi!

  I have a form with several fields whose "Required" property is Yes, and I want to trap Error 3314 (required field) in the form_error.  I would like the code to set focus on the first field that is required and null and display a custom message.  The problem is that it isn't seeing the control as Null (so it ignores If IsNull(ctl), and just displays the customary message).  I would greatly appreciate any advice.

  I am also interested in finding out if you can determine a control's
  table-level properties (e.g., required, and datatype) while in the form,
  instead of using an asterisk and looking at the label caption to do it.
  Here is the code:

  Dim ctl as Control
  Dim strCaption as String
  ......
  If DataErr = 3314 Then
       For Each ctl In Me.Controls
            ' get the caption for the field label
            strCaption = Screen.ActiveControl.Controls.Item(0).Caption
            If Left(strCaption, 1) = "*" Then          'required field
                 If IsNull(ctl) Then  'it is ignoring this, not seeing the
  field as null
                      ctl.SetFocus
                      MsgBox Chr$(34) & strCaption & Chr$(34) & " is a
  required field."
                      Response = acDataErrContinue
                      Exit sub
                  End If
            End If
       Next
  End if

  Thank you so much for any assistance,
  Gale