Open an Access Macro in Edit Mode from VBA

[kjv1611]

I have searched high and low, trying random things along the way, and have not found a solution for this one. I would think it possible, but just that I'm not searching for the right thing.

I want to set up a form where the user clicks a button to edit the macros for another task.

Basically, the way I'm setting it up is that if there is a Macro set up for running a particular function, and that needs changing, the user can click the form button to view and edit the macro without having to search for it in the Navigation Pane.

Is this at all possible? I found a couple of different options for opening the VB Editor, so I figure surely you can do the same with a Macro.

Can anybody point me in the right direction?

So far, I've tried going through the options of Application. and DoCmd., and the only thing I can find even remotely related is DoCmd.RunMacro, but I don't want to run it in this case, I want to edit it.

Thanks for any information,

 

[kjv1611]

I did find one hint at a possible workaround, but no good info on just opening a Macro in edit mode:

http://www.accessmonster.com/Uwe/Fo...-to-Update-a-macro-s-properties-thru-VBA-code

I figure there's got to be a way to do it. After all, you can edit a macro in Access, and you can access it from the Navigation Pane (Access 2007 and 2010) as well as from the "build" button when the macro is selected in the form control's event properties.

 

[kjv1611]

Just tried looking at Application.RunCommand, and nothing jumped out at me from there so far..

 

[PHV]

I can't read your previous URL.
Have a look here:

http://us.generation-nt.com/answer/how-update-macro-properties-thru-vba-code-help-8216552.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks, PHV, that's the same thread, but posted in a different location.

I suppose I should've looked a the replier's name to see that it was Allen Browne, and therefore, it's not likely I'm going to find something different than what he suggested.

 

[kjv1611]

..But.... I keep thinking there HAS to be a way.. I mean, why would Access allow you to open the VB Editor from a line of code, but not open a Macro?

It wouldn't really matter, but I want to make it possible to edit macros this way, since more people are comfortable there than with VBA. Then again, most people around here are really comfortable with either. I so much as mention any of the words, "Macro", "VBA", "Code", "Automation", "Formula" - Excel, "Query", "SQL", I get some level of panic or blank-look from the other person... not everyone, but a large number for sure. So with most people, I try to make myself be pretty basic/high-level in such discussions.

I guess for now, that if there is a Macro involved, I'll just have to have it set where the user can copy the macro name and paste it in the search bar for finding it... maybe I can make Access search for the Macro directly.. seems I did see a cmdSearch or something like that... That's better than nothing.

 

[kjv1611]

Well, I've at least found the DoCmd.NavigateTo Command for the navigation pane... but no clue how to tell it to search.. so far..

 

[kjv1611]

Well, not really what I was hoping for, but I think I may have gotten to something that will be really helpful, nonetheless.

There are lots of mentions of different things with the Navigation Pane, such as this:

http://www.utteraccess.com/forum/Customizing-NavPane-VBA-t1496042.html

So going with that idea (and many other similar), I think that perhaps it'll be beneficial to create several custom groups of objects, and then use the VBA code example in the last link to then show all related objects. That'll make it simple enough to view and modify any macros, tables, queries, modules, whatever, for the particular "group". In my case, it's a particular process.

I've just been trying to find a quick and easy way tell Access something like: DoCmd.OpenMacro,"MyMacroName",acEditView But I can't seem to find anything such the like.
The other idea was to have it say DoCmd.NavigateTo "MyMacroName"... but again, no go... seems it only shows groups, not a specific item within groups... else I wouldn't really need to create custom groups at all. And I CERTAINLY don't want to create a custom group for every object. Yerk!

 

[kjv1611]

Alright, this is getting plain silly, BUT I have found a better solution, I think. I'm not looking to EDIT the Macros in code, but rather to VIEW the Macros in Edit Mode..

So, the main part (maybe the only part... we'll see) that I'm going to do for now is just using this one line of code:
DoCmd.SelectObject acMacro, "MyMacroName", True

Can't believe it took me so long to try it. It was mentioned in this thread which I looked at fairly early on:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0a34dbec-5078-4e16-b6a7-78f62c39f1bf/

What that code does is it activates the Access Window in older versions of Access, I think, OR the Navigation Pane in '07 and '10.

So, the next option I'm considering is using Sendkeys to open the selected macro in Edit mode... I'll test around and think about it.. may just get the code together for it, and just leave it sort of optional for now... I don't think it'll be a big deal with what I'd need to send, but using SendKeys is sorta scary at times.

 

[kjv1611]

Okay... I can select the macro in the navigation pane, but it's not the same as clicking on it to select it... so when I use SendKeys, it does nothing... just sits there..

I want to be able to do this, or something to the extent:

Sub SelectMacro()
    DoCmd.SelectObject acMacro, "mcrHowdy", True
    'Pause (0.1)
    SendKeys ("{^~}")
End Sub

But it never seems to work. I mean, the macro is "selected", but it's not "really selected"... not sure I can phrase that just the way I want... for instance, in Access 2010, if I CLICK on the macro in the navigation pane, the name is bold, and it's sorta a bright orange selection color. If I run the macro, it's sort of a dim orange selection color and the name isn't bold... If I click on it, and then do a <Ctrl>+<Enter> from the keyboard, it opens in design view... if I run the macro, it won't recognize the keyboard shortcut..

Gotta keep trying, though, there's got to be a way..

 

[kjv1611]

Oh... the Pause bit, I was going to delete. I basically was using someone's custom wait function in one test to see if it were a timing issue... but not so., b/c it is same results regardless... and seems to entirely be the TYPE of selection on the item in the navigation pane list..

 

[kjv1611]

And I also tried running the same one line that selects it with FALSE on the end, saying I don't want to see it in the database window... That works IF the macro is already open... so that puts me back at square one, b/c the macro wouldn't be open unless I can find a way to open it - in design mode, not execution... argh!

 

[dhookom]

Help me understand why you even need this functionality. It doesn't make sense to me.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Howdy kjv1611 . . .

I'm in total agreement with [blue]dhookom[/blue]. Allowing users to edit macro's is [blue]a ticket for trouble[/blue] in my book. For what your asking this would typically be a form where the user inputs or selects arguements for a routine.

I'd give this serious 2nd thought if I were you.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Why: I think it would be helpful with some individuals in some circumstances. It would help them, I think; and it would help me.
Usage: Very limited - it won't be in every database, nor likely in databases for every group.
Protection: I have a few pieces in place on this section:
1. Warning messages - can't miss warnings - as to it's danger
2. Controls locked and disabled before reading and clicking a label.
3. An additional "are you sure" sort of prompt

Chances are, none of the current users would even touch it - so it would just sit there for them... However, it'd be there, making it easier for me to do a quick open, fix, go about my business.

And the "security" in place will, I know, only protect against honest mistakes. However, if someone is really wanting to do damage, they'll find a way regardless of whether I use password authentication or anything else. I mean, they could just as easily find the database and delete it.

Thanks for any thoughts.

 

[TheAceMan1]

kjv1611 . . .

[blue]Chances are, none of the current users would even touch it - so it would just sit there for them... However, it'd be there, [purple]making it easier for me to do a quick open, fix, go about my business.[/purple][/blue]

For you makes sense. For programmers makes sense. For users made me cringe. Anyway goodluck with this . . .

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]