Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Oct 2021
    Posts
    13

    Question Embedded Macro Disappears from On Dirty Event After Closing and Reopening Form

    The title says it all.


    I create a macro in an On Dirty event on a combo box, the macro performs exactly the way I need it to on the form, then after closing the form and opening it again the On Dirty event field is blank... I triple-checked that I hit the 'save' button at every. step. of. the. way.
    Please help.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    Is the "event field" the event column in the property sheet? You get no warnings or messages about not being able to save design changes?
    Most of us who help here don't use macros so you might have to copy, zip and post your db here - especially since I tried saving a macro for this event and the property sheet shows [embedded macro]. However, it doesn't actually run, which is odd. Then again, I've never used the Dirty event on a control, just forms.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,480
    Have you tried an external macro?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  4. #4
    Join Date
    Oct 2021
    Posts
    13
    It is, and none.
    The purpose of that macro was to perform an action based on the old value of the control, and I realized (sadly, after posting my question) that I might have better luck with programming what I need done. I'll have to learn how to do it sooner or later, anyway. But that's not the topic of this discussion, so I guess I'll just cut my losses here.
    Appreciate the quick response!

  5. #5
    Join Date
    Oct 2021
    Posts
    13
    I don't think so. I'm not sure what that is.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,480
    I cannot replicate your problem, but then I answer yes to the Save option when I close the embedded macro?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,480
    It is a macro that you can see in the Navigation pane.
    One that you would use the Save As option on, in the Macro designer window.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    I think WGM means creating a macro from the ribbon. Anyway, just for fun I created an event procedure instead and it ran fine when the control was dirty. No idea why the macro assigned to the control didn't run but I guess it's just another reason for me to not use them.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,480
    Quote Originally Posted by Micron View Post
    I think WGM means creating a macro from the ribbon. Anyway, just for fun I created an event procedure instead and it ran fine when the control was dirty. No idea why the macro assigned to the control didn't run but I guess it's just another reason for me to not use them.
    My embedded macro would run, and would stay attached to the event?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  10. #10
    Join Date
    Oct 2021
    Posts
    13
    Quote Originally Posted by Welshgasman View Post
    I cannot replicate your problem, but then I answer yes to the Save option when I close the embedded macro?
    Yes. That's what I did.

  11. #11
    Join Date
    Oct 2021
    Posts
    13
    Quote Originally Posted by Welshgasman View Post
    It is a macro that you can see in the Navigation pane.
    One that you would use the Save As option on, in the Macro designer window.
    I had not tried that.
    I have tried this just now from the window where I'm creating the embedded macro, but the 'save object as' option is greyed out.

  12. #12
    Join Date
    Oct 2021
    Posts
    13
    Just to provide this thread with some semblance of a cathartic ending, and I hope I'm not breaking any (major) rules here, the macro that I was trying to embed had the following purpose.
    Form control #1 contains unique text (selectable options from a drop-down combo box). Blank by default.
    Form control #2 contains dates. Blank by default.
    Control #2 must change to current date when control #1 gets a value, but only if the starting value is blank.
    So, from blank to something in #1, make #2 show today's date. From something to something in #1, don't do anything in #2.
    Could you point me in the right direction on this forum, please?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    Can probably provide a code solution if you can figure out how to start one. In form design view, click on 1st control, click ellipses (...) for AfterUpdate in property sheet. If you're presented with an option list, choose code builder. Post what you get from doing that and provide the name of the 2nd control in your post.

    EDIT - is the combo bound to a field? If not, this might be more difficult. In that case, will probably also need a BeforeUpdate event for the combo.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  14. #14
    Join Date
    Oct 2021
    Posts
    13
    Quote Originally Posted by Micron View Post
    Can probably provide a code solution if you can figure out how to start one. In form design view, click on 1st control, click ellipses (...) for AfterUpdate in property sheet. If you're presented with an option list, choose code builder. Post what you get from doing that and provide the name of the 2nd control in your post.

    EDIT - is the combo bound to a field? If not, this might be more difficult. In that case, will probably also need a BeforeUpdate event for the combo.
    Both controls are bound to fields in a table. It is the same table.
    Control #1 is [OrderID]
    Control #2 is [Assigned]

    Here's what I see after I've followed your instructions:

    Code:
    Option Compare Database
    
    Private Sub OrderID_AfterUpdate()
    
    
    End Sub
    
    
    Private Sub OrderID_Enter()
       Forms![Supply Order Control]![OrderID].Requery
    End Sub

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    wrong event - BeforeUpdate, not Enter. Or is that one that you needed anyway, because I see it already has code. Haven't decided yet if BeforeUpdate is needed so might as well wait on that. One more thing - there is the possibility that control 1 can go from something to nothing by way of deletion. Then what happens to #2? That should be accounted for?

    My ride should be here in about 20 minutes so I probably won't get back to this until this afternoon. However, there are lots of people who could chime in with the code as it's quite simple when you know how. For their benefit I'll say I was thinking to use OldValue property of combo since it's bound and we can check if it contained anything or not when it is updated.

    Suggest you add Option Explicit under the other option statement. You turn this on by default in the vb editor options "always require variable declaration". That it is not the default is really quite stupid if you ask me.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-03-2017, 08:17 AM
  2. Replies: 7
    Last Post: 08-07-2014, 06:07 PM
  3. Dirty event not activating on Access 2007 form
    By newbieX in forum Programming
    Replies: 4
    Last Post: 06-17-2014, 12:57 PM
  4. Embedded macro on form
    By trout in forum Macros
    Replies: 7
    Last Post: 07-09-2013, 01:13 PM
  5. Replies: 3
    Last Post: 06-02-2011, 07:40 AM

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