Solved Best method to edit table entries in a form?

[mamradzelvy]

Hi,
I'm looking for some insight on ways to edit table entries within a form.

What am i trying to do: Open a pop-up form on a specific record (that part is done), however, i want all the changes to be save on demand via a button confirmation, rather than just editting the raw data within the table on the form.

Now i think there's two methods here:

A: Use me.dirty and undo, which im not sure how they work properly yet​

or B: Load the data from the db via query and then send the text box values back as variables in another query.​

(am i right or wrong here?)

Is there any other method; is any of these methods better than the other, and why?


PS: i hope this is the right forum section for this post.

 

[strive4peace]

hi mamradzelvy,

it isn't a good idea for users to edit data directly in tables. Always best to make forms to add and edit.

Information is automatically saved when you move to another record. The hard part is stopping it from saving! You can use the form BeforeUpdate event to ask if that is what they want to do and if not, Undo and Cancel

 

[Minty]

The second method you are describing is called an Unbound form.
It generally involves a lot more code to work successfully than a bound form, and most of the time is unnecessary.

As @strive4peace has suggested the Undo / Cancel route in the before update event is normally the best way to go.

 

[strive4peace]

here is some code you can put on the form BeforeUpdate event :

Private Sub Form_BeforeUpdate(Cancel As Integer)
   'make sure user wants to add or edit record
   If Me.NewRecord then
      If Msgbox("Do you want to add this record?", vbYesNo, "Add record") = vbYes then
         ' everything is ok, exit the procedure
         exit sub
      End If
   Else
      If Msgbox("Do you want to save changes to this record?", vbYesNo, "Save changes") = vbYes then
         ' everything is ok, exit the procedure
         exit sub
      End If  
   end if
  
   'don't save the record 
   Cancel = true

   'undo the record
   Me.Undo

End Sub

 

[mamradzelvy]

here is some code you can put on the form BeforeUpdate event :

   'make sure user wants to add or edit record
   If Me.NewRecord then
      If Msgbox("Do you want to add this record?", vbYesNo, "Add record") = vbYes then
         ' everything is ok, exit the procedure
         exit sub
      End If
   Else
      If Msgbox("Do you want to save changes to this record?", vbYesNo, "Save changes") = vbYes then
         ' everything is ok, exit the procedure
         exit sub
      End If  
   end if
  
   'don't save the record 
   Cancel = true

   'undo the record
   Me.Undo

thank you very much for this, i was using the code bellow (which i've had recommended to me in the past here on the forum) for now, but i suppose it being on the button is not as smart as the data will remain changed when the user exits through alt+f4 or task manager, right?

Private Sub btnExit_Click()
 Dim Answer As Integer
 If Me.Dirty = True Then
    Dim Response As Integer
    Response = MsgBox(Prompt:="Do you wish to save changes?", Buttons:=vbYesNo)
    If Response = vbNo Then
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close
        Else
        DoCmd.Close
    End If
 Else
    ' The no button was selected.
    DoCmd.Close
    
  End If
End Sub

 

[strive4peace]

that is correct, mamradzelvy

you're welcome!

people will answer the question you ask ... so if you want code for a button, that is what you'll get

best though, is to trap changes before they're written

 

[strive4peace]

btw, mamradzelvy ... I add fields to track when records are added and edited to tables.

dtmAdd, date/time, Default Value = Now()
dtmEdit, date/time, Default Value = Now()

they both automatically get a value when a record is created. dtmEdit is changed on the formBeforeUpdate event. So instead, you could do it this way:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   dim sMsg as string

   'make sure user wants to add or edit record
   If Me.NewRecord then
      sMsg = "Do you want to add this record?"
   Else
      sMsg = "Do you want to save changes to this record?"
   end if

   If Msgbox(sMsg, vbYesNo, "Save changes?") = vbYes then
      ' everything is ok. Record date/time edited 
      Me.dtmEdit = Now()
      ' exit the procedure
      exit sub
   End If

   'don't save the record 
   Cancel = true

   'undo the record
   Me.Undo

End Sub

 

[strive4peace]

mamradzelvy, if this answers your question, please click on the Solved button above your first post on the right