Generic Form with "Save", "Cancel" buttons

[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...

 

[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.

 

[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

 

[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.