Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 950
  • Last Modified:

Access - Saving a record with command button and Before Update

I want  the user to have the ability to add an additional record or save a record either with a SAVE command button or Before Update.  The form is connected directly to the table "tblChartOfAccounts".  My Before Update code is checking the fields and is saving the record.  Why  is the code different when using the command button or the Before Update button?


Private Sub cmdSaveChanges_Click()

    If IsNull([GL Account]) Or [GL Account] = "" Then
    MsgBox "Account Number Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Exit Sub
 
    ElseIf IsNull(AcctName) Or AcctName = "" Then
    MsgBox "Account Name Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Exit Sub
 
    Else
    MsgBox "The Account Code has been saved", vbOKOnly, "Saved"
    DoCmd.Close
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim response As vbmsgboxresult
    If IsNull([GL Account]) Or [GL Account] = "" Then
    MsgBox "Account Number Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Cancel = True
Exit Sub
 
    ElseIf IsNull(AcctName) Or AcctName = "" Then
    MsgBox "Account Name Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Cancel = True
Exit Sub
     Else
    response = MsgBox("Do you want to save this Account Code", vbQuestion + vbYesNo, "Save Account Code")
    If response = vbyes Then
    MsgBox "Account has been added", vbOKOnly, "Account Added"
   
    DoCmd.Save
   
    Else
    MsgBox "This Account Code will not be saved", vbOKOnly, "Unsaved"
   
    End If
   DoCmd.Close
End If
End Sub
0
SharonInGeorgia
Asked:
SharonInGeorgia
  • 5
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Private Sub cmdSaveChanges_Click()

    If IsNull([GL Account]) Or [GL Account] = "" Then
    MsgBox "Account Number Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Exit Sub
 
    ElseIf IsNull(AcctName) Or AcctName = "" Then
    MsgBox "Account Name Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Exit Sub
 
    Else
Me.Dirty = False  '**save the record here
    MsgBox "The Account Code has been saved", vbOKOnly, "Saved"
    DoCmd.Close
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim response As vbmsgboxresult
    If IsNull([GL Account]) Or [GL Account] = "" Then
    MsgBox "Account Number Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Cancel = True
Exit Sub
 
    ElseIf IsNull(AcctName) Or AcctName = "" Then
    MsgBox "Account Name Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Cancel = True
Exit Sub
     Else
    response = MsgBox("Do you want to save this Account Code", vbQuestion + vbYesNo, "Save Account Code")
    If response = vbyes Then
    MsgBox "Account has been added", vbOKOnly, "Account Added"
   
   '' DoCmd.Save '*** don't do this
   
    Else
    MsgBox "This Account Code will not be saved", vbOKOnly, "Unsaved"
   
    End If
  ' DoCmd.Close    '*** don't do this
End If
End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You  can't do a Save in the Before Update.   A 'save' will trigger any Before Update code you have.

Use the BU  just for validation.

mx
0
 
SharonInGeorgiaAuthor Commented:
So "save" triggers the Before Update code.... Would you include the code to save the record in Before Update if you do not have a save button?   For instance, I have two forms:   In one of the forms, only 3 out of the seven fields are required so the user can save the form before filling out each field or he can complete all of the fields.  I have code in "Before Update" and in a "Save" command button checking for missing required fields and then prompting him to save.   In my other form, the user is required to select a drop-down value for all of the fields so I do not have a save button.  In my first form, is it necessary to include the validation code in both places?  
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Would you include the code to save the record in Before Update if you do not have a save button?"

No ... you don't want to do that ... because you will basically end up in an endless loop.

"In my first form, is it necessary to include the validation code in both places?  "

No.  You can do it in one or the other place.  However, the nice thing about BU is that you can Cancel the BU.  Of course, if you do it in the cmdSaveChanges code above, if the validation fails ... then you would *not* execute the Me.Dirty = False.

The possible downside of doing it in the cmdSaveChanges code is ... if the user uses a keyboard short cut or the menu (if available) ... eg ... Shift+Enter ... you code in the button will not trigger. Sooooo ... the safest approach is to use the BU for the validation ... because that *will* trigger whenever (and how) a 'save' is attempted.

mx
0
 
SharonInGeorgiaAuthor Commented:
SO..... if the user completes the 3 required field and he does not tab to the end of the record to trigger Before Update, what should I do to allow the user to say "Hey, I'm finished and I want to save this new record".
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, you have this right?

Private Sub cmdSaveChanges_Click()

Me.Dirty = False  '**save the record here

End Sub

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sharon ... here is a bit more robust Save button code ... to cover the case when BU fails (Cancel = True) ... and will prevent from getting an Error 2101

Private Sub btnSave_Click()

    On Error Resume Next
    Me.Dirty = False
    If Err.Number = 0 Then
        '  no action required here
    ElseIf Err.Number = 2101 Then
        Exit Sub    'ignore this error ... BU validation failed
    Else
        MsgBox "An unexpected error has occurred." & Chr(13) & Chr(13) & _
               "Error Number: " & Err.Number & Chr(13) & "Description: " & Err.Description, 48, "Error"
    End If

End Sub

mx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now