Generic Form with "Save", "Cancel" buttons

[The_Doc_Man]

Possibly overwhelmed?

 

[MajP]

If you where going to do this more than once then might as well build a class module to make this simple, flexible, and re-usable.

To use on any form simply add the buttons you want. All are optional. In the demo I choose all the buttons, but you can pick whatever ones you want.

Save
Save and Close
Save and Add New
Cancel
Cancel and Close

When you instantiate you can pick to show messages or turn them off.

This is all it takes to make any form into a Simple Save and Cancel

Private SCF As New SaveCancelForm  'after the top of the module after options

Private Sub Form_Load()
  SCF.Initialize Me, True, Me.cmdSave, Me.cmdSaveClose, Me.cmdSaveNew, Me.cmdCancel, Me.cmdCancelClose
End Sub

One thing important in the Demo is that the class traps the Before update event. However if you want to do additional actions such as validation in the Before udpate you can still write a Before update event method in the form. The order is the Before update in the form is trapped before the Before update in the class. This has to be accounted for. Because you can cancel the before update in the form before the class traps the already canceled event. This required some error trapping in the class. To demo this I do validation in the forms before update.

 

[mloucel]

As @Pat Hartman says it is extremely simple to do this. For a simple form in single form view.

Private SavedOnPurpose As Boolean
Private Sub cmdCancel_Click()
  If Not Me.Dirty Then
    MsgBox "There are not updates to cancel.", vbInformation, "No Updates"
  Else
    MsgBox "Updates canceled.", vbInformation, "Updates Canceled."
  End If
    Me.Undo
End Sub

Private Sub cmdClose_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdSave_Click()
  SavedOnPurpose = True
  Me.Dirty = False
  MsgBox "Record Saved", vbInformation, "Saved"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim rtn As Long
  If Not SavedOnPurpose Then
    rtn = MsgBox("Do you want to Save this record?", vbYesNo, "Save Record?")
    If rtn = vbNo Then
      Cancel = True
      Me.Undo
    End If
  End If
  'Reset
  SavedOnPurpose = False
End Sub

Where it gets tricky is when people want to do weird things like Create multiple records on a continuous form and then roll those back. Or Not create a parent record if you do not create child records.
Most of the time these are IMO just dumb user interface designs.
As @Edgar_ points out you have to account for all of the things that could make the form try to save (close, move to new record). That is why even though I have save / cancel buttons I still need to prompt to save/cancel in the before update.

If curious I included an example where you can add multiple records and roll them back in a transaction. Not something I would do, but it shows it can be done.

I simply love your example, is simple and works.. I am sort of not a beginner anymore, I do understand a lot, but one question.. you declare SavedOnPurpose as boolean so I believe at this momment is assumed TRUE; then if the user clicks the "X" asks " Do you want to Save this record? ", and here is my question... SavedOnPurpose is set to FALSE [last line of code], so I thought it SHOULD not save, so it should be be TRUE, I thought there was a mistake, but to my surprise, it doesn't matter if you change the value [False or True] the effect when you say Yes or NO is the same, the record is always saved..

 

[Gasman]

No, if you say no, the cancel is set to true.
Walk the code. What is the value of saved on purpose after being dimmed?

 

[mloucel]

No, if you say no, the cancel is set to true.
Walk the code. What is the value of saved on purpose after being dimmed?

And I'm still Learning...

 

[Pat Hartman]

The example @MagP posted goes further than my simple example. It assumes that you only want to save the record if the user presses the save button which is different from what I suggested. My suggestion only stops erroneous data from being saved. It all depends on your goal. What you want to avoid is prompting the user for every save which some people do. All that does is to train users to ignore your warnings and to always press OK. Within a week, they will be ignoring ALL messages and just pressing OK if you train them to do this with always prompting before save.

