Disable Default Access Form Error Messages 1

[dmeyring]

Hello,

I have a Access 2003 form that I've added some error handling code to the Form Before Update event that prevents the user from leaving required fields empty. The trouble is, when the user tries to exit Access while leaving a required field empty they receive two messages:
The first = the message that I have programmed
The second = a default Access error message which is what I'm trying to avoid in the first place since it's confusing to the user

The error code is 2169 - I have the following code to perform actions when these errors occur, but am not sure of what statements to write within to get the default Access error messages to not appear, or disappear once they've triggered.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 2169
????
End Select
End Sub

Thanks!!

 

[Remou]

You would need to deal with the error, Undo erhaps, and then add a response, say:

Response=acDataErrContinue

 

[dmeyring]

Ok, so i changed this to:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 2169
Response = acDataErrContinue
End Select
End Sub

I must definitely be missing something...This allows the user to click exit, shows my error message, click ok to that, then close the form completely without allowing you to go back and fix the missing data.
Thoughts?

 

[Remou]

Please post your code.

 

[dmeyring]

This is the code for the custom error messages, it is working well: (let me know if this is not the piece of code that you're looking for)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

    nl = vbNewLine & vbNewLine

    For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
         msg = "Data Required for '" & ctl.ControlTipText & "' field!" & nl & _
               "You can't save this record until this data is provided!" & nl & _
               "Enter the data and try again . . . "
          Style = vbCritical + vbOKOnly
          Title = "Required Data..."
          MsgBox msg, Style, Title
          ctl.SetFocus
          Cancel = True
          Exit For
        End If
      End If
    Next

End Sub

 

[Remou]

I think that you may need this:

http://www.tek-tips.com/faqs.cfm?fid=2071

 

[dmeyring]

I just finished implementing that FAQ's solution, works great to close via my Close button and by no other method, but I'm still getting my custom error messge and then the Access error message after it

 

[Remou]

You should call the field testing procedure from cmdClose_Click(). This should make the Form_Error routine unnecessary.

 

[dmeyring]

Did exactly as you suggest, the only remaining issue is that now: user clicks close button, my custom error message is triggered, user clicks ok to that message, then the Access closes entirely and the incomplete entry is allowed to be recorded. It seems like there's got to be some sort of statement that would return the user to the form once they click through my message box instead of closing the whole app.

 

[Remou]

Are you sure you have got the code for the FAQ working? It should not be possible to close the form without your say so, that is, unless

AllowClose = True

 

[dmeyring]

I'm pretty sure i've got the code right in Form_Load and in Form_Unload, but there may be something wrong with how I've got it implemented in btnClose_Click. (I use btn instead of cmd, but it's consistent through all the code) I'll paste all three below:

Private Sub Form_Load()
    AllowClose = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Cancel = Not AllowClose
End Sub

Private Sub btnClose_Click()

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


    nl = vbNewLine & vbNewLine

    For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
         msg = "Data Required for '" & ctl.ControlTipText & "' field!" & nl & _
               "You can't save this record until this data is provided!" & nl & _
               "Enter the data and try again . . . "
          Style = vbCritical + vbOKOnly
          Title = "Required Data..."
          MsgBox msg, Style, Title
          ctl.SetFocus
          Cancel = True
          Exit For
        End If
      End If
    Next


On Error GoTo Err_btnClose_Click

    AllowClose = True
    
    DoCmd.Quit

Exit_btnClose_Click:
    Exit Sub

Err_btnClose_Click:
    MsgBox Err.Description
    Resume Exit_btnClose_Click
    
End Sub

 

[Remou]

Try this:

Private Sub btnClose_Click()
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

On Error GoTo Err_btnClose_Click

    nl = vbNewLine & vbNewLine
    
    AllowClose = True

    For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
         msg = "Data Required for '" & ctl.ControlTipText & "' field!" & nl & _
               "You can't save this record until this data is provided!" & nl & _
               "Enter the data and try again . . . "
          Style = vbCritical + vbOKOnly
          Title = "Required Data..."
          MsgBox msg, Style, Title
          ctl.SetFocus
          AllowClose = False
          Exit For
        Else
       End If
      End If
    Next
    
    DoCmd.Close


Exit_btnClose_Click:
    Exit Sub

Err_btnClose_Click:
    If Err.Number <> 2501 Then
        MsgBox Err.Description
        Resume Exit_btnClose_Click
    Else
        Err.Clear
    End If
End Sub

 

[dmeyring]

Success with a little modification...I pasted what you haad above, but it was giving me additional Access error msgs after my error message would display. So I tried this and it worked:

Private Sub btnClose_Click()
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

On Error GoTo Err_btnClose_Click

    nl = vbNewLine & vbNewLine
    
    AllowClose = True

    For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
         msg = "Data Required for '" & ctl.ControlTipText & "' field!" & nl & _
               "You can't save this record until this data is provided!" & nl & _
               "Enter the data and try again . . . "
          Style = vbCritical + vbOKOnly
          Title = "Required Data..."
          MsgBox msg, Style, Title
          ctl.SetFocus
          AllowClose = False
          Exit For
        Else
       End If
      End If
    Next
    
    DoCmd.Close


Exit_btnClose_Click:
    Exit Sub
[COLOR=red]
Err_btnClose_Click:
        Err.Clear
[/color]
End Sub

It seems to be working...not sure why, but I'm keeping my fingers crossed that it'll continue. Do you think that this will be ok?