Stop MS Error message

[AccessAddict]

Hi

I have the following code attached to the Before Update event of a form which loops through all the forms controls and prompts the user for data entry or not for any required fields (any fields for the code to 'skip over' are in the If ctl.Name = etc section):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim frm As Form Dim stdResponse As Variant
Set frm = Me
Dim ctl As Control
For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
With ctl
If ctl.Name = "FstName" Then GoTo 10
If ctl.Name = "LstName" Then GoTo 10
If IsNumeric(ctl.Value) Then
If ctl.Value <= 0 Then GoTo 20
GoTo 10
End If
If ctl.Value = &quot;&quot; Or IsNull(ctl.Value) Then GoTo 20
End With
End If
10:
Next ctl
Exit Sub
20:
stdResponse = MsgBox(&quot;You have not completed the &quot; & ctl.Name & &quot;.&quot; _
& vbCr & &quot;This is a required field, do you want to complete it now?&quot; _
& vbCr & vbCr & &quot;Select YES to return to the form.&quot; & vbCr & vbCr _
& &quot;Select NO to exit the form WITHOUT SAVING the record.&quot;, vbYesNo, &quot;Missing Data&quot;)

If stdResponse = vbYes Then
ctl.SetFocus
Cancel = True
Exit Sub
Else 'vbNo
Me.Undo
Exit Sub
End If
End Sub

On closing the form, if all the required fields are completed the form closes and the record is saved as normal. If any required fields haven't been completed, the code kicks in and the message box is displayed. If the user clicks No on the message box, the form is closed and the record isn't saved. However, if the user clicks Yes, instead of going straight back to the open form at the correct field, a MS Access error message pops up - (You can't save this record at this time etc Do you want to close the database object anyway).

How can I stop that message box opening as it is very confusing for the user? I've tried Setwarnings False, ON Error Resume Next and an error trapping routine but can't get rid of it. Thanks for any help
Cheers

Alan

 

[Hasu]

Hi,

Use form_error event to trap error and hide it.


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

If DataErr = 2169 Then Response = 0

End Sub



Hope this helps...
Hasu

 

[AccessAddict]

Hi

Thanks for the reply. Your code stops the MS error message fine, but on clicking 'Yes' on the message box the form now closes instead of going back to the open form to input the missing data. Any thoughts?

Thanks for your time

Alan

 

[Hasu]

Hi,

First define from level variable: blnError
and use below code that will stop user to exit form without entering validate data. But this process will undo your last record!


Dim blnError As Boolean

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

If DataErr = 2169 Then blnError = True: Response = acDataErrContinue

End Sub

Private Sub Form_Unload(Cancel As Integer)

If blnError Then blnError = False: Cancel = True

End Sub



Hope this helps...
Hasu

 

[AccessAddict]

Hi Hasu

Thanks for the reply. Will work on it tomorrow, my eyes are rapidly cosing right now and I need sleep!!

Thanks for your time and help

Cheers

Alan