Programatically changing code behind a button/label

[Mike375]

There is a thread on the Macro forum about using a macro to change the macro that is the default macro behind the label or code and that has prompted me to start this thread.

The following code and macro changed the default macro

With Forms("MasterForm").Controls("Command1571")
If .OnClick = "Macro1477" Then
.OnClick = "Macro1162"
End If
End With

And a SetValue macro action

Item [Forms]![MasterForm]![Command1571].[OnClick]
Expression "Macro1162"

I got the code from this site

http://msdn.microsoft.com/en-us/library/aa172963(office.11).aspx

The only way I can think of to change the code behind a label or button (and hence my question) would be to have the code in a module and a macro with a RunCode action line.

If the macro name to be inserted could be taken from a text box on a tabular form or a combo.....and descriptiopn of what it did...then I think that would be neat.

But would there be someway that the actual code behind the button or label could be changed?

 

[CyberLynx]

In my opinion, if you want to dynamically change the Code for a control during runtime then the best way to do it is by using Functions.

For example, let's say you have code now contained within the OnClick event for a Command Button named CmdButton. And for argument sake let's say this code is merely:

MsgBox "Hello World..."

Put this code into a Function procedure directly into the Form's Code Module, let's call the Function CmdFunc1:

Private Function CmdFunc1()
   MsgBox "Hello World..."
End Function

Now create yet another Function procedure, again within the very same Form Code Module and let's call it CmdFunc2"

Private Function CmdFunc2()
   MsgBox "Hi Mike, how are you?"
End Function

Now, In the CmdButton Command button's OnClick property window slot enter:

=CmdFunc1()

Now somewhere in code, perhaps from yet another Command Button named CmdButton2, enter this line of code:

Me.CmdButton.OnClick = "=CmdFunc2()"


Run your Form. When the Form Opens press the CmdButton command button. You will get a message box that states:

Hello World...

Now select the CmdButton2 Command button you added to Form. This button will change the the OnClick Property value for CmdButton and point it to fire the other Function we created.

Select the CmdButton again. Now you will get a message box which states:

Hi Mike, how are you?

Done deal.....

.

 

[Mike375]

That looks like in principle the same as having a macro with RunCode and the changing the macro that is attached to the OnClick even or whatever.

I was wondering whether it was possible to change the actual writing that is in the code attached to the label or button.

Something I am thing of doing and would be interested in hearing of any better way or ways is to change the way I have Access/Word/Bookmarks etc done.

At the moment I have pairs of labels on a form for each type of letter that will be done. One label opens the Word doc for editing and the second label fills the bookmarks, prints and a few other things.

What I just made was some modules that have the same code as behind the labels that print the letters. I then put each of these into a RunCode on a macro. Then on a continuous form one field has the letter description and on another field (not visible) is the macro name. Click on the letter description field and the macro runs the block of code in the module. I could then add the same sort of thing to the form to open the appropriate letter for editing etc.

 

[Banana]

While you indeed can do editing of modules on the fly, it's just not practical for what you are trying to do, and is more likely to cause pain and misery.

Functions, as CyberLynx showed, actually makes much more sense, and if the content of letter can change, then that content should be in a table and the function can extract from that table or whatever. For continuous form, the label/button should be able to reference the current row's value that you can then use to govern which function should be executed.

 

[Mike375]

What would be really great, at least for me and what I do, would be if you could use a Report to match the formatting, flexibility etc of a Word doc containing linked queries.

I think I must do things the wrong way with reports because everyone else seems to use them, judging by the activity of the Report forum of the site.

With a Report how do you get around the problem of only having one or two records and then attaching it to a letter that is sent to the person?

 

[Banana]

Well, if it's a letter with only few differences here and there, then I would say that Mail Merge in Word is easier. You can do this with bookmarks, but Mail Merge can also work for non-letter application and you can define how each column should be associated with a given field.

 

[Mike375]

Well, if it's a letter with only few differences here and there, then I would say that Mail Merge in Word is easier. You can do this with bookmarks, but Mail Merge can also work for non-letter application and you can define how each column should be associated with a given field.

I use Bookmarks for the Access data and of course the linked queries. 99% of the letters I would do for people who use what make would do are single letters, usually a response to a request from a client etc. I do have the letterhead done via bookmarks as well.

Linked queries with the headers blank and correctly formatted in Word look great because it looks as if someone typed in the details.

When we do more than one letter then it might be 10 or so that are the same. Then we just go to the client from the index and click Print Letter 1 etc.

The main problem with the system I use is you can have problems. Scansoft as part of scanning program on the computer needs to be removed from the Word add on. And of course there is Vista