AlternateBackColor change in vba causes form save on close

[swas]

Hello,

I'm using a subform within a couple of different parent forms, all working fine.

I wanted to tweak a little bit of presentation for the subform depending on the parent, so in the respective parent Form Load event I use code similar to the following:

With Me.frmMySubForm.Form
.RecordSelectors = True
.NavigationButtons = True
.AllowAdditions = True


.Section(acDetail).BackColor = vbRed
.Section(acDetail).AlternateBackColor = vbBlue


End With

This code works fine. My only problem is the last line where AlternateBackColor is set / included. With this line of code in, when the form is closed the user is asked "Do you want to save changes..." with all forms listed (The parent + any subforms, in fact any other forms open). If not included, all works normally (ie. No 'Save' message).

It is the same even if used as a .accde or .accdr database file (Which I thought the user couldn't save form changes).

If 'Save' is selected, then the new settings are saved against the form meaning other places where the subform is used then don't display correctly. I could set the values there too, but still faced with user question whether to save when that form is closed.

AlternateBackColor was introduced with A2007 - could this be a subtle bug?


We're only talking 3rd world problems here, but it is irritating me. At the moment the only solution I see is avoiding setting AlternateBackColor in code?


Thanks

swas

[jwhite]

Set a command button for the user to close the form. On click of the button, Docmd.Close Me.Name, acSaveNo. And in the On Close event of the form, call the same procedure instead of letting MS-Access close the form automatically.

[Micron]

That is a weird one. I always thought one had to open a form in design view (even if in code) in order to get that prompt. No other form property that I've ever set had the same effect, but after testing this one, I get it too. It doesn't seem to matter what even on which form it's on, the result is the same. I tried others because the subform opens first, then the main. All I can think of is calling a sub on form close that turns off warnings, then closes the form, then turns warnings back on. Not the safest thing to do for sure. Maybe someone knows how to trap for the design state - something like the Dirty property of a form.

I considered just using a button click event, but assumed you probably allow the form control box, which would allow the user to close via the X button at the top right and bypass the code.

Edit: I would consider setting a flag when the alternate color is changed and base the decision to prompt for a save according to the flag value.

[June7]

How do users close the form? By the X close button?

As jwhite said, disable the X close button and provide a custom button to close (jwhite missed one argument in the code):

DoCmd.Close acForm, Me.Name, acSaveNo

If this is a multi-user split database, each user should be running their own copy of frontend.

Several properties can be manipulated in runtime that would trigger that prompt.

[swas]

Thanks for the thoughts.

Command button close - Works if closed by the button, but I have 60 + forms and don't really want a 'special' close for one. If the button code is called from the form close event it bombs with a 'Close action cancelled' error, and prompts to save anyway.

Tried fiddling using OnUnload event (Unload - Deactivate - Close sequence), setting SetWarnings = false or Echo = false. Neither works. In fact the 'Save' question is asked before OnUnload fires so even cancel=true flunks, or anything in the OnClose event.

At the moment the choices I see are command button close, or don't set AlternateBackColor from code...

Thanks again

swas

[swas]

Sorry - yes the form close button is used (X)

[June7]

Recommend you change all 60 forms. Shouldn't take all that long, maybe a day out of your life - copy/paste to the rescue.

I always disable the X close in a code heavy db.

Also, to revisit my other point - is this a multi-user split db with each user running their own copy of frontend?

[swas]

Thanks June7.

I'm not sure why the preference for non X close - this is probably the first time I am caught out with an unsolvable (minor) issue. I'll think about that one...

Yes it is a split db, again not sure how this may affect the problem.

Please don't interpret my not seeing your logic to suggest it isn't the correct advice either...


Thanks

swas

[June7]

The issue you encountered was probably the main reason I went with disabling X close. I have many forms with code that modifies properties during runtime and I don't want that prompt to popup.

So to eliminate the popup, I use custom button, disable the X close and right click shortcut menu and function keys and hide/customize the ribbon.

Also, there are usually other buttons on form so to me it is consistency in design.

[Micron]

It probably wouldn't take you too long to write something that could enumerate through all your forms and disable the x button, but I don't see that as a viable solution when you can still right click on the form and close it from the context menu. I don't recall if it was ever said, but I suspect this is a continuous form. As a hack, consider putting a textbox as wide as your detail section, transparent border and set it to the back of the other controls. Make it visible if it meets the criteria. You should not get prompted for changing this property. I suppose it could work as well for a datasheet view.

Or maybe see if this is of any interest to you, but it looks like a lot more work than a simple hack
http://allenbrowne.com/ser-69.html

[swas]

Thanks for the thoughts.

We're creating a lot of work to solve a small problem. that's not to say it isn't good programming logic in the suggestions. With shortcut menu's etc... covered there could still be unforeseen times the issue occurs (If the user closes the main db window, a pc restart, application.quit used somewhere etc...).

If my only issue is the couple of forms where the issue occurs, perhaps it would be simpler to create a couple of copies of the subform, set the AlternateBackColor in each, then just use the appropriate one to display.

Appreciate the help.

swas

[June7]

I stated in earlier post:

Several properties can be manipulated in runtime that would trigger that prompt.

However, I cannot remember which they would be. But there has to be a reason why I use that argument in my code. I do remember changing code to include the acSaveNo and the only reason I would have done that was because I was getting that prompt and/or seeing changes in properties after form closed. So my answer was based on old weak memory and could be faulty.