2007 - Setting Format property in a datasheet at runtime -- prompt to save changes?

[nsbecker]

Hi - I've got an interesting one here:
I've got an mdb file that some users run in Access 2003 and some in 2007.
I have a Localize function that is called on the open of each form to set the captions and formatting to match the user's locale. One thing that I commonly do is set the Format property of text boxes that display date/time values to match the user's date/time preferences (i.e. m/d/yy or d/m/yy). It is not always an option for me to use Access' built-in "Short Date", "Medium Date", etc, formats - for instance, my most common format string is m/d/yy h:nn AM/PM).

If I have a form with a subform that is displayed in Datasheet mode, when I set the Format property of a textbox in the subform, then when I am done with the form and close it, I get a message asking me if I want to save the changes to my main form and my subform, even when running under the Access 2007 runtime.

Anyone seen this before, or have any suggestions for me? Attached is a sample db - open Form2 to observe the behavior.

Thanks!!
Nathan

[nsbecker]

just a quick follow up-

I realized I didn't explicitly say this in the detail, but the prompt to save changes only occurs in Access 2007; I'm running Access 2007 SP2.

A little more testing reveals that this happens on ANY form (whether it is a subform or not) that has the default view of Datasheet. So, setting the format property of a textbox (or other control) programmatically when the default form view is Datasheet is causing Access 2007 to issue a prompt to ask the user if he/she wants to save changes to the form when the form is closed.

I realize that I could add the acSaveNo parameter to a DoCmd.Close call - however, this is not great for the development environment (although I could add a switch related to if a developer is working on the system...), and I'm not sure how to handle forms that open as datasheets (and only have an X in the upper right) or subforms that get closed by the "parent" form, and thus don't have a DoCmd.Close call of their own.

Any suggestions would be appreciated! And I apologize if I led anyone down a bunny trail by not explicitly stating that the behavior only occurs in Access 2007!

Nathan

[ChipR]

I must be missing something. Why does your form have a Default View of Datasheet if you are using it to display text boxes?

Also, I can't at the moment think of a reason not to use the acSaveYes parameter.

[NeoPa]

Surely TextBoxes are used in Datasheet view too Chip. No? I'm not completely sure as I never use it but I'd be very surprised if it work so much differently from other views.

As for saving the form every time a user is using the database, logging a completely misleading date for when the design of the form was last 'changed' is just one of many issues I can imagine might result from this.

Unfortunately, I can offer nothing either, relative to the vagaries of Access 2007 on this point Nathan. I certainly understand why you would be very careful about allowing such updates however.

[ChipR]

@nsbecker
Are your users getting a compiled version of the database with the source code removed? With the default acSavePrompt, a .accde file (or equivalent 2003) in runtime will just close and not give users this option, while the development version would.

[nsbecker]

NeoPa - right, it's not desirable to have the users always save the update. And I too was surprised that it works differently. My hunch is that it has something to do with Access 2007's enhanced datasheet properties (such as allowing users to add a totals row, etc) - i'll bet that the Format property is "special" when the form is in datasheet view, and thus by changing it, Access is thinking this must be a "major change" to the form design and therefore prompting the user if the change should be saved.

ChipR - chances are using an mde (2003 equivalent of an accde) would solve the problem. We have hesitated to make this move across the board b/c some of our customers like the ability to create custom queries and reports, etc. We probably could investigate some alternative methods of delivering our application that would still allow for this function, but this is not a small decision.

Thanks!