Which event would handle the verifying of input values into text box f

[KerryMarie]

I created a main form named frmCustomRpt.
I placed a tab control over this main form.
I created another form named frmSubTabular with its record source being a table named tblSecurity.
I then created subform named subTabular whose source object is frmSubTabular.
I placed the subTabular subform on one of the pages of the tab Control.

The subform has a Default View of Datasheet. The first field of this Datasheet view is called
KERBEROS. Once the user tabs off of a newly created record of this Datasheet, the following VBA code in the BeforeInsert Event and BeforeUpate Event of the FORM named frmSubTabular ensures that the user can't leave the record without entering a value for the 1st text box field named KERBEROS.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strLowCase As String
Dim strUpperCaseLN As String
Dim strUpperCaseFN As String
If IsNull(Me.KERBEROS) Then
Me.KERBEROS.SetFocus
Else
strLowCase = StrConv(Me.KERBEROS, vbLowerCase)
Me.KERBEROS = strLowCase
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strLowCase As String
Dim strUpperCaseLN As String
Dim strUpperCaseFN As String
If IsNull(Me.KERBEROS) Then
Me.KERBEROS.SetFocus
Cancel = True
Else
strLowCase = StrConv(Me.KERBEROS, vbLowerCase)
Me.KERBEROS = strLowCase
End If
End Sub

Now I want to ensure that the user can't leave the newly created record without entering a text value for the 2nd field titled Last_Name and the 3rd field titled First_Name.

Does anyone know which event I could place the following VBA code in ?

Would the following snippets of code go into an event of the FORM or an event of the respective text box fields for Last_Name and First_Name ? Also which specific event should these snippets of code be placed in ?

If IsNull(Me.Last_Name) Then
Me.Last_Name.SetFocus
Else
strUpperCaseLN = StrConv(Me.Last_Name, vbUpperCase)
Me.Last_Name = strUpperCaseLN
End If

If IsNull(Me.First_Name) Then
Me.First_Name.SetFocus
Else
strUpperCaseFN = StrConv(Me.First_Name, vbUpperCase)
Me.First_Name = strUpperCaseFN
End If

 

[KerryMarie]

As I stated earlier, I created another form named frmSubTabular with its record source being a table named tblSecurity.

I just now realized that I had not set any of the field's Required property in the table desgn to Yes.
I changed the value to Yes for the Last_Name field to see what would happen. Now when I omit a value for the Last_Name field and tab off the record, the system generate the following message:

The field 'tblSecurity.LastName' cannot contain a NULL value because the required property for this field is set to TRUE. Enter a value into this field.

I guess I should be setting the Requried property in the table design to Yes for all required input fields.
My next question is which event would trigger this error message I received because I wanted to send the message instead of the system generating the message. The system does not put the cursor in the proper field that needs to have an input value.

 

[VBslammer]

The [tt]Form_Error()[/tt] event is where you'll need to trap input errors, which are not the same as the runtime errors in the Errors Collection. There isn't a good deal of information on the [tt]DataErr[/tt] codes, but if you trap them in the handler and display a messagebox containing the details of the DataErr object you can determine which input errors are likely to occur on your form. For example, to find out which errors are triggered when you input bad data, put this code in the form's error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  MsgBox "Error: " & DataErr & vbCrLf & _
         "Description: " & AccessError(DataErr) & vbCrLf & _
         "Control: " & Screen.ActiveControl.Name
End Sub

Once you map out all the DataErr codes pertinent to your form, you can trap them using code similar to this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  Const INPUTMASK_VIOLATION = 2279
  
  If DataErr = INPUTMASK_VIOLATION Then
    Select Case Screen.ActiveControl.Name
      Case "txtPhone"
        Beep
        MsgBox "The phone number you entered is invalid!"
      Case "txtSSN"
        Beep
        MsgBox "The SSN you entered is invalid!"
      Case "txtZip"
        Beep
        MsgBox "The ZIP code you entered is invalid!"
      Case Else
        Beep
        MsgBox "An input mask violation occurred in control " & _
            Screen.ActiveControl.Name & "!"
    End Select
    Response = acDataErrContinue
  Else
    Response = acDataErrDisplay
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

One way to get a quick reference to the error codes is to add them to a table so you can browse their descriptions. Here's a procedure that loops through the error code range and adds non-superfluous errors to a table:

Sub GetAccessErrors()
On Error GoTo ErrHandler
  Dim i As Integer
  Dim str As String
  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Dim qdf As DAO.QueryDef
  
  Set db = CurrentDb()
  
  On Error Resume Next
  Set tdf = db.TableDefs("tblAccessErrors")
  If Err = 0 Then
    On Error GoTo ErrHandler
    DoCmd.RunSQL "DELETE * FROM tblAccessErrors"
  Else
    Err.Clear
    On Error GoTo ErrHandler
    Set tdf = New DAO.TableDef
    With tdf
      .Name = "tblAccessErrors"
      .Fields.Append .CreateField("ID", dbLong)
      .Indexes.Append .CreateIndex("PrimaryKey")
      With .Indexes("PrimaryKey")
        .Primary = True
        .Unique = True
        .Fields.Append .CreateField("ID")
      End With
      .Indexes.Refresh
      .Fields.Append .CreateField("Description", dbMemo)
      .Fields.Refresh
    End With
    db.TableDefs.Append tdf
  End If
  
  For i = 0 To 11000
    str = AccessError(i)
    If Len(str) > 0 And str <> "Application-defined or object-defined error" Then
      DoCmd.RunSQL "INSERT INTO " & tdf.Name & _
        " (ID,Description) VALUES (" & i & ",'" & Replace(str, "'", "''") & "')"
    End If
  Next i
  
  On Error Resume Next
  Set qdf = db.QueryDefs("qryAccessErrors")
  If Err <> 0 Then
    Err.Clear
    Set qdf = db.CreateQueryDef("qryAccessErrors", "SELECT * FROM " & tdf.Name)
  End If
  
  On Error GoTo ErrHandler
  DoCmd.OpenQuery qdf.Name, acViewNormal

ExitHere:
  On Error Resume Next
  Set tdf = Nothing
  Set qdf = Nothing
  Set db = Nothing
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

On my system there are 1664 distinct error entries in the table.

VBSlammer

"You just have to know which screws to turn." - Professor Bob