Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009

    Unanswered: How to loop through combo box values and press command button on form with macro

    Hi All,

    I have form in one of my mdb file which gets opened automatically when I open the mdb file. Now I have two combo boxes on that form first one is Region and second one is country and then I have command button to print.
    The first combo box gets populated with regions like USA, Asia, Europe etc.. and second combo box is linked to the first one .i.e. it gets populated with the countries which fall under above region for e.g. if I select USA in combobox1 then it will get populated with Brazil, Argentina etc.. that means every time when the region gets changed in the combobox1 the values in combobox2 get changed. Now I use this file for printing some pdf reports, Actually there is only one report in the mdb file but it's used for generating hundred of reports that is I need to print the report for each country of all regions so I will select the region in combobox1 and country in combobox2 and then click on the command button which will preview the "Report1" with the selected country's data same thing will happen for all countries that means the "Report 1" pulls the data from the data according to the country selected in the combobox2. Now as I have hundreds of reports to print in this mdb file I want to automate this process of printing to pdf file with a macro. I have thought of something like below to automate this process :
    I will comeup with macro in access which will go the combobox1 select the first region in it and the go to combobox2 and select the country in it and then automatically click on the print button. It will do this process for all countries in combobox2. One more thing as of now the commandbutton which I have on the form just previews the report I have to hit the ctrl+p in order to print it to pdf using pdf creator so I want the macro to do the same thing as well automatically that is it shouldn't preview the report but should automatically print it to pdf using pdfcreator as printer and save the pdf on the local drive automatically. I have tried something from my end to create a macro as I have mentioned above but the same is not working.

    In macro actions first I selected the Gotocontrol and in control name I entered the combobox1 and then I went to the next row and then in actions I selected Setvalue and then I entered the name of Region in quotes and then I did the same thing for combobox2 as well but when I run this macro it goes into combobox1 selects the value which I have hardcoded in qoutes but it doesn't select the value in combobox2 in the same way this is what I have done so far but unfortunately I couldn't get some action in action list in macros which will allow me to click the print button apart from this in the above macro I am hardcoding the value but ideally it should pick the values or loops through the values in a table or query.

    Any help or suggestion on the above.. I am facing this difficulty as I haven't worked on ms access much earlier. Please help..

    Thanks for your help in advance

  2. #2
    Join Date
    Jan 2009
    Kerala, India

    How to loop through combo box values and press command button on form with macro

    The Countries Table have the Region Code too, right? You may link both tables together in SELECT Query to use it as Report Source.

    Instead of struggling with Code to control the comboboxes, their values and button clicks etc. you may design a Report and define Region as the Report Main Group and Countries as sub-group. Introduce Page-breaks on Countries and Region Groups. You can print the Report at one go as a .pdf file.

    You may use pdf writer software to extract group of pages (wherever applicable) and save them as separate pdf file.

    If you need manual control then use the combobox references (Forms!myForm!cboRegion) as criteria for the Report Query to filter report data before printing. (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts