Quick Trick: Copying Embedded Macros from one Property to another

Now that we spent all this time talking about templates, how about we put to use something we learned about it in a totally unexpected way? Remember that we talked about how embedded macros are really represented as SaveAsText/LoadFromText alongside objects?

Unlike VBA code associated with event properties (e.g. OnLoad, etc) and like other ‘simple’ properties (such as the Default view Property), you can copy an embedded macro from a control/form/report’s property to another (or the same) control/form/report property.

The trick is that for each event property off of form/report/controls, there is a “shadow” property that contains the embedded macro for that event property. For example, the OnLoad property has a dual OnLoadMacro property that contains its embedded macro (if there is one, otherwise it’s empty).

So, it’s very easy to copy embedded macros around by simply writing code that looks like this:

Forms(0).Controls(“foo”).OnLoadMacro = Forms(1).Controls(“bar”).OnOpenMacro

Of course, if you look at what actually is in the .*Macro “shadow properties”, you will find out that it is the same output that a DoCmd.SaveText acMacro, “foo”, “Macro1” has. This means that you can easily load/save/copy standalone macros to/from embedded macros.

Comments (5)

  1. Alan Cossey says:

    Very clever. Thanks for pushing so much info our way.

  2. unsurprisingly says:

    Your June or July pointer to the office 2007 beta 2 test drive looked kind of interesting to check out, but here’s what you get when you try to run it from win xp fully updated w/ie 6:


    Your Browser or Operating System is not compatible with the Test Drive System. The Test Drive System supports the following:

       * Windows 98, 2000 and XP

       * Internet Explorer 5.5 and later

  3. ThirdOfFive says:

    Do you have Windows XP with SP2 installed? I believe the requirements mention that you need SP2.

    Here are the requirements:


  4. grovelli says:

    Does the DoCmd object have a SaveText method? Because on Access VBA help I can only find Save.

  5. ThirdOfFive says:

    The DoCmd SaveAsText and LoadFromText methods are undocumented and hidden from VBA object explorers. Just type DoCmd.SaveAsText on your VBE immediate window and you will see the arguments, etc.