Help needed abandoning a half entered record

[tgbyhn10]

Hi

I'm new to access programming so would appreciate a little help.

My problem is that I want to validate a record in the beforeupdate event of the form. I have done this by setting cancel = true if the validation fails. The problem i have is that I then can not move to a different record until I enter valid data on the current records. Ideally, I want to be able to move away from this record / cancel data entry alltogether.

I want something like one of the following but I don't know how to code it or which would be best:

1) a cancel button on the form which abandons the current half entered record and returns to the previous good record in the table

2) cancel the half entered record and return to the last good record in the table if the user clicks on the previous record button

3) have a cancel button on the msgbox that displays the validation error and cancel the half entered record and return to the last good record in the table if the user clicks on it.

Any help appreciated

Regards

Pete



Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim InValidInvoiceTotal As Boolean
Dim InValidCompanyName As Boolean
Dim sMsg As String

On Error GoTo Err_Form_BeforeUpdate

Msg = ""

InValidInvoiceTotal = False
If IsNull(Me!InvoiceTotal) Then
InValidInvoiceTotal = True
ElseIf Not IsNumeric(Me!InvoiceTotal) Then
InValidInvoiceTotal = True
ElseIf (Me!InvoiceTotal) = 0 Then
InValidInvoiceTotal = True
End If

If InValidInvoiceTotal Then
Msg = Msg & "The invoice total is zero." & Chr(10)
UnitPrice1.SetFocus
Cancel = True
End If

InValidCompanyName = False
If IsNull(Me!CompanyName) Then
InValidCompanyName = True
ElseIf Len(Me!CompanyName) = 0 Then
InValidCompanyName = True
End If

If InValidCompanyName Then
Msg = Msg & "You have not selected a customer from the database" & Chr(10)
Me!cboCompanyName.SetFocus
Cancel = True
End If

If Cancel = True Then
MsgBox "Please correct the following errors or press cancel to abandon the record" & Chr$(10) & Chr$(10) & Msg, vbInformation, "Validation"
End If

Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description

End Sub

 

[boblarson]

I would get rid of all of your Cancel = True within the code and just change this last part:

If Cancel = True Then
MsgBox "Please correct the following errors or press cancel to abandon the record" & Chr$(10) & Chr$(10) & Msg, vbInformation, "Validation"
End If

to this:

If MsgBox("Do you wish to continue with this record?" & vbCrLf & Msg, vbQuestion + VbYesNo,"Validation") = vbNo Then
   Cancel = True
   Me.Undo
Else
   Cancel = True
End If

Just as an FYI - You don't need to use Chr$(10) & Chr$(10) as VbCrLf will be less typing but means the same.

 

[tgbyhn10]

Thanks. All sorted now.