Linking macros to checkboxes selected on an Access form

[Emma C]

Hi,

I have the requirement to copy and paste the results of various queries into Excel workbooks. I've set up macros to "OutputTo" the results of each of the queries (nearly 100!) and I've set up a further macro to export them all rather one after the other than running each macro individually.

Where I'm coming un-stuck is that ideally I'd like to be able to create a form with checkboxes where the user can select which queries to run and export, click a "Go" button (or something) and this would trigger the relevant macros to run. I have a good understanding of Access but I'm not great with VBA (I can make small adjustments myself but writing from scratch is rather beyond me at the moment...) and google as hard as I might I haven't been able to find any examples of this being accomplished although it doesn't seem like a big leap from where I am now...

Please help!

[ADezii]

Hello Emma, actually there is a very simple solution to your problem, as long as you perform 3 Steps exactly. For this Demo, I'm assuming that you are using 10 Check Boxes, but the actual number is irrelevant, as long as you do not exceed the limit of Controls on a Form. Follow the Steps below precisely:

1. Name your Check Boxes sequentially, as in: chk1, chk2, chk3...chk10.
2. Name you Macros in similar fashion as in: Macro1, Macro2, Macro3...Macro10.
3. Make sure the Labels for you Check Boxes are in sync with the Macro being called, as in: If Check Box #4 is the only one selected, and then Macro4 will run. Be sure the actual Query that Macro4 will Output is reflected in the Label for Check Box 4 (chk4).
4. When all is done, execute the following Code which will Execute the corresponding Macro for each Check Box selected:
   

   Dim ctl As Control
   Dim intCtr As Integer
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
       intCtr = intCtr + 1
         If ctl.Value Then
           DoCmd.RunMacro "Macro" & CStr(intCtr)
         End If
     End If
   Next

5. Any questions, feel free to ask.