Results 1 to 14 of 14
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    This is the error I've been dreading... changing the design of a form when an error

    I've been working in forms today and ran across something I wondered about in reports, and maybe have happened there to contaminate my report design.

    So, I have forms and reports with colored headers. They can change with code on open.
    Also, the captions change based on the language being used.
    I also have a Print button that changes the colors to black and white.


    So, I tried printing the form, and got an error (I'll deal with that later).
    If I answer yes to this question (below in the image), my form design gets changed from the original when it started. Not good.

    How should I make run-time changes to a report or form, changing the form/report properties, but not have those changes be permanent if there is an error (especially encountered by a user and they press yes!)

    If the form/report closes normally, then the changes aren't saved to the design, nor is there a dialog box like shown below.
    I maybe doing something wrong when changing form properties? Like I need to do a local instance or whatever Access does in this situation?

    Click image for larger version. 

Name:	220805frmPrint.jpg 
Views:	30 
Size:	91.5 KB 
ID:	48458

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I presume this is during development, not a production app - so click no.

    A possible alternative is to click cancel, return to the VBA window, copy (and for safety paste into a text file) the entire form module, then close without saving. Reopen the form module, highlight all code and replace.

    As for a user clicking yes in a production environment. 1, your code needs error handling so they are never presented with the option and 2. provide the user with a .accde - changes cannot be saved - but if they encounter an unhandled error, the app will just close - perhaps with a message, perhaps not.

    How should I make run-time changes to a report or form
    I presume you mean changes made by code, not you being in the vba editor changing code as it runs - so see my response in your other thread. You have already been advised changing code whilst the app is running is not recommended.

    There are scenarios that can work. For instance a sub of function in a standard module only runs when called. So if a form module calls such a function, you can see the result, make changes to the module and allow the form to call it again should work OK.

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    if your users are closing the object by way of a command button you could also apply the acSaveNo parameter. DoCmd.close acReport, "Your Report Name",acSaveNo
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  4. #4
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    While running the form/report, not while coding.

    Quote Originally Posted by CJ_London View Post
    I presume this is during development, not a production app - so click no.....
    This is in development, but not production. Not being "expert" in behind the scenes Access magic, I'll do my best to explain better.
    I'm talking about using a form / report as:

    1) There are times when you want to write code that makes a permanent change to form or report design or any other object. Like maybe you changed the size of a description field from 30 to 40 and want to go through every object and change it's size or length. And your done, it's permanent.

    2) Then there are times when a user is using the form, and you want to change a property, like the background color of a text box (or in a multi-language enabled app the captions get changed), but you as the developer don't want the color change to be permanent to the design, only for this instance of run-time movie magic. You don't want these changes to properties to persist once the form or report closes out (or an error occurs). Somehow Access knows this is what I'm doing, as the changes to the properties aren't then saved and permanent when the form closes "normally".

    Situation #2 is what happened in my example. It happened once before when I was changing report properties programmatically. It was a couple of days before I noticed that all my captions had spanish and a lot of "waiting to be translated" there, where it should have been in English as designed. I must have hit an error, saw the message like above and thinking I needed to save a change to the report answered "yes".

    I guess I'm asking if there is some explicit way to tell Access that the property changes I'm making during run-time of the form / report are to be temporary and not saved under any circumstance?

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by moke123 View Post
    if your users are closing the object by way of a command button you could also apply the acSaveNo parameter. DoCmd.close acReport, "Your Report Name",acSaveNo
    Yes! I didn't see this msg until I finished my post #4. As I state below, it seems Access does this automatically though in a normal close, but I will be adding a CLOSE button to the form to shut down just the way I need to (if I add fancy stuff later).

    Now, is there a way to explicitly state when a form / report begins, that I won't want property changes saved under any circumstance?

  6. #6
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Does on open of on load make a difference?

    To elaborate more, I've seen code samples that make the kinds of changes I mentioned to a form or report in either the On load or On open events.

    I've searched, but haven't found a good explanation on when to use either.

    Is it possible that my choice to do these changes in "On open" event for the form impacts what could be considered a permanent change?
    In any case, I am still changing lots of temporary form properties as the user does different things during data entry.

    Update: So I thought, I'm going to search again on this open vs load issue and found this:
    vba - Difference between the "On Open" event and the "On Load" event for a data-entry Form - Stack Overflow

    It would appear that I would be better served making the changes to the form properties in the On load event. Agree?
    Last edited by twgonder; 08-06-2022 at 07:03 AM. Reason: update

  7. #7
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by CJ_London View Post
    I presume this is during development,... - so see my response in your other thread. You have already been advised changing code whilst the app is running is not recommended. ...
    I'm sorry for being a numb-nut here, I've got so many things swirling around in my head from different threads, on top of being sleep deprived. I do remember a thread about Access crashing and the advice not to make code changes while the form is still active in view mode. I've stopped doing that. The original post here didn't have to do with that situation.

    What was the other response that you're referring to? Does it apply to the situation I have here (as I may have explained it better since)?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Is it possible that my choice to do these changes in "On open" event for the form impacts what could be considered a permanent change?
    No

    It would appear that I would be better served making the changes to the form properties in the On load event. Agree?
    Like all things, it depends. There are a number of events that fire when a form is opened, which one is right to use depends on what you are doing. In the case of changing properties for the time the form is open and no reference required to the recordset then Open is fine, otherwise use load.

    You must have come across these links before
    https://support.microsoft.com/en-us/...86553682f9#bm3
    https://docs.microsoft.com/en-us/off...cess.Form.Load

    What was the other response that you're referring to?
    here https://www.accessforums.net/showthread.php?t=86431
    Does it apply to the situation I have here (as I may have explained it better since)?
    I think it does, otherwise I wouldn't have suggested it, you may disagree

    Now, is there a way to explicitly state when a form / report begins, that I won't want property changes saved under any circumstance?
    set a flag for the form - but up to you to avoid any system 'save form' messages

    2) Then there are times when a user is using the form, and you want to change a property, like the background color of a text box (or in a multi-language enabled app the captions get changed), but you as the developer don't want the color change to be permanent to the design, only for this instance of run-time movie magic. You don't want these changes to properties to persist once the form or report closes out (or an error occurs). Somehow Access knows this is what I'm doing, as the changes to the properties aren't then saved and permanent when the form closes "normally".
    As I've said before, I provide users with a .accde rather than .accdb if only because I don't want users messing with the form, let alone saving changes. There are other reasons as well - I don't want them messing with reports or modules either for example.

    However occasionally a client requires the flexibility of some small changes such as resizing or even repositioning/hiding a control, reorder columns, change back/foreground colours, font size etc. In part because some users may have a reason for wanting a dark background rather than a light one, or they have poor eyesight, whatever. Some changes apply to all users, some to a specific user

    For them I provide a form properties table - managed by a form. It contains the
    UserFK - left null if applies to all users
    FormName
    objectName (form/header/footer/controlname)
    propertyName (visible, enabled, left, width, top, height, backcolour, etc, whatever properties they are allowed to change)
    propertyValue - string but with control over the type of values that can be entered dependent on the property - some such as colour open a colour chart for the user to select a colour

    There is another table that stores property profiles used with this property manager form.

    In the form open event this table is referenced and changes all properties as specified and uses a query that overrides an 'all users' change with a users change if one exists

    I use much the same technique for my translation code - except I store the phrase (can be more than one word) and its translation and the code loops through all controls and matches on caption - saves storing the word 'Customer' for the 20 different forms where that word is used. And for developer changes - if whilst looping through the code if can't match on the caption, it fetches a new translation from translate.google or similar and adds it to the translation tables. So first time developer opens the form, translation is automatic. Obviously requires an internet connection for the initial population.

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Should this close the form?

    Quote Originally Posted by moke123 View Post
    if your users are closing the object by way of a command button you could also apply the acSaveNo parameter. DoCmd.close acReport, "Your Report Name",acSaveNo
    I added this one line to the command button to close, should this close the form? It stays open.
    Is there any more I need to add?

    Code:
    Private Sub cmdClose_Click()
      DoCmd.Close acReport, Me.Name, acSaveNo
    End Sub

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    looks like you are closing a report with the same name as your form. To close the form - same code except acForm rather than acReport

  11. #11
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by CJ_London View Post
    looks like you are closing a report with the same name as your form. To close the form - same code except acForm rather than acReport
    Doh! (Homer style, I guess I need more sleep)

    Now that that works properly, when you work in forms for data entry and close them, is there more than one line of code that you do, to clean up a bunch of stuff? I'm talking about only in closing the form (and maybe report). If it's only one line for DoCmd.Close I'll leave it in the event procedure. But if it tends to grow in complexity, it's better added to the "Forms generic" module now so that all forms can use that standardized feature. As I said, for now I'm working on a simple entry form for testing these ideas and the code, then I'll move them as they work out to my template.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Off the top oh my head

    It’s customary to close and/or set to nothing all record sets /collections/dictionaries that the form code may have created and not closed by exiting a sub or function but not everyone does it and access is pretty good at cleaning house and freeing up memory so not essential

    Another option is a message box for the user to confirm they want to close the form

    and maybe on close you want another form or report to open or get the focus if already open

    you might want to know the status of the form why is it closing- because the user clicked the button? Because of an unhandled error? Perhaps another form has closed it? (which is actually managed in the close event)

  13. #13
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by CJ_London View Post
    Off the top oh my head

    It’s customary to close and/or set to nothing all record sets /collections/dictionaries that the form code may have created and not closed by exiting a sub or function but not everyone does it and access is pretty good at cleaning house and freeing up memory so not essential

    Another option is a message box for the user to confirm they want to close the form

    and maybe on close you want another form or report to open or get the focus if already open

    you might want to know the status of the form why is it closing- because the user clicked the button? Because of an unhandled error? Perhaps another form has closed it? (which is actually managed in the close event)
    Is there a universal close for everything the form might have opened, in case some procedure wasn't programmed properly to clean-up?
    If it's run, you would only want to close the stuff that this form may have touched, not other processes, like a menu that will still be running.
    One thing I envision is adding a terminated date/time stamp to the log of the form. That would be best handled via a sub outside the form class.

    I figure if they clicked close and made a mistake, they can easily start the form again, you can only do so much.
    Yes, it would have to return focus/control to the menu program that called it.
    Does Access do that automatically? I think so from the little menu I got from the translate demo database.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Is there a universal close for everything the form might have opened, in case some procedure wasn't programmed properly to clean-up?
    not so far as I am aware - as I suggested the close event should do it automatically.

    Does Access do that automatically? I think so from the little menu I got from the translate demo database.
    again it depends. you may have your menu form close or be hidden so you would need code to open or make it visible again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 26
    Last Post: 11-03-2021, 09:11 AM
  2. Replies: 232
    Last Post: 04-17-2017, 01:08 PM
  3. Replies: 7
    Last Post: 06-13-2014, 10:41 AM
  4. Replies: 1
    Last Post: 12-19-2013, 02:55 PM
  5. Replies: 5
    Last Post: 12-22-2011, 01:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums