Save Button (1 Viewer)

Kiki5454 · Mar 7, 2012

Hello, I am trying to create a save button and a close button using vbyesnocancel that will work. I created a save button using the wizard in access 2007, which just uses a Macro in the on click event. I've also tried different code in the before update of the form which doesn't work. (Ie. if "No" is clicked the record is still saved or if the form is closed it still saves the record)

I got rid of the before update event in the form and now just concentrating on the buttons, this is what I have so far:

Code:

Private Sub CommandClose_Click()
'Provide the user with the option to save/undo
'changes made to the record in the form
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
MsgBox "Record has been saved"
DoCmd.RunCommand acCmdClose
Else
DoCmd.RunCommand acCmdUndo
DoCmd.RunCommand acCmdClose
End If
End Sub
 
Private Sub CommandSave_Click()
'Provide the user with the option to save/undo
'changes made to the record in the form
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNoCancel, "Changes Made...") = vbYes Then
DoCmd.Save
MsgBox "Record has been saved"
DoCmd.GoToRecord , , acNewRec
Else
Cancel = True
End If
End Sub

However, it still saves the record when exiting out of the form. Also the close button prompts me to confirm deletion of a record when I select "No."
Any suggestions would be greatly appreciated!
Thanks!

gemma-the-husky · Mar 7, 2012

what if they just close the form?

is it bound, or unbound?

John Big Booty · Mar 8, 2012

Welcome to the forum.

The easiest way to do this is in the Form's Before Update event. The code in that event might look something like;

Code:

    Dim strMSgRsp As String
    
    strMSgRsp = MsgBox("Do you wish to save this record?", vbYesNoCancel)
    
    If strMSgRsp = vbNo Then
        Cancel = True
        Me.Undo
    ElseIf strMSgRsp = vbCancel Then
        Cancel = True
        Exit Sub
    End If

If the user clicks the Form's "x" button you will not be able to cancel that action, so your best bet is to remove that button by setting the Close Button property to No (on the Form's Format Tab). You can then implement your own close button and incorporate the above code in the code behind the button.

Kiki5454 · Mar 8, 2012

It is a bound form.

Kiki5454 · Mar 8, 2012

That works! Now what happens is when I click "No" all the textboxes get cleared, which I do not want to happen. I want them to be able to come back to where they left off. However, if they do click "Yes" I do want all textboxes and option groups cleared. Any way to incorporate that into this code?
Thank you for your help!

John Big Booty · Mar 8, 2012

Remove the line of code Me.Undo from the line of code for the vbaNo response.

Kiki5454 · Mar 8, 2012

Thanks. Ok, did that. I am still encountering these issues:
1. When selecting "No" I get the system message "You can't save this record at this time." ....Do you want to close the database object anyway?" which of course I say yes. How do I turn these off?
2. I really wanted to use on-click buttons for "Save and New" and "Close." It just seems so far that they don't work in conjunction with my before_update event code. What is the best code to use for those 2 buttons?

Thanks

John Big Booty · Mar 8, 2012

Kiki5454 said:
Thanks. Ok, did that. I am still encountering these issues:
1. When selecting "No" I get the system message "You can't save this record at this time." ....Do you want to close the database object anyway?" which of course I say yes. How do I turn these off?

...

From this I'm assuming that the user has clicked the red "X" button. I addressed this in post #3

Kiki5454 said:
...

2. I really wanted to use on-click buttons for "Save and New" and "Close." It just seems so far that they don't work in conjunction with my before_update event code. What is the best code to use for those 2 buttons?

Thanks

I believe this was also covered in post #3.

Kiki5454 · Mar 8, 2012

Sorry, I guess I didn't understand. I thought he said to place that same code from the before update event of the form into the save button also. When I did that, I get the prompt twice. And that message still comes up when clicking "No."

John Big Booty · Mar 9, 2012

Can you post the code you have used, and the event that is firing it?

Kiki5454 · Mar 9, 2012

Yes! In the before update event of the form is this code:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMSgRsp As String
    
    strMSgRsp = MsgBox("Do you wish to save this record?", vbYesNoCancel)
    If strMSgRsp = vbNo Then
        Cancel = True
        Me.Undo
    ElseIf strMSgRsp = vbCancel Then
        Cancel = True
        Exit Sub
    End If
End Sub

In the Save button is the same code:

Code:

Private Sub CommandSave_Click()
Dim strMSgRsp As String
    
    strMSgRsp = MsgBox("Do you wish to save this record?", vbYesNoCancel)
    If strMSgRsp = vbNo Then
        Cancel = True
        Me.Undo
    ElseIf strMSgRsp = vbCancel Then
        Cancel = True
        Exit Sub
    End If
End Sub

In the close button:
is a Macro that has CloseWindow and Save Prompt.

I also realized, when clicking "Yes" on the save command button the record is not saved.

John Big Booty · Mar 9, 2012

When the Save button is clicked what action do you wish to happen?

Do you want to move to a new record, or simply for a message advising that the record has been successfully saved?

Kiki5454 · Mar 13, 2012

I managed to fix that problem. Thanks.
Now I am trying to create a subform that will display the results from a query. After one enters a SSN into a textbox, I am using Me.subform.requery in the after update of the textbox. I have it linked on SSN, but it is still not working.
Do I need to post this question into a new thread?
Thanks.

Kiki5454 · Mar 13, 2012

Also, the subform's recordsource is the query.

Beetle · Mar 13, 2012

You need to use the .Form property when referencing a subform. The syntax is;

Forms!MainFormName!SubformControlName.Form

The .Form would then be followed by either .PropertyName (such as .Requery) or !ControlName (if you need to reference a Control on the subform).

If you are working in the main form module then you can use the Me! keyword in place of Forms!MainFormName of course.

Note that SubformControlName refers to the "window" that holds the subform (which is called a Subform Control). The Subform Control may, or may not, have the same name as the subform itself.

Kiki5454 · Mar 14, 2012

Hi Beetle! Thanks, that worked! How do I clear the results in the subform for each new entry? I have a save command button and an Undo command button. Would I use Recalc?

Kiki5454 · Mar 14, 2012

I think I found it, I'm using "DoCmd.GoToRecord , , acNewRec" to clear the results and it seems to work fine. Is that good or is there a better way?
Thanks!

Beetle · Mar 14, 2012

That depends on what you mean by "clear the results". DoCmd.GoToRecord, , acNewRecord is going move the record set to a new, blank record but is not going to "clear" anything. Any data that was entered in a previous record is still going to be there.

Are you wanting to actually delete data from some previous record, or do you just want to move to a new record so the user can enter a new set of data? (if the latter, then your current method is fine).

Kiki5454 · Mar 14, 2012

The latter. Perfect, thanks!

Save Button (1 Viewer)

Registered User.

Super Moderator

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Duly Registered Boozer

Registered User.

Registered User.

Duly Registered Boozer

Registered User.

Similar threads

Users who are viewing this thread