Full Version: Validation of data in Forms_BeforeUpdate event
UtterAccess Forums > Microsoft® Access > Access Forms
bobdee
I have the following code in the Forms_BeforeUpdate event. I keep getting the following message when the user clicks Yes to enter the missing field now.
You can't save this record at this time."
I'm not sure why this message is appearing and how do I get rid of it. Also, in the code below, do I need the Cancel = False statement?
Thanks.
CODE
On Error GoTo Err_Ctrl
    
If Nz(Me.SITE, "") = "" Then
    If MsgBox("A SITE must be selected. Do you want to enter one now?", vbYesNo + vbExclamation) = vbYes Then
        Cancel = True
        GoTo Exit_Sub
    Else
        Me.Undo
        Cancel = False
        Me.SITE.SetFocus
       GoTo Exit_Sub
    End If
ElseIf Nz(Me.LOCATION, "") = "" Then
    If MsgBox("A LOCATION must be selected. Do you want to enter one now?", vbYesNo + vbExclamation) = vbYes Then
        Cancel = True
        GoTo Exit_Sub
    Else
        Me.Undo
        Cancel = False
        Me.LOCATION.SetFocus
        GoTo Exit_Sub
    End If
End If
  
Exit_Sub:  On Error Resume Next
  
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub
  
Err_Ctrl:
    MsgBox Err.DESCRIPTION, vbOKOnly + vbExclamation
    Resume Exit_Sub
End Sub
theDBguy
You may have your Cancels backwards...
ancel = True - means you want to cancel the update.
Cancel = False - means you want to save the record.
Hope that helps...
MDM_Inc
It looks like you should set Cancel = True no matter what the user answeres to the MsgBox() question. The error message that the user is seeing "You can't save this record at this time." most likely is being causes by a 'save' button on the form. If you make a change to a record and press Shift Enter do you still see the error message? If not, then look at your code for the save button.
Alan
bobdee
Neither suggestion seems to be doing the trick. In stepping through the code, the message appears at the Exit Sub line in the Exit handler.
theDBguy
Are you able to post a small copy of your db with test data? I have a feeling we're not seeing the whole picture.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.