How to call a macro from a combo box?

[jaad]

I have a combo box that can open any form in my database.

this is the macro that makes is work:

IsNull([Screen].[ActiveControl]) - StopMacro
SetTempVar - FormToOpen, [Screen].[ActiveControl]
[CurrentProject].[IsTrusted] - SetValue - [Screen].[ActiveControl], Null
OpenForm - =[TempVars]![FormToOpen], Form, , , , Normal
RemoveTempVar - FormToOpen

and this works great because I often need to open many forms given the activity I perform.

I've tried to set up the same for a macro but Access is telling me that it can't find "MacroToOpe n" (which is the TempVars name that I gave it.

any idea why?

(I set up the same kind of table that I had to create to be the source of my combo box for my form and report combo boxes. The only difference is instead of open a form or a report from the combo I want to run a macro from the combo box. Can it be done?

[beacon]

This isn't the exact answer to the question you asked, but it's a possible solution if you don't get the answer you're looking for.

I don't know if you can run a macro from the combo box, but if you convert the macro to VBA, you can assign a value to the combo box (either hard coded or from the row source) and, in the AfterUpdate() event for the combo box, force the code to fire when you select the value you assigned to the combo box.

Private Sub YourComboBoxName_AfterUpdate()

    Dim userSelection as String 'this assumes that the value is a string...you can change it to an integer/double number or a date data type if needed

    userSelection = Me.YourComboBox.Value

    If userSelection = "YourValue" Then
        DoCmd.OpenReport "YourReportName"
    End If

End Sub

If you use this method, be careful about how you setup the row source for the combo box. If the bound column is different from what is actually displayed, you'll need to make that adjustment.

Good luck,
beacon

[jaad]

That looks great Beacon thanks a bunch... One thing though.

DoCmd.OpenReport "YourReportName"

it is not a report that I am trying to open or maybe it is actually.... the macro(s) that I have are saved as a button on top of the form and they direct me to various forms and reports and opening them referencing various ="[id]="&[[ID] for a tenant, or a location or a repair or an inventory item... but none uses the same reference and reason why I set them up as macro button but I am running out of room on my form and wanted to get those macro button into a combo box like I did for a few forms and reports that aren't linked to an [ID] reference into the form that I am working on - those are jsut generic utility forms. but the ones that I want to open with a macro need to keep this ="[ID]="&[ID] reference

[beacon]

You can add an OpenArgs to the DoCmd.OpenRepor t to ensure that the report you're opening only opens the ID you want.

The revised code from above would be something like:

Private Sub YourComboBoxName_AfterUpdate() 
  
    Dim userSelection as String 'this assumes that the value is a string...you can change it to an integer/double number or a date data type if needed 
    Dim varParameter as String

    userSelection = Me.YourComboBox.Value 
    
    varParameter = "[ID]=" & [ID]
  
    If userSelection = "YourValue" Then 
        DoCmd.OpenReport "YourReportName", OpenArgs:varParameter
    End If 
  
End Sub

This should filter your report for the current ID on the form.

If you have a number of items like this that you want to have launch reports or other forms, whatever the case may be, just add them to the If...Then statement.

[jaad]

thanks for the code Beacon. Unfortunately all my [ID] records are named differently and I would have to create a whole bunch of Alias everywhere and that seem a bit involved for just saving room on my desktop.

I am going to forget about doing that. These last few days I have been reading about Normalization and all these little problems that I have been having to find solution for seem all to point out to the fact that portion of my database do not follow the rules of normalization. I have been working really hard at re organizaing my tables and queries and kind of saw the light at the end of the tunnel. Everything seems to fall in place now that I have my structure and relationship in order. I have modified the design of several forms and you know what? I;m starting to feel like I know what I am doing for a change lol.

[beacon]

Well, the good thing is that once you get your database normalized you will definitely be able to use the code above to give you some added functionality on your forms.