As to your question, the SavedOnPurpose Flag should be set to False in the Form's Current event. Set to True in the click event of the Save button and then set back to False at the end of the BeforeUpdate event. So, the only time the flag is true is the time between when the Save button is pushed and the last line of the form's BeforeUpdate event. There is some necessary error trapping that is missing from the posted code. If the form has a close button and the user closes the form without pressing the save button, the close event requires error trapping for error 2501 and possibly a second error number as well. So, if I were going to employ a Save button, I would also go with a Save and Close as well as a Close without saving and lock down the Access "X" to prevent the form from closing except via one of my save options. Of course, you still have the Access close which also might want to control. I use a loose hand and do not control anything that doesn't actually need controlling such as saving bad data. I reserve my control for preventing bad data from being saved. Of course, you can't be 100% on this because if the user picked Red when he meant Blue, too bad. If Blue is a valid value, the data gets saved. This is also the reason I avoid most defaults and require the user to enter data if a field is required.

The vast majority of my apps assume the user knows what he is doing and so always save when Access wants to save it but rely on the Form's BeforeUpdate event to detect incomplete or erroneous data and so prevent saving in those cases.

The most important concept is consistency. Make all the forms work the same way to avoid confusing users and introducing errors. I create a default main form for each application. I copy one from the previous app and change the theme to match the one the user chose for the current app. The form has the basic header/footer and common buttons so I have to pick a RecordSource and add the controls to the form.

 

[mloucel]

The example @MagP posted goes further than my simple example. It assumes that you only want to save the record if the user presses the save button which is different from what I suggested. My suggestion only stops erroneous data from being saved. It all depends on your goal. What you want to avoid is prompting the user for every save which some people do. All that does is to train users to ignore your warnings and to always press OK. Within a week, they will be ignoring ALL messages and just pressing OK if you train them to do this with always prompting before save.

As to your question, the SavedOnPurpose Flag should be set to False in the Form's Current event. Set to True in the click event of the Save button and then set back to False at the end of the BeforeUpdate event. So, the only time the flag is true is the time between when the Save button is pushed and the last line of the form's BeforeUpdate event. There is some necessary error trapping that is missing from the posted code. If the form has a close button and the user closes the form without pressing the save button, the close event requires error trapping for error 2501 and possibly a second error number as well. So, if I were going to employ a Save button, I would also go with a Save and Close as well as a Close without saving and lock down the Access "X" to prevent the form from closing except via one of my save options. Of course, you still have the Access close which also might want to control. I use a loose hand and do not control anything that doesn't actually need controlling such as saving bad data. I reserve my control for preventing bad data from being saved. Of course, you can't be 100% on this because if the user picked Red when he meant Blue, too bad. If Blue is a valid value, the data gets saved. This is also the reason I avoid most defaults and require the user to enter data if a field is required.

The vast majority of my apps assume the user knows what he is doing and so always save when Access wants to save it but rely on the Form's BeforeUpdate event to detect incomplete or erroneous data and so prevent saving in those cases.

The most important concept is consistency. Make all the forms work the same way to avoid confusing users and introducing errors. I create a default main form for each application. I copy one from the previous app and change the theme to match the one the user chose for the current app. The form has the basic header/footer and common buttons so I have to pick a RecordSource and add the controls to the form.

Thank you, I have learned more in this forums than in my own class, I just quit class and started asking questions and looking for answers here, and surely I have learned at a fast pace, way more than page by page in a college class.
Surely I am by no means good at coding but I have learned in around 1 year, way more than I could have ever done in college.

Thank you everyone for your patience, I am truly blessed to have awesome teachers all over the globe.

Maurice.

 

[Edgar_]

Here's a minimal example of an unbound form that does no more than what the code says it does.

No surprises, full control. Minimal code.

Option Compare Database
Option Explicit

Private Sub btn_cancel_Click()
    DoCmd.Close
End Sub

Private Sub btn_save_Click()
    If ShouldCommit Then
        If Nz(Me.OpenArgs, "") = "editing" Then
            CurrentDb.Execute _
                "UPDATE people SET " & _
                "first_name='" & Me.txt_first_name & "'," & _
                "last_name='" & Me.txt_last_name & "'," & _
                "age=" & Me.txt_age & " " & _
                "WHERE person_id=" & Form_f_people_list.person_id, _
                dbFailOnError
            
        ElseIf Nz(Me.OpenArgs, "") = "adding" Then
            CurrentDb.Execute _
                "INSERT INTO people (first_name, last_name, age) VALUES ('" & _
                Me.txt_first_name & "','" & _
                Me.txt_last_name & "'," & _
                Me.txt_age & ")", _
                dbFailOnError
            
        End If
    
        Form_f_people_list.Requery
        DoCmd.Close
    Else
        MsgBox "Please complete the form"
    End If
