Before update validation

[teiben]

I've read numerous posts and I'm still not getting this. I have a form with 3 required fields: Supplier, name and PartNumber. I want the code to let the user be able to go back and fill in the field so I put the setfocus there - but instead the access message interupts and doesn't allow the correction. Then it states that can't save a record at this time (bla, bla). Could someone please look at what I have and assist me in making the form validation as well as allowing the user the chance to fix the mistakes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Name) or me.name =””Then
MsgBox "Required Name", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Me![ Name].SetFocus

ElseIf IsNull(Part_Number) or Part_Number = “” then
MsgBox "Required P/N", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Me![Part_Number].SetFocus

ElseIf IsNull(Supplier) or Supplier = “” then
MsgBox "Required Supplier", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Me![Supplier].SetFocus

Else
Cancel = True

End if
End sub

 

[ghudson]

Try this for I cleaned up the mistakes I found. Ensure you are referencing the actual text box not, not the table field name. Also, use double quotes to test for empty strings.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(txtName) or txtName = "" Then
MsgBox "Required Name", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
txtName.SetFocus
Cancel = True
Exit Sub
 
ElseIf IsNull(txtPart_Number) or txtPart_Number = "" then
MsgBox "Required P/N", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
txtPart_Number.SetFocus
Cancel = True
Exit Sub
 
ElseIf IsNull(txtSupplier) or txtSupplier = "" then
MsgBox "Required Supplier", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
txtSupplier.SetFocus
Cancel = True
Exit Sub
 
Else
Cancel = False
End If
 
End sub

 

[adamgoossens]

Then it states that can't save a record at this time (bla, bla). Could someone please look at what I have and assist me in making the form validation as well as allowing the user the chance to fix the mistakes:

If you want to stop the update from occurring, you have to set the Cancel parameter to True. If you want to allow the update (because your validation passed), then you don't change the Cancel parameter (or you set it to False, meaning "don't cancel the update").

When you cancel the BeforeUpdate event, Access throws an error with the number 2169, which is the "Cannot Save Record at This Time". If you catch it in Form_Error, you can provide the Response acDataErrContinue, which tells Access to ignore the error and not pop up an ugly message box:

If DataErr = 2169 Then Response = acDataErrContinue

Oh, there's one problem with your code (the one cleaned up by ghudson): your Else statement at the bottom wants needs to set Cancel = False instead of True (all of the validation tests passed so you *don't* want to cancel).

 

[teiben]

Thanks, how would I incorporate this into the existing code? At each if statement or would I some how comingle it in?