Record Validation Strategy

[Sprinks]

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

 

[Allen Browne]

Yes, Sprinks, it's not straight-forward working with this mix of events and
the messages that differ in different versions of Access and tend to either
tie the user in knots, throw unnecessary message, or fail to achieve what
you need.

A solution might be to have your button explicitly save in a way that gives
you a trappable error if the save fails. You can then use error handling to
give the user the choice and respond accordingly.

Aircode:

Private Sub cmdExit_Click()
On Error Goto Err_Handler

If Me.Dirty Then
Me.Dirty = False
End If

DoCmd.Close acForm, Me.Name

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3314&, 2101&, 2115&, 2501& 'can't save.
If MsgBox("Can't save." & vbCrLf & "Discard and close anyway?", _
vbYesNo+vbDefaultButton2) = vbYes Then
Resume Next
Else
Resume Exit_Handler
End If
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Sprinks]

Allen,

Thank you for your helpful response, as always.

The cmdExit code had the effect of not permitting the record deletion when
the user chooses Cancel from the B4Update code. Changing it to an Undo
corrected it. The final form works exactly as I want.

Private Sub Form_Load()
On Error GoTo Err_Handler

If IsLoaded("Timesheet") Then
If Me.NewRecord Then
Me![txtStaffID] = Forms![Timesheet]![txtStaffID]
Me![txtStatusID] = 2
Me![cboProjectNumber] =
Forms![Timesheet]![TimeSheetDetail].Form![cboProjectNumber]
Me![txtNoteDate] = Forms![Timesheet]![PeriodStartDate] +
Nz(Forms![Timesheet]![txtDayNumber])
Me![txtNote].SetFocus
End If
End If

Err_Exit:
Exit Sub

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

End Sub

Private Sub cmdExit_Click()
On Error GoTo Err_Handler

If Me.Dirty Then
Me.Dirty = False
End If

DoCmd.Close acForm, Me.name

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3314&, 2101&, 2115&, 2501&
If MsgBox("Can't save." & vbCrLf & "Discard and close anyway?", _
vbYesNo + vbDefaultButton2) = vbYes Then
Resume Next
Else
Resume Exit_Handler
End If
Case 3021& ' No current record
Resume Exit_Handler
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Select
End Sub

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 through cmdExit
Me.Undo
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

Yes, Sprinks, it's not straight-forward working with this mix of events and
the messages that differ in different versions of Access and tend to either
tie the user in knots, throw unnecessary message, or fail to achieve what
you need.

A solution might be to have your button explicitly save in a way that gives
you a trappable error if the save fails. You can then use error handling to
give the user the choice and respond accordingly.

Aircode:

Private Sub cmdExit_Click()
On Error Goto Err_Handler

If Me.Dirty Then
Me.Dirty = False
End If

DoCmd.Close acForm, Me.Name

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3314&, 2101&, 2115&, 2501& 'can't save.
If MsgBox("Can't save." & vbCrLf & "Discard and close anyway?", _
vbYesNo+vbDefaultButton2) = vbYes Then
Resume Next
Else
Resume Exit_Handler
End If
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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