End Sub

Private Function ShouldCommit() As Boolean
    ShouldCommit = _
        Len(Nz(Me.txt_first_name, "")) > 0 And _
        Len(Nz(Me.txt_last_name, "")) > 0 And _
        Len(Nz(Me.txt_age, "")) > 0 And _
        IsNumeric(Me.txt_age)
End Function

Private Sub Form_Load()
    If Nz(Me.OpenArgs, "") = "editing" Then
        With Form_f_people_list
            Me.txt_first_name = .first_name
            Me.txt_last_name = .last_name
            Me.txt_age = .age
        End With
    End If
End Sub

Of course, this is a 3 inputs form, but it should not grow too much. Shall extra requirements be necessary, you add them there. As mentioned, this is a valid approach for its simplicity and flexibility.

This architecture assumes the user works with a list of records from which they can add or edit them using the unbound form. The list is necessary to get the values from that list form when editing a record.

 

[Pat Hartman]

You cleverly omitted all the navigation code.

 

[KitaYama]

Option Compare Database
Option Explicit

Private Sub btn_cancel_Click()
    DoCmd.Close
End Sub

Private Sub btn_save_Click()
    If ShouldCommit Then
        If Nz(Me.OpenArgs, "") = "editing" Then
            CurrentDb.Execute _
                "UPDATE people SET " & _
                "first_name='" & Me.txt_first_name & "'," & _
                "last_name='" & Me.txt_last_name & "'," & _
                "age=" & Me.txt_age & " " & _
                "WHERE person_id=" & Form_f_people_list.person_id, _
                dbFailOnError
           
        ElseIf Nz(Me.OpenArgs, "") = "adding" Then
            CurrentDb.Execute _
                "INSERT INTO people (first_name, last_name, age) VALUES ('" & _
                Me.txt_first_name & "','" & _
                Me.txt_last_name & "'," & _
                Me.txt_age & ")", _
                dbFailOnError
           
        End If
   
        Form_f_people_list.Requery
        DoCmd.Close
    Else
        MsgBox "Please complete the form"
    End If
End Sub

Private Function ShouldCommit() As Boolean
    ShouldCommit = _
        Len(Nz(Me.txt_first_name, "")) > 0 And _
        Len(Nz(Me.txt_last_name, "")) > 0 And _
        Len(Nz(Me.txt_age, "")) > 0 And _
        IsNumeric(Me.txt_age)
End Function

Private Sub Form_Load()
    If Nz(Me.OpenArgs, "") = "editing" Then
        With Form_f_people_list
            Me.txt_first_name = .first_name
            Me.txt_last_name = .last_name
            Me.txt_age = .age
        End With
    End If
End Sub

@Edgar_ Not trying to be picky, but if the form is unbound, you should fill the form before being able to edit it. I don't see how you fill the controls. Do you use DLookups for each control, or do you use a disconnected ADO recordset?

 

[Edgar_]

I don't see how you fill the controls. Do you use DLookups for each control, or do you use a disconnected ADO recordset?

Please check the Load event.

The workflow includes a "main" form dedicated to displaying a list of records through a continuous form, offering viewing and deletion options exclusively. The capabilities for adding and editing are reserved for the unbound form, accessed through the "main" form. This setup mitigates several issues, such as the need for navigation features within a simple entry form and addressing concerns like attempting to add children before parents or other illogical scenarios. The setup is also something people used to other platform can easily get, reducing the need for training in most cases.

As for how the data is loaded, since there is already an accessible recordset containing the necessary data, in the Load event of the unbound form, I simply reference that record. However, as you may anticipate, many other approaches can be followed depending on your needs. One might access the form's recordset directly from the unbound form, utilize DLookups (like you say), pass data through OpenArgs or TempVars, or even open a new recordset from the unbound form, among other possibilities.

If anyone wants to see it in action, I attached the example here.

 

[KitaYama]

Please check the Load event.

The workflow includes a "main" form dedicated to displaying a list of records through a continuous form, offering viewing and deletion options exclusively. The capabilities for adding and editing are reserved for the unbound form, accessed through the "main" form. This setup mitigates several issues, such as the need for navigation features within a simple entry form and addressing concerns like attempting to add children before parents or other illogical scenarios. The setup is also something people used to other platform can easily get, reducing the need for training in most cases.

As for how the data is loaded, since there is already an accessible recordset containing the necessary data, in the Load event of the unbound form, I simply reference that record. However, as you may anticipate, many other approaches can be followed depending on your needs. One might access the form's recordset directly from the unbound form, utilize DLookups (like you say), pass data through OpenArgs or TempVars, or even open a new recordset from the unbound form, among other possibilities.

If anyone wants to see it in action, I attached the example here.

I'm receiving some Active X errors while testing your database. It's not your fault. It's a common error while sharing Access databases on different languages.

Though I can't open your sample database, but going through the code, I think I understand what you're trying to achieve.
You're dealing with two forms, one bound and one unbound pointing to the same record. I thought you're talking about one unbound form.

I don't know what will happen while a user opens the unbound form to edit, another user edits the same record. I think you need a lot of code to check if any field has been changed before saving the record. In a bound form, using pessimistic lock takes care of it.

thanks for the sample database.

 

[Edgar_]

You're dealing with two forms, one bound and one unbound pointing to the same record. I thought you're talking about one unbound form.

Yes, the bound form has the recordset and the unbound form takes the record from there.

I'm separating concerns to get the best of the bound form and the best of the unbound form. That way I dodge the nuisances of some of Access' defaults.

It's not fun to do everything from the same form. It would be a mess to keep up with, the maintenance would be a nightmare and it could frustrate the users.

 

[arnelgp]

there are many approach you can take.
another one is using Transaction on a Bound form.
you can Edit multiple records and later decide you don't want to save them
after all so you can Cancel all changes you made.

using the demo edit as many records (deleting their content, moving to another record and
doing the same) then press the Cancel button to re-instate all records.

You can even delete multiple records and re-instate them using the Cancel button.

when you press Save button, all you're changes are written to the table.

 

[Jason Lee Hayes]

there are many approach you can take.
another one is using Transaction on a Bound form.
you can Edit multiple records and later decide you don't want to save them
after all so you can Cancel all changes you made.

using the demo edit as many records (deleting their content, moving to another record and
doing the same) then press the Cancel button to re-instate all records.

You can even delete multiple records and re-instate them using the Cancel button.

when you press Save button, all you're changes are written to the table.

That is so simple yet affective; excellent... Thanks for sharing...

 

[mloucel]

there are many approach you can take.
another one is using Transaction on a Bound form.
you can Edit multiple records and later decide you don't want to save them
after all so you can Cancel all changes you made.

using the demo edit as many records (deleting their content, moving to another record and
doing the same) then press the Cancel button to re-instate all records.

You can even delete multiple records and re-instate them using the Cancel button.

when you press Save button, all you're changes are written to the table.

I would ADD "dbForceOSFlush" but I am a bit nervous when saving data, it may be overkill but......

Private Sub cmdSave_Click()
    With WrkSpace
        'commit any changes
        .CommitTrans dbForceOSFlush
        Me.Recordset.Requery
        'begin  new transaction
        .BeginTrans
    End With
    m_dirty = False
End Sub

 

[Pat Hartman]

And moi is still MIA

 

[Jason Lee Hayes]

And moi is still MIA

It's not a wasted effort by those that provided assistance though. I've learned something new which I'm grateful for the responses. moi is still MIA made me chuckle...

 

[jdraw]

Jason,
I agree fully. There is always something --could be new concept; new usage; another way to look at an issue; a code snippet; or simply reading an interesting dialog. Just browsing through older or open threads can lead to discovery and initiate a new field of interest. Often a learning opportunity.