Combo Box Pointing to Macros 1

[Mike555]

I'm using Access 2000 and I'm trying to setup a combo box that lists the macros that I have setup in the database. When the user clicks on the combo box the macros should cascade, and then the user should be able to click on the macro which they wish to execute.

Any ideas how to do this?

Thanks.

 

[coco86]

Try something like this:

Enter the following code in the "Onload" event of the form

Dim str As String
Dim mcr As Object

For Each mcr In CurrentProject.AllMacros
str = str & mcr.Name & ";"
Next
Me.Combobox.RowSourceType = "value list"
Me.Combobox.RowSource = str
Me.Combobox.Requery
Me.Refresh

Then, add the following code to the event that should run the macro

DoCmd.RunMacro Me.combobox

 

[Mike555]

Thanks for assisting. I've used your code and the combobox now lists the macro names, but when I select one it does not execute the macro...any ideas?

Thanks.

 

[Mike555]

I also get a message stating "An expression you entered is the wrong data type for one of the arguments" when the form is opened.

Mike

 

[coco86]

Where did you put this line of code?

DoCmd.RunMacro Me.combobox

Is it in the click event of the combobox?

The error message is a little puzzling since I don't get the same message. Have you tried setting a break at the beginning of the Form_Load() procedure and stepping through the code to see exactly where the error occurs? That might shed some light on the subject.

 

[Mike555]

I've found out what the problem was...

The DoCmd.RunMacro Me.combobox statement needed to be set as the After_Update event since it executes the macro after the combobox is updated. After making this change it works perfectly.

One more question--The combobox lists my Autoexec macro, which doesn't need to be listed with the other macros. Is there a way to remove this?

Thanks

 

[coco86]

sure, just make the following addition

For Each mcr In CurrentProject.AllMacros
if mcr.name <> &quot;Autoexec&quot; then
str = str & mcr.Name & &quot;;&quot;
end if
Next

 

[coco86]

After reading someone's answer to another post, I've got another suggestion as well, this is even easier.

Just set the rowsource property of your combobox to this:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name)<>&quot;AutoExec&quot AND ((MSysObjects.Type)=-32766))
WITH OWNERACCESS OPTION;