Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    5

    Unanswered: Need help with code behind save button

    I want to force users to place a comment in a nonrequired field when they select "Other" for a reason in a dropdown box. I already have code that works for the yes/no, but I can't figure out where to place my "If" code to force a comment where it will work.

    Private Sub btnSave_Click()
    On Error GoTo ErrorHandler

    Dim strMsg As String, strTitle As String

    strMsg = "Do You Want To Save This Record?"
    strTitle = " Save Record ?"

    If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
    Me.Undo

    ExitErrorHandler:
    Exit Sub

    ErrorHandler:
    MsgBox "Please make sure all information is entered."
    Resume ExitErrorHandler

    'If Me.cboReason.Value = "Other" Then
    'MsgBox "Enter Comment for Reason Code 'Other'."
    'Resume ExitErrorHandler
    'End If

    End If
    DoCmd.GoToRecord , , acNewRec
    End Sub

    I'd appreciate any help, i've tried to place the code just about everywhere and can't figure this out. Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Validation code that involves more than one Control has to be done in the Form_BeforeUpdate event.
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     If Me.cboReason = "other" And Nz(Me.CommentField, "") = "" Then
      Cancel = True
      MsgBox "Enter Comment for Reason Code 'Other'."
      CommentField.SetFocus
      Exit Sub
     End If
    End Sub
    Replace CommentField with the actual name of your Control.

    In point of fact, all your code asking the user if they want to save the Record should also be in the Form_BeforeUpdate event. The use of a 'save' button is really unnecessary, in Access, and often creates problems.

    The native behavior of Access is that a Record is Saved, automatically, when the user
    • Moves to another Record.
    • Closes the Form.
    • Closes Access, itself.

    Because of this, in order for this bit of code
    Code:
    strMsg = "Do You Want To Save This Record?"
    strTitle = " Save Record ?"
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
    Me.Undo
    to insure that the Record isn't saved without a comment, if cboReason = "other", you'll have to
    • Remove the native Close Button from the Form
    • Remove the Navigation buttons from the Form
    • Remove the ability for the user to Close Access itself
    • Add a custom Form Close Button
    • Add a custom Quit Button for Access itself
    • Add custom Navigation Buttons.

    Moving your above code to the Form_BeforeUpdate event, after the code that I gave you, would alleviate all of these problems. If you want to keep your 'Save' button, you could merely retain the

    DoCmd.GoToRecord , , acNewRec

    code behind the button. When the user clicks it, Access will move to the Form_BeforeUpdate event and all of the Validation code will be executed.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2012
    Posts
    5

    RE: Need help with code behind save button

    Moving to the before update worked for the comment field, i also put the save code behind that as you suggested. It errors though when you don't put the comment in after choosing "other" because of the save button's code of DoCmd.GoToRecord , , acNewRec

    I use the save code as a nice way to ensure they complete all fields, it will give them a nice error message if they didn't fill in a control and can undo everything they typed if they choose no for whatever reason.

    I have removed the native close buttons on forms and Access itself as well as the navigation buttons. I added custom buttons to make the user exits the way that i would like them to.

    Do you have any suggestions for the Save button to go to a new record only if it passes the before update validation?

    Thanks for your help.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Leave the code

    DoCmd.GoToRecord , , acNewRec

    in your 'Save' button. As I said before, when the user clicks on it, Access will execute the Form_BeforeUpdate event, the Validation code will run.

    After the Validation is satisfied the user will be moved to a New Record. You can leave the button's caption as 'Save' just like before. From the user's standpoint, that is what's occurring.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2012
    Posts
    5
    It works in every instance except where the user doesn't put a comment in when "other" is selected. When you don't place a comment in, the message box comes up for the comments, then the code itself errors Runtime Error 2105: You can't go the specified record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •