none
[Access 2003 + VBA] Form_BeforeUpdate event Generate Compiler Error Message when OnClick_btnSave event RRS feed

  • Question

  • Hi,

    I have a vba form interface for the access database with different rules and criteria to have a complete saved record.

    I used to have all my rules in OnClick_btnSave event, but it,s useless because it saves record and shows warning message of incomplete record at the same time.

    I transfered all my rules in Form_BeforeUpdate event, when I click save button, if all the criteria are met it's ok, but when a rule is not respected I received a compiler execution error message 2001, Operation Cancelled...

     

    How do I fix that, please?

    Thanks

    Admin-Dev

    Friday, November 4, 2011 11:21 PM

Answers

  • Are you Textboxes bound to a Recordset in your Form (Table/Query)?

    If so, then the values are automatically saved in your Form.

     

    Only need to check if there are Values missing, before closing the Form or go to a New Record for example.

    That's what you are trying to do in the Before Update event. I suggest a small adjustment in the code, something like:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If IsNull(Me![DateOrder]) Or Me![DateOrder] = "" Then
            MsgBox "Please choose the order date", , "Data entry required..."
            Me.DateOrder.SetFocus
         Cancel = True
         
    End If
    


    Then with the Save command you have used, you save the Form Object under a New Name called "Changes updated!".

    If you want to force to save the current record, try this:

    DoCmd.RunCommand acCmdSaveRecord
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, November 7, 2011 8:09 PM
    Moderator
  • You might not have an error handling in place. You can trap this error by placing below code in the OnClick event of your Save Button:

     

    Private Sub cmdSave_Click()
    
    On Error GoTo Error_handler
    
        DoCmd.RunCommand acCmdSaveRecord
    
        Exit Sub
       
    Error_handler:
        If Err <> 2501 Then
            MsgBox Err.Number & " " & Err.Description, vbCritical
          ' clear the error
        Err.Clear
       
    End Sub
    



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, November 7, 2011 10:11 PM
    Moderator

All replies

  • Can you post the code you have in the Before Update event?

    Do you have any code in the OnClick event of your btnSave?

     

    Thanks,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Saturday, November 5, 2011 4:11 AM
    Moderator
  • in before update event:

    If IsNull(Me![DateOrder]) Or Me![DateOrder] = "" Then
            MsgBox "Please choose the order date", , "Data entry required..."
            Me.DateOrder.SetFocus
            DoCmd.CancelEvent
    else
    end if 


    and in save button event:

    DoCmd.Save acForm, "Changes updated!"
    

     



    • Edited by Admin-Dev Monday, November 7, 2011 7:55 PM
    Monday, November 7, 2011 7:54 PM
  • Are you Textboxes bound to a Recordset in your Form (Table/Query)?

    If so, then the values are automatically saved in your Form.

     

    Only need to check if there are Values missing, before closing the Form or go to a New Record for example.

    That's what you are trying to do in the Before Update event. I suggest a small adjustment in the code, something like:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If IsNull(Me![DateOrder]) Or Me![DateOrder] = "" Then
            MsgBox "Please choose the order date", , "Data entry required..."
            Me.DateOrder.SetFocus
         Cancel = True
         
    End If
    


    Then with the Save command you have used, you save the Form Object under a New Name called "Changes updated!".

    If you want to force to save the current record, try this:

    DoCmd.RunCommand acCmdSaveRecord
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, November 7, 2011 8:09 PM
    Moderator
  • you absolutely got it danishani!

    textboxes are bound to a table and values are saved automatically to my records! And I moved the rules from the save button to before update event to prevent MS Access from auto-saving too!

     

    I'll stay with

    DoCmd.Save acForm, "Changes updated!"
    
    'cause I want to prevent from force save...
    I'll try it and get back to you!
    Regards
    Monday, November 7, 2011 9:50 PM
  • when I click save button and a rule is not respected I receive this error message:

     

    runtime error 2501

    DoMenuItem action has been canceled


    • Edited by Admin-Dev Monday, November 7, 2011 9:59 PM
    Monday, November 7, 2011 9:58 PM
  • You might not have an error handling in place. You can trap this error by placing below code in the OnClick event of your Save Button:

     

    Private Sub cmdSave_Click()
    
    On Error GoTo Error_handler
    
        DoCmd.RunCommand acCmdSaveRecord
    
        Exit Sub
       
    Error_handler:
        If Err <> 2501 Then
            MsgBox Err.Number & " " & Err.Description, vbCritical
          ' clear the error
        Err.Clear
       
    End Sub
    



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, November 7, 2011 10:11 PM
    Moderator