Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Form_BeforeUpdate automatically updating database....HELP!

    Good Day

    From all of what I have read and the suggestions of others, I should do all my edits, data validation and database updates in the Form_BeforeUpdate procedure. If I make a change to a record. and then click on the Close button, the Form_BeforeUpdate procedure executes as it should.

    But I want to prevent the Form_BeforeUpdate procedure from automatically storing a record and I want to do my edits, data validate and database updates using the ENTER KEY.

    In my Form_BeforeUpdate I have just the code CANCEL = TRUE. The code prevents unwanted data from being stored, however after closing the form, I am getting a message box that says "You cant save this record at this time" and YES/NO buttons to close the database. How can I prevent this from happening?

    What am I doing wrong?

    Thank you so much for your help!

    PS - As a person new to ACCESS, I have learned a lot from the VERY KIND people on this forum.....I truly appreciated it.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    Good Day

    But I want to prevent the Form_BeforeUpdate procedure from automatically storing a record and I want to do my edits, data validate and database updates using the ENTER KEY.
    If you have:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     Cancel = True
    
    End Sub
    This should prevent Access from ever saving the record.

    The Before Update must complete without being Canceled before the record will be saved.

    I used something like this:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     Cancel = False
    
    
    ' perform data validation
    If IsNull(Me.CompanyName) Then
    
       MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
          
       Cancel = True
    
    
    End If
    
    
    If Not Cancel Then
      ' passed the validation process
    
        If Me.NewRecord Then
            If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
                Cancel = True
            Else
                ' run code for new record before saving
            
            End If
        
        
        Else
            If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
                Cancel = True
            Else
               ' run code before an existing record is saved
               ' example: update date last modified
                
            End If
        End If
    
    End If
    
    
    ' if the save has been canceled or did not pass the validation , then ask to Undo changes
    If Cancel Then
    
        If MsgBox("Do you want to undo all changes?", vbYesNo, "Confirm") = vbYes Then
            Me.Undo
    
        End If
        
    End If
    
    
    
    End Sub
    If you want the Enter Key to save the record, what I would suggest is that you first create a "save" command button.
    To get the enter key to click the "save" button, set the command button's Default property to Yes. The "save" button will then cause the Before Update event to fire.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    67
    HiTechCoach

    Thank you. Just completed my first ACCESS program. Proceeding to the next program that will be a part of the application. More questions to follow!

    Again, thank you so very much for all your help and patience. It is TRULY appreciated.

Posting Permissions

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