Close with 'acSaveNo' option 1

[KerryL]

Trying to close a form with a command button in a way that any data in the fields is not saved to the table.

In the OnClick event for cmdClose, I have the following code:

[BEGIN CODE]
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

DoCmd.Close , , acSaveNo

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub
[END CODE]


When I created the OnClick code, I used the 'acSaveNo' option, thinking the data wouldn't be saved when cmdClose is clicked. But the data is being saved anyway.

What's the easiest way to close a form with cmdClose without the data being saved?

 

[RoyVidar]

Hi!

This question is a bit larger than perseived at first glance. But to start, the acSaveNo option, means that you don't save any design changes of the form, and has no influence on the record.

- the first and "easy" answer:
Using Me.Undo before the close, would be a better choice.

BUT - this will not prevent the user from saving the record, which might be done thru lot's of oparations such as: moving to another record, hitting SHIFT+Enter (save), closing the form thru other methods than the close button (CTRL+F4, CTRL+W, ALT+F4, and probably more)

To be sure that changes are not saved, the forms before update event might be used. It will fire whenever an attempt to save the record is made. The following little example, placed in the forms before update event, would ensure that no changes whatsoever would be made to the forms recordset. To allow for some changes, you could for instance add a message box and an if statement asking the user if changes are ok...

[tt]if me.dirty then
me.undo
end if[/tt]

Strictly speaking, testing for a dirty form (form having unsaved changes to the record) isn't necessary, but habits...

HTH Roy-Vidar

 

[KerryL]

Roy,
I guess I don't understand the form's Before Update event. (I looked in Access Help but it took me to come macro help instead.)

Can you explain it so I understand what the 'dirty' code is doing?

kerry

 

[KerryL]

Roy,
The 'dirty' code works. Unfortunately, it works too well.

On the form is a sub-form, and as soon as I tab or click to a subform field, all the data I've entered in the main form fields disappears.

Any suggestions?

 

[RoyVidar]

Hi again!

Well, to quote (higlighting some elements): The following little example, placed in the forms before update event, would ensure that no changes whatsoever would be made to the forms recordset. To allow for some changes, you could for instance add a message box and an if statement asking the user if changes are ok

There are several approaches. One of them is to to use what I stated (also in the quote):

[tt]if me.dirty then
if msgbox("wanna save",vbokcancel,"Save?&quot = vbcancel then
me.undo
end if
end if[/tt]

This would give a message box whenever you try to save the record (and place your cursor within the subform).

You'll have to decide whether to do such testing (and having message boxes...) or rely on that the users don't posses the knowledge of closing the form by other means than your close button;-)

For the last thingie, you might try the first suggestion in the first post (and remove code from the before update event), put the line me.undo on the line before docmd.close

Roy-Vidar

 

[KerryL]

I added me.undo to the cmdClose button code, but the record still ends up being written to the table. Here's the code behind cmdClose:

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

Me.Undo
DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub




This is growing a bit frustrating. All I want to do is close the form without any data in the fields being saved to the table. Seems like it should be a bit easier than this.

 

[RoyVidar]

Hi again!

I think the code is working, but perhaps not the way you'd like;-)

If the record is written to the table, that means it's saved before you hit the close button.

One might say theres different states the current record of a forms recordset (bound forms) can have. Me.Undo will work to undo unsaved changes.

But if the record is saved to the table before you invoke the close, then there's nothing to undo.

Are you looking at Deleting the current record before you close?

Roy-Vidar

 

[KerryL]

I apologize for the confusion. Here's exactly what I'm trying to do.

I don't want users to be able to save incomplete records to the table. So I added cmdSave, which does data checks on all the required fields and forces the user to fill them in before cmdSave will actually save the record.

Since cmdClose does a default save, I'm trying to remove the possibility of cmdClose saving a partial record by mistake. Therefore, I don't want cmdClose to save anything on the way out.

IOW, cmdSave is used to save a full record; cmdClose is simply used to exit the form without saving.

 

[RoyVidar]

Not trying to be impolite, but here's previous post in fewer words:

Because me.undo precedes docmd.close, docmd.close does not perform a save in this case.

The record is alredy saved.

Do you want to delete current record?

Roy-Vidar

 

[KerryL]

I'm sorry. I've tried a few things, so it's getting a bit confusing. I understand I could avoid this problem by using unbound objects, but admittedly, my coding skills are not great so I was trying to keep it as simple as possible.

My objective is to avoid having the cmdClose save a partial record. The me.dirty option would work perfectly except for the fact that there's a subform on the main form. Let me ask if something like this would work for the OnClick event for cmdClose:


If Me.Dirty Or If Forms!sfrmName.Dirty Then
If MsgBox("Wanna save?", vbYesNo) = vbYes
[Perform Required Data Checks]
DoCmd.Close
Else
If MsgBox("Click 'Undo Changes' button to clear fields", vbOKOnly,) = vbOK
Exit Sub
DoCmd.Close
End If
End If

Exit_cmdClose_Click:
Exit Sub


What I'm attempting to do is this:

If the main form or subform is dirty, then ask the user if he wants to save. If yes, perform data checks on required fields, then close/save.

If user says NO to save, display a msg box that says "Click Undo Changes button to clear fields." The user then must clear the fields in order to close the form without saving.


I'm sure there are better ways to accomplish this, but as I said, my coding experience is limited so I'm trying to allow Access to do as much for me as I can.

Thank you for your patience.
Kerry

 

[RoyVidar]

Hi again. You say that if the main or sub form is dirty, then you want to ask the user if he wants to save.

I haven't been very succesfull in explaining access behavior before, so lets make a little example. You enter a record on the main form, then go to the subform and enter three records. After that, you enter another record in the main form and another five records in the subform. In the fifth record of the subform, the little pencil icon is shown on the record selector indicating unsaved record.

If you perform a dirty check on both the main and sub form, it wil be true. So you ask the user whether he wants to save. Well the user selects no, then what.

The first record in the main form with it's three records in the subform are alredy saved, and can't be undone thru a me.undo. Unless you use some event on the controls, or perhaps the forms before update you have no control whatsoever on whats saved, partial or not (well, exept table level/control level validation rules, required property of the field).

Ok, back to the last record on the main form with it's five records in the subform. The record in the main form is alredy saved (an observation you made yourself when experimenting with the forms before update event "On the form is a sub-form, and as soon as I tab or click to a subform field, all the data I've entered in the main form fields disappears.&quot. The four previoud records in the subform (only the fifth record has the "unsaved"/pencil) are alredy saved.

So if you issue an Me!MySubForm.Form.Undo and Me.Undo, only thing happening, is that the fifth record in the subform get's undone.

If you want control over whats saved in the main form, you will have to trap for it before you enter the subform, else the record is saved without any checks.

To check for this, you could use code either on the individual controls to prevent empty/wrong data or using some form event. Using events on individual controls might be a bit dangerous, because a "pesky" user might avoid entering anything in a control by just clicking in another control, so you'll probably need to use a form event. The only "bombproof" form event to use if you want to prevent the saving of a record given certaion validations, is the previously mentioned, and tested, before update event of the form.

Ok - if the intention was to "undo all changes" when the user selects no to cancel, then you'll have to delete the record(s) that's been entered. Should that be only the current record in the main form and the assosiated records in the subform or should it be all the records in the main form entered in current session with assosiated records in the subform? If the latter, how would you determine which records where entered now?

So - back to my initial reply: "This question is a bit larger than perseived at first glance"

All of the above is doable in unbound form (but as stated by MichaelRed in the other thread It is a fair amount of keyboard activity) and should also be doable in bound forms. But there are a lot of considerations to make when coding.

When I started working with Access, a collegue gave me some advice to handle situations like this. You mustn't consider this a rule, but I think it would ease up on some of the issues involved here.

"Never allow the user to enter data in both the main and sub form when using such structures and datavalidation is needed"

How - use a single form to enter the "main form data", use the level of validation needed (for instance the before update of the form). When the record is verified and saved, allow the user to open another form to enter the "sub form data". In such cases, I often use a main/sub structure so that the user have all information regarding the "main recor" available, but I leave the main form controls disabled/locked or similar. I think this appraoch might also be more agreeable considering your own description of your coding skills. Again, it's not my intention to offend.

Roy-Vidar

 

[Edski]

Sorry to but in here.
Would setting all the 'Required' properties for every field to 'Yes' in the Table design help?

 

[KerryL]

Roy,
Thank you very much for your detailed explanation. I better understand the larger issues at work here. And don't worry, you haven't offended me in any way; I admit that coding is notmy strong suit--that's why I use Tek Tips so often.

I do understand your concern over how the Undo command would know which record in the main or sub form should be cleared, etc. However, the form/subform in question is really only dealing with one customer record. The main form has name/address/etc and the subform lists organizations the customer is associated with. So although there could be multiple orgs selected in the subform, all the data the Undo command would effect is tied to a single customer record. I'm sorry I didn't make that clear in previous posts.

FWIW, I was able to create some cmdClose code that seems to be doing what I need it to do. It checks to see if the form or sform is dirty. If not, it closes. If yes, it asks the user if he wants to save. If no, a msgbox appears telling the user to click 'Undo' to clear the fields, then close. If the user wants to save, it does data checks on reqd fields so he cannot save a partial record.

Here's my code. Not a perfect way to do it, but I tested all the possible scenarios I can think of and it seems to work okay.

=================================
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

If Me.Dirty Or Not MCSC.Form_sfrmContactCats.cboCatID Then
If MsgBox("Do you wish to save this record?", vbYesNo, _
"Cannot Save Partial Record&quot = vbYes Then
'Do data checks
DoCmd.Close
Exit Sub
Else
If MsgBox("Click 'Undo Changes' button to clear fields, then close form.", vbOKOnly, _
"Cannot Save Partial Record&quot = vbOK Then
Exit Sub
End If
End If
Else
DoCmd.Close
End If

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub
====================================

Again, thank you very much for your insight, and especially your patience, in helping me to understand how Access works with Close, Save & Undo commands. I really appreciate it.

Kerry