Save changes to a table

[damdoumaa]

Hello everybody,

I guess that this is kind of a basic question, I hope someone has the answer.

This is about a contact database.
I have a search form that allows the user to search by either first name or last name. The user gets the list of contacts that meet the criteria in a listbox, then double clicks on a contact to get all the person's information in another form. That's good so far.

Since the contact form has bound textboxes that have controlsource directly from the table, the user will be able to change any field directly to the table (sometimes users think that if they don't click on save, the changes will not be saved which is not true in this case)

I want to have some kind of control on this. I want to have a button called "save" that will give a message to the user "Are you sure you want to save?" if the user clicks "yes" the changes will be saved to the table, otherwise the changes will not be saved. The same message should appear when the user had made changes and attempts to exit the form.

Is this doable??

THANK YOU

 

[Ukraine82]

Paste the code on command button.

You might want to change Sub Command31_Click() to whatever your button name is.

Private Sub Command31_Click()
On Error GoTo Err_close_Click
Dim LResponse As Integer
Dim LMsg As String
LMsg = "Do you wish to save changes?"
LResponse = MsgBox(LMsg, vbYesNo, "Save changes")
If LResponse = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
DoCmd.Close
Exit_close_Click:
Exit Sub
Err_close_Click:
MsgBox Err.Description
Resume Exit_close_Click

End Sub

Michael

 

[The_Doc_Man]

Takes a bit of work, but yes you can do that. If you are happy with VBA it is not TOO hard.

In your form you need a class module that has a global variable (i.e. in the general declaration area rather than inside a specific routine) that says UsedOK or some such name. It can be Dim'd as Boolean. On form load event, set the flag to FALSE.

Use the button wizard to add a SAVE button to your form. You have the option of selecting a Save Record action from the list of wizard operations. As part of the action, you can set the UsedOK flag true. While you are at it, build a CANCEL button on the form. The wizard can also build you the required Undo Record command. Maybe you won't keep it, but then again it is nice to have. And if you are not really comfortable with VBA at that level, it will show you how to cancel the operation and reset the record to its pristine state.

Now in the Form's BeforeUpdate event, which would be fired AFTER you clicked the SAVE button or AFTER someone tries to navigate away from the record, but BEFORE the actual update occurs, you can test the state of the UsedOK flag. If UsedOK is set, let the update happen. If UsedOK is NOT set, you can pop up a message box to prevent the accidental update. If you allow the update to occur, then in the AfterUpdate event, clear the UsedOK flag. If the user does not select to allow the update, you then have two choices - reset changes or return to a "dirty" form. This is where it would be nice to have a copy of the Undo action handy to determine how to undo your user's changes. The logic here is kind of ugly depending on how many options you want to give your users.

In the application where I used this, I allowed them to SAVE or CANCEL by button, in which case nothing else got in the way. They returned to the same record, which was now "clean." From that, they could exit or navigate away. But if they tried to exit or navigate away from a dirty record, a message box would pop up. They could choose to save the record by clicking yes (just like they had pressed SAVE, and set the UsedOK in this case) or return to the dirty record by clicking NO (resulting as though they had done nothing). I made the message boxes modal so they couldn't avoid expressing their choice. Then I simply blocked all attempts to leave a dirty record.

NOTE: You can tell if the form is "dirty" but have to do a comparison over all controls (sensitive to control types) to determine which field is dirty. This is essentially a For Each loop over all controls on the form, skipping the labels and other items that cannot possibly get dirty 'cause they aren't bound to anything in a recordsource.

 

[damdoumaa]

Thank you veryyy much... that was very helpful

 

[damdoumaa]

If the user does not select to allow the update, you then have two choices - reset changes or return to a "dirty" form.

Doc Man,
Thank you for this response. However I have some questions about this method.

If UsedOK is set, let the update happen.

1- I can "let the update happen" by doing nothing. Is that correct?

If you allow the update to occur, then in the AfterUpdate event, clear the UsedOK flag.

2- How do I do this?

THIS is the code that i got so far in the BeforeUpdate event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim LResponse As Integer
Dim LMsg As String

If UsedOK = True Then
'Let update to happen
ElseIf UsedOK = False Then
LMsg = "Do you wish to save changes?"
LResponse = MsgBox(LMsg, vbYesNo, "save changes")
If lsresponse = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
End If
End Sub

AM I missing anything? This is not totally working, because When I press exit, then select "No" from the messagebox, the changes are saved !!

Thank you for your help.

-DAMDOUMAA