Public Function to assign a macro to all buttons with a certain name

[isaacski]

Hi Everyone!,
I'm not even sure if this is possible but I'll ask anyways. I have built an access application that contains a set of buttons along the top of every form that serve as navigation.* These buttons each perform the same function on every form they are on. (menu opens the main menu, etc) I have database macros to assign each button the same function but I still have to go through each form and manually assign them. I was wondering if it was possible to define a public function that on db open will look for all buttons with a certain name and assign them the macro. (so all buttons called cmdmainmenu will have the OpenMainMenu macro assigned and so on).

Is it possible? If yes, thoughts on what the vba would look like?

I appreciate any help!!

Kim


*Before you go there, I have already tried the navigation form and set all forms as subforms. This slowed the db down so much that it was no longer efficient. So now my solution is to have the navigation buttons at the top of each individual form which helps in speed but involves a lot of tedious programming.

 

[jdraw]

I don't think so, but it's more guess than fact.
As I see it, buttons on form are private to that form, so events to private buttons would seem private - not public.

Another approach may be to build a form with the buttons and events you need, then make multiple copies of the base form.

Back to the original question, it may be possible to open each form in design mode and set the properties as you need them via a vba procedure. If it was a function, you could try to do this with each form involved, passing in the form name.

If you try it, please let us know how you make out.

Good luck.

 

[isaacski]

sounds like it save more time to just go through every form and assign each macro accordingly If I try something that works I'll definitely post it!

Thanks for the reply

 

[jdraw]

I did some googling since your post.

These are vba not macros

http://www.pcreview.co.uk/forums/change-form-design-via-vba-t2461346.html

http://www.access-programmers.co.uk/forums/showthread.php?t=146563

And there are others.

 

[isaacski]

It will take me a while to figure out what is being said in these... thanks for the suggestions! I did some testing myself and found that really what I'm trying to do is create a set of vba instructions that I can call in a form OnLoad event. I tested it on the individual form and I can put the vba code Me.cmdmainmenu.OnClick = "OpenMainMenu" into the forms onLoad event with success. Now I just need a way to define it and call it rather than copy and paste the same code for every form's onLoad event. I am more of a visual person so I'll try to illustrate what I'm thinking below which will NOT have correct code for the function because I don't really know if its even possible or what the vba would be.

I hope I'm still making sense...

Instead of having this code on every form's OnLoad event.....
Private Sub Form_Load()
Me.cmdmainmenu.OnClick = "OpenMainMenu"
Me.cmddeliverable.OnClick = "Opendeliverables"
Me.cmdrostermgt.OnClick = "OpenRosterMgt"
End Sub

Have something like
Function assignmenubuttons()
Me.cmdmainmenu.OnClick = "OpenMainMenu"
Me.cmddeliverable.OnClick = "Opendeliverables"
Me.cmdrostermgt.OnClick = "OpenRosterMgt"
Exit Function

And then in the form's that have the nav buttons
Private Sub Form_Load()
Call assignmenubuttons
End Sub

?????????? possible?

 

[Galaxiom]

If the same buttons are used everywhere then put them on one form and use a subform to show the rest of the controls.

Use the form's Load Event to change the SourceObject property of the subformcontrol to the appropriate form. This can easily be done using the OpenArgs argument of OpenForm command to pass the name of the subform to be displayed.

Open separate instance of the button form if you need multiple forms open at the same time.