Record Validation Strategy

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I'm having difficulty with what would seem to be a routine problem, so I'm
hoping someone can help.

I load a 2nd form as a modal/popup from my primary form. The Onload event
initializes a couple of fields. I would like to provide an exit button,
which I use consistently throughout the application. In all other cases, it
merely exits the form via DoCmd.Close.

In this instance, however, I wish to provide record-level error checking to
ensure all fields are non-null. If a Null field is found, I give the user
the option of returning to edit it, or Cancel the record.

If I put the record-checking code in the BeforeUpdate event procedure, I
have no way of cancelling the DoCmd.Close command in the OnClick event of the
command button. As suggested, I moved this code to the Unload event, so that
I could Cancel, but I get an unwanted "Close Action cancelled" message.

I also tried changing the code in the OnClick event to attempting to save
the record, and I got a "No current record" message.

Can anyone help? Thank you.
Sprinks

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

Dim ctl As Control
Dim intResponse As Integer

For Each ctl In Me.Controls
If ctl.Tag = "R" Then
If Nz(ctl.Value) = 0 Then
Me![txtRecOK] = False
intResponse = MsgBox(ctl.ControlSource & " is a required field.
Press OK to enter a value " & _
"or Cancel to exit without saving the record.",
vbOKCancel, "Required Field")
If intResponse = vbOK Then
Cancel = True
ctl.SetFocus
Else
' Delete record & exit
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
End If
' Found first blank one, exit sub
Exit Sub
End If
End If
Next ctl

Err_Exit:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2115
Resume Err_Exit
Case Else
MsgBox "The following error has occurred. Please contact the
system administrator." & _
vbCrLf & vbCrLf & Err.Number & vbCrLf & Err.Description,
vbOKOnly, "Runtime Error"
End Select
Resume Err_Exit

End Sub

.

Relevant Pages

  • Re: Record Validation Strategy
    ... the user chooses Cancel from the B4Update code. ... Private Sub Form_Load ... Exit Sub ... For Each ctl In Me.Controls ...
    (microsoft.public.access.forms)
  • Re: Record Validation Strategy
    ... Private Sub cmdExit_Click ... I would like to provide an exit button, ... I could Cancel, but I get an unwanted "Close Action cancelled" message. ... Dim intResponse As Integer ...
    (microsoft.public.access.forms)
  • Re: Dcount problem
    ... Cancel = True ... Exit Sub ... CurrentDb.Execute ("INSERT INTO tblnewparts (partno, xfile, ... "Project Costing Database") = vbNo Then ...
    (microsoft.public.access.formscoding)
  • Re: stop code sending cancelled email
    ... close the email without sending The cancel triggers error ... "Alex Dybenko" wrote: ... Private Sub Command5_Click ... Exit Sub ...
    (microsoft.public.access.modulesdaovba)
  • Re: Exit Sub = Spaghetticode?
    ... mit Exit Sub enden. ... damit Exit Sub nicht verwendet wird, da Exit Sub als ein Sprung gilt ... ' Code-Abschnitt 1 ... verwende, wenn die Prozedur 'unfertig' verlassen werden soll, d.h. ggfs. ...
    (microsoft.public.de.vb)