Capturing an Error generated by a forms table - req. field 2

[sellert]

I have a database that has several forms. These forms are of course tied to tables that have required fields. I am trying to capture the Error number 3314 which is generated when a new record is added and a field on the form is left blank that is tied to a required field in the table.

I have tried Before/After insert, Before/After Update, and On Error. My code is:

If Err.Number = 3314 Then
errSource = Err.Source
Select Case errSource
Case Is = "txt_MRRNo"
errorSource = "MRR Number"
criticalErrFlag = True
Case Is = "cmb_InspStatus"
errorSource = "Inspection Status"
criticalErrFlag = True
Case Is = "txt_CurrentAssignedTo"
errorSource = "Assigned To - Current"
criticalErrFlag = True
Case Is = "txt_AssignedDate" Or "txt_AssignedTime"
errorSource = "Assigned Date / Time"
criticalErrFlag = True
Case Is = "cmbMtrlType"
errorSource = "Material Type"
Case Is = "txt_BeginInspDate" Or "txt_BeginInspTime"
errorSource = "Begin Date / Time"
End Select
If criticalErrFlag = True Then
MsgBox errorSource & " is a required field" & Chr(13) & Chr(13) & _
"This field should have auto populated when you assigned/reassigned" & Chr(13) & _
"this MRR. This means that a system bug exists. An email will be generated" & Chr(13) & _
"and sent to the developer of this system.", vbCritical, "MISSING SYS GEN REQUIRED DATA"

'........................
'Generate Email Message
',,,,,,,,,,,,,,,,,,,,,,,,,
DoCmd.SendObject acSendNoObject, , , "sjellert@Companyname.com", , , "MRR Status Auto fill Error", "Main Data form - QC Information Tab" & Chr(13) & _
"Field: " & errorSource & Chr(13) & _
"MRR Number: " & Parent!cmb_MRRNo & Chr(13) & _
"User: " & mod_MS_Security.LoggedIn_User, False
Me.Undo
Exit Sub
ElseIf criticalErrFlag = False Then
MsgBox errorSource & " is a required field" & Chr(13) & Chr(13) & _
"You must enter this data prior to moving on.", vbCritical, "MISSING REQUIRED DATA"
Me.[errSource].SetFocus
Exit Sub
End If
End If

 

[VBslammer]

I use a custom class to validate input, but you can get the basic idea from this example. You have to check the DataErr argument passed in to the Form_Error() event - not the Err.Number:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  Dim ans As VbMsgBoxResult
  Dim ctlProps As ControlProperties
  Dim msgProps As MsgBoxProperties
  Dim oValidator As New clsInputValidator
  
  If DataErr = 3316 Then
  
    If oValidator.IsTextInputViolation(Me, ctlProps) Then
      
      oValidator.FormatInputErrorMessage ctlProps, msgProps
      ans = MsgBox(msgProps.PromptText, msgProps.Style, msgProps.Title)
      
      [green]'user may either try to correct their mistake, or cancel this
      'record altogether and start over/move focus elsewhere.[/green]
      If ans = vbRetry Then
        Me.Controls(ctlProps.Name).Undo
        Response = acDataErrContinue
      Else
        Me.Undo
        Response = acDataErrContinue
      End If
    Else
      Response = acDataErrDisplay
    End If
  End If
End Sub

VBSlammer

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

 

[sellert]

VBSlammer,
Thanks for your help, I knew it had to be something simple like that. Could you elaborate on creating your own class for validation? I have never tried that before and was wondering if it is more efficient or what the advantages are.

 

[VBslammer]

I get tired of typing the same code over and over so I try to encapsulate the repetitive code in class modules. Validation can be very time consuming and specific to each form, so it saves me time if I have a class that can perform the common operations for me. If I make a change inside the class, it cascades to every form that uses it as well.

My clsInputValidator Class receives a reference to the current form, and a user-defined type called ControlProperties that looks like this:

Public Type ControlProperties
  Name As String
  DisplayName As String
  Value As String
  DataType As DAO.DataTypeEnum
  ValidationRule As String
  ValidationText As String
End Type

The class then handles the basic validation by populating the udt with properties of the "frm.ActiveControl" including its validation text and validation rules. Based on the rules, it can determine if the input is valid or not using the [tt]Eval()[/tt] function. It will also populate another udt for use with a messagebox:

Public Type MsgBoxProperties
  PromptText As String
  Style As VbMsgBoxStyle
  Title As String
End Type

Here's what the class method looks like:

Public Sub FormatInputErrorMessage(ByRef ctlProps As ControlProperties, _
                                   ByRef msgProps As MsgBoxProperties)
On Error GoTo ErrHandler
  Dim lbl1 As String * 14
  Dim lbl2 As String * 14
  Dim lbl3 As String * 14
  Dim lbl4 As String * 14
  
  lbl1 = " Column Name: "
  lbl2 = " Rule Broken: "
  lbl3 = " Description: "
  lbl4 = " Your Entry : "
  
  msgProps.PromptText = "There was a problem with the entry you made:" & vbCrLf & vbCrLf & _
              lbl1 & ctlProps.DisplayName & vbCrLf & _
              lbl2 & ctlProps.ValidationRule & vbCrLf & _
              lbl3 & ctlProps.ValidationText & vbCrLf & _
              lbl4 & ctlProps.Value & vbCrLf & vbCrLf & _
             "What now? Choose an option below:" & vbCrLf & vbCrLf & _
             "1. Click RETRY to continue editing the " & ctlProps.DisplayName & " field." & _
             vbCrLf & vbCrLf & _
             "2. Click CANCEL to undo the current record."
             
  msgProps.Style = vbInformation + vbRetryCancel + vbDefaultButton1
  msgProps.Title = "Attention"

ExitHere:
  Exit Sub
ErrHandler:
  Call LogError("clsInputValidator", "FormatInputErrorMessage()", Err, Err.Description)
  Resume ExitHere
End Sub

If I had to type all that in each form I wanted to perform validation on, I'd have carpal tunnel syndrome! Classes make your code look cleaner too, but the real advantage of using class modules, especially when your code base grows to tens of thousands of lines of code, is that they can be released from memory after you're done with them. This is not so for code in standard modules - they stay resident until Access shuts down.

VBSlammer

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

 

[sellert]

Thanks VBSlammer,
This information has been very helpful. I will use it in my apps to make them more efficient.

 

[sellert]

VBSlammer,
I wanted to try and use what you gave me and I get an
"Automation Type not supported in Visual Basic" error. I am using access 97.

Public Type ControlProperties
Name As String
DisplayName As String
Value As String
DataType As DAO.DataTypeEnum ** this line causes error
ValidationRule As String
ValidationText As String
End Type

 

[VBslammer]

Unfortunately, Access didn't support user-defined enumerations until version 2000. You can still substitute the enumerated variables with Integer data types, and all you lose is the intellisense help while you're coding:

Public Type ControlProperties
  Name As String
  DisplayName As String
  Value As String
  DataType As Integer
  ValidationRule As String
  ValidationText As String
End Type

Private mctlProps as ControlProperties

Sub Test()
  If mctlProps.DataType = DAO.DataTypeEnum.dbText Then
    [green]'do stuff[/green]
  End If
End Sub

You'll run into the same problem with the other udt:

Public Type MsgBoxProperties
  PromptText As String
  Style As Integer
  Title As String
End Type

Private mMsgProps As MsgBoxProperties

Sub Test()
  If mMsgProps.Style = (vbMsgBoxStyle.vbInformation Or vbMsgBoxStyle.vbOKOnly)
    [green]'do stuff[/green]
  End If
End Sub

Any chance of migrating to Access 2000? There was a huge improvement between 97 and 2000. Definitely a worthwhile upgrade.

VBSlammer

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

 

[willockman]

VBSlammer, I've written my own code for "IsTextInputViolation" but would be very interested to read your code for the Class Module function "IsTextInputViolation" and for "LogError".

So far I haven't been able to trap the code from the frm.activecontrol as the next form control is the active control when the error is trapped. With thanks.

 

[PHV]

Have you tried to play with Screen.PreviousControl ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willockman]

PHV - Many thanks for your help, which I have incorporated into another part of the same form. Unfortunately, Screen.PreviousControl does not fit in with VBSlammer's Class Module code for "IsTextInputViolation".