Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    17

    Unanswered: undo on an unbound form

    I have been looking for ways to make a Undo button for an unbound form so when the user makes a mistake they can hit the cancel button and it will load the original information. I have read many forums saying that this is not possible.

    ex, user beings to edit a PC's information and decides they selected the wrong PC and needs to cancel out if it and chose the correct one.

    I did find one example of a DB that has this function but the coding is completely out of my league

    Form_UnBound_UnDoReDo - Roger's Access Library

    I was wondering if there is any simpler way to do it

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by mike.burns7 View Post
    I have been looking for ways to make a Undo button for an unbound form so when the user makes a mistake they can hit the cancel button and it will load the original information. I have read many forums saying that this is not possible.

    ex, user beings to edit a PC's information and decides they selected the wrong PC and needs to cancel out if it and chose the correct one.

    I did find one example of a DB that has this function but the coding is completely out of my league

    Form_UnBound_UnDoReDo - Roger's Access Library

    I was wondering if there is any simpler way to do it
    Unbound forms are not simple.

    If you want complete control then an unbound form will give you that. Since you have complete control, you can so anything that you are willing to write the code to handle. The downside is that you must write lots of VBA code to handle everything that all the events that Access provides with bound forms.

    I have created several hundred full blown applications is Access. In 1000+ forms, I have used at maybe 5 to 10 unbound forms.

    In my opinion, Access's bound forms is one of the major strengths of this application. I have learned to take full advantage of this powerful feature.

    Why do you think you need an unbound form to do what you have described? Bound forms handle it quite well.
    Last edited by HiTechCoach; 05-18-10 at 20:22.
    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
    Apr 2010
    Posts
    17
    The current database we use now uses a bound forms. the downside i found to a bound form is that if a field is unlocked and you edit it, and then move to the next record, that edit you made automatically gets saved into the table with out the need of a "save" button.

    I started making a different version of the DB with an unbound form and like the fact that if a record accidentally gets edited and you move to the next record, nothing is saved.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by mike.burns7 View Post
    ...if a field is unlocked and you edit it, and then move to the next record, that edit you made automatically gets saved into the table with out the need of a "save" button.
    This can be prevented by asking the user if they want to save the changes, using the Form_BeforeUpdate event:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not (Me.NewRecord) Then
     If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
      Me.Undo
     End If
    Else
     If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
      Me.Undo
     End If
    End If
    End Sub
    A big part of the reason to use Access for database development is the speed with which it can be created, using bound forms. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development using unbound forms by experienced developers takes twice as long as it does when using Access and bound forms.

    The belief that unbound forms are necessary in order to do data validation or to prevent "accidental" saving of a record (the reasons usually given) is simply not true!

    If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

    You can create an EXE file which gives total protection to your code/design

    You can distribute the db to PCs without a copy of Access being on board

    Your data security if far, far better than anything you can do in Access
    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 2010
    Posts
    17
    i think from the information i gathered here, bound forms are the way i am gonna go given my time and skill lever.

    I appreciate all the suggestions and help guys

    Missinglinq I will give that beforeupdate code a try and see if it suits my needs

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by mike.burns7 View Post
    The current database we use now uses a bound forms. the downside i found to a bound form is that if a field is unlocked and you edit it, and then move to the next record, that edit you made automatically gets saved into the table with out the need of a "save" button.

    I started making a different version of the DB with an unbound form and like the fact that if a record accidentally gets edited and you move to the next record, nothing is saved.
    Missinglinq has given you some very good advice.

    Access Forms have many properties and events that can give you all the control over a bound form that you need.

    I also recommnd that you use the Before update event. This event is great fopr form level data validation and confirming record changes. This event always fires before the record is committed.


    Here is sample code that can be used in the before update event of the customer form in the sample Northwind.mdb
    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


    It is also possible to keep the form locked (.AllowEdits = False )until a Edit button is clicked

    Hope this helps ...
    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

  7. #7
    Join Date
    Apr 2010
    Posts
    17
    Missinglinq

    i tried editing your code to what i want it to do but i am having some trouble

    basically i have a save and cancel button on my form that i want user's to use.

    so if they edit a record and then go on to the next done without clicking either one of those buttons i want a pop-up to come up and remind them to use the buttons on the form.

    i know its simple but having trouble

  8. #8
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by mike.burns7 View Post
    Missinglinq

    i tried editing your code to what i want it to do but i am having some trouble

    basically i have a save and cancel button on my form that i want user's to use.

    so if they edit a record and then go on to the next done without clicking either one of those buttons i want a pop-up to come up and remind them to use the buttons on the form.

    i know its simple but having trouble
    Please post the VBA code you have for each command button.
    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

  9. #9
    Join Date
    Apr 2010
    Posts
    17
    This is the save button on form
    Code:
    Private Sub btnApplyNewUser_Click()
    
    On Error GoTo Err_btnApplyNewUser_Click
    Me.btnChangeUser.Visible = True
    
    'inserts record of new user into asset history table and adds current date into assignment_date
    
    DoCmd.SetWarnings False
    DoCmd****nSQL "UPDATE tblAsset_Assign_History SET return_Date = Now() WHERE tblAsset_Assign_History.Assignment_date = (SELECT Max(tblAsset_Assign_History.Assignment_date) AS [MaxAssignment_date] FROM tblAsset_Assign_History GROUP BY tblAsset_Assign_History.AssetID HAVING (tblAsset_Assign_History.AssetID)= " & Me.AssetID.Value & ");"
    DoCmd****nSQL "INSERT INTO tblAsset_Assign_History( [AssetID], drvempno, Assignment_Date) SELECT " & Me.AssetID.Value & " as assetID, " & Me.drvEmpNo.Value & " AS empdrvno, now() as Assignment_Date"
    Forms!frmAsset!List100.SetFocus
    Me.frmAsset_Assign_History_subform.Requery
    
    'locks fields
    Me.Computer_Name.Locked = True
    Me.Asset_Number.Locked = True
    Me.Inventory_StatusID.Locked = True
    Me.drvEmpNo.Locked = True
    
    DoCmd.SetWarnings True
    
    'ignores when user does not enter a name and just changes inventory status
    Err_btnApplyNewUser_Click:
    Forms!frmAsset!List100.SetFocus
    Me.frmAsset_Assign_History_subform.Requery
    
    'locks fields
    Me.Location.Locked = True
    Me.Inventory_StatusID.Locked = True
    Me.drvEmpNo.Locked = True
    
    DoCmd.SetWarnings True
    
    'hides buttons
    Me.btnApplyNewUser.Visible = False
    Me.btnAssignCan.Visible = False
    Me.btnApplyNewUser.Visible = False
    Me.btnAssignCan.Visible = False
    Me.btnAddRec.Visible = True
    End Sub

    this is the cancel button


    Code:
    Private Sub btnAssignCan_Click()
    'if user clicks "add asset" x = 1 to run undo record command
    If x = 1 Then
    On Error GoTo Err_Command258_Click
        DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    Err_Command258_Click:
    End If
    'if user clicks change user runs undo command
    x = 0
    
    'shows buttons
    Me.btnChangeUser.Visible = True
    Me.btnAddRec.Visible = True
    
    DoCmd.CancelEvent
    
    Me.Requery
    
    'set focus on listbox
    Forms!frmAsset!List100.SetFocus
    
    'hides buttons
    Me.btnApplyNewUser.Visible = False
    Me.btnAssignCan.Visible = False
    
    DoCmd.SetWarnings True
    End Sub
    
    Private Sub btnChangeUser_Click()
    'unlock fields to edit
    Me.Inventory_StatusID.Locked = False
    Me.drvEmpNo.Locked = False
    Me.btnApplyNewUser.Visible = True
    Me.btnAssignCan.Visible = True
    
    
    End Sub

    and then i this is the before_update i would like to edit which was posted by
    Missinglinq. I would simply like it to pop up a vbOKonly and remind the user to use the form buttons. I tried editing it but it was giving me argument errors i couldn't get around

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not (Me.NewRecord) Then
     If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
      Me.Undo
     End If
    Else
     If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
      Me.Undo
     End If
    End If
    End Sub

Posting Permissions

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