Closing Command Button Won't Bring Up Prompt (1 Viewer)

[Reese]

[Solved] Closing Command Button Won't Bring Up Prompt

Okay, weird situation.

In the past I've used command buttons with both VBA & Macros to bring up a prompt to save or discard changes to the current record then close the current form.

For some reason, on just one single form that I just created, none of the techniques seem to work. It will close the form but will automatically save changes without bringing up a prompt. I've tried creating buttons from scratch, trying out both Macros & VBA, and I've tried copying/pasting buttons from other forms--of course making the appropriate changes.

Nothing seems to work. Am I missing some random setting within the form properties that randomly got changed or something?

 

[JHB]

Post a sample database with a form which act like you want it and one which doesn't + the necessary tables.

 

[Khalid_Afridi]

Which version of Access you are using? Is it behave only for the specific form OR all forms created earlier?
Did you use the SetWarnings to False in any of your VBA code?
If so, then change it back to true.

 

[Reese]

Okay, I thought that it was just the form, but now it seems to have affected the entire program. Yes, all of Access 2007 seems to be having this problem.

I even created a tiny sample database with one form that has 3 buttons with macros--one set to an automatic save, one an automatic don't save and one to a prompt. All three automatically save. I have attached the database.

Is there a program preference that somehow got changed?

 

[Reese]

I forgot to mention that in the first database I tested that the warnings are set to true by running an update query. The standard warning came up as normal, so I can only assume that the warnings are properly set.

 

[JHB]

The macros you have is for the form object, not for data in the form.
Open the form in design view, place a control on it, run it and the hit the buttons one after one to see the differences, (each time after you have change something in the form)..

 

[Reese]

Okay, I see what happened there. I created a text box, went to Form view, hit the buttons and they worked.

But how can I create buttons that save, don't save or prompt for a save regarding the data? And how come what I had worked for the data before but not now?

Here's an example of the VBA code that I used in various forms:

Private Sub CancelCmd_Click()

Dim CmdCancelMsg As String
CmdCancelMsg = MsgBox("Cancel update and close without saving?", vbYesNo + vbDefaultButton1, "Cancel Update?")
If (CmdCancelMsg = vbYes) Then
    DoCmd.Close acForm, "ZooMobile Payment Form", acSaveNo
End If

End Sub

 

[Reese]

And, yes, I know that Access will automatically save things but I want a way for the user to cancel mistakes.

I may just scrap the whole thing and let people know that it automatically saves. What confuses me is how what I had used to work but no longer does. And I've been using the database for about 4+ months.

 

[Reese]

And, besides, what's the point of having a command button that saves the design of the form? The general users won't need that--only the manager of the database design, who can save manually in Design view. And a save button will only confuse the end users.

 

[Reese]

I think I found a solution:

http://visualbasic.ittoolbox.com/gr...close-form-and-dont-save-record-edits-3704643

 

[JHB]

Here is the database with the 3 requirements.

 

[Khalid_Afridi]

I think you want the users to save or discard data to database?
Access has a default behavior to save the current record automatically as soon you edit it and then move to another record. This happens with a bound form to a table / query or directly in a table or query.

In your case, if you want not to save any data to table, than you have to design your forms something programaticaly using VAB and unboud forms. You can provide a button with yes/no msg so that user can decide either to save the record or not.

A sample design:

1: Create a unbound form with whatever your text you want to save to database
2: On your button click..

Private Sub CancelCmd_Click()

Dim CmdCancelMsg As String
CmdCancelMsg = MsgBox("Cancel update and close without saving?", vbYesNo + vbDefaultButton1, "Cancel Update?")
If (CmdCancelMsg = vbYes) Then

'code to save your data to the database
'INSERT STATEMENT goes here....

DoCmd.Close acForm, "ZooMobile Payment Form", acSaveNo
End If

End Sub

 

[Reese]

Khalid-- Thanks for the suggestion, but I already have about 35 forms (some with up to 5 subforms in each) that are already bound to tables. That's too much to redo.

JHB-- Thanks for the help with that sample database. The code is very similar to the solution that I found in that other forum thread I linked. What is the purpose of the Me.Dirty = False? That's the only part I don't understand the function for.

Thanks.

Looking back and just trying to understand in hindsight, do you guys have any idea why my initial techniques worked to save/cancel data in the past but suddenly stopped working in that regard? I'm going to replace the code, I'm just trying to understand why/how it happened.

 

[JHB]

Looking back and just trying to understand in hindsight, do you guys have any idea why my initial techniques worked to save/cancel data in the past but suddenly stopped working in that regard? I'm going to replace the code, I'm just trying to understand why/how it happened.

If you have some backup of the database, try and see if it still worked to save/cancel data, and if, check if the macro is the same as you have now.

 

[Reese]

Sigh... my backups seem to have the same issue. I guess it will remain a mystery. So long as my current solution works--and doesn't randomly stop working--I'm happy.

Anyway, thanks for the help!