Macro to click a command button

[abhay_547]

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

 

[Chad-M]

What are you using to convert your reports to pdf's? Im using bullzip which will convert my documents to a .pdf by rewriting a settings file. In the settings file it basicly says what you want to call it where you want to put it etc. Once the setting has been used then it will reset back to blank. You will also need a procedure to find the active printer and change it to your pdf writer.

 

[spikepl]

I suspect a combo set from code does not trigger en event. I bet you Combo1 has an AfterUpdate eventhandler which requeries Combo2 (to update it according to what was selected in Combo1). The cure: In the macro, Requery Combo2 after Comb1-value is set .

As to the rest: it's a mess to do using macros. But: a macro can be run from a macro. So your "Click a command button" correpsonds to executing that macro from the macro in which you wish to do the "clicking".

In VBA it would be easy to make a loop, to process selected records. I dont know if you can loop in macros, but as Plan B you could make a macro for each specific country, and then another macro, from which you execute the macros for the desired countries.

 

[abhay_547]

Hi Spikepl,

I had tried to work on the macro as I got stuck with VBA code. Below is the code which I have so far. I am facing a error .i.e. Run-time error '3061': Too few parameters. Expected 1. in the below code:

Private Sub Commandbutton_Click ()
Dim rst As DAO.Recordset
Dim CustomerID As Long

Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
Debug.Print rst
With rst
    Do Until .EOF
        ProgramID = !ProgramID
        DoCmd.OpenReport "StrptReport", acViewPreview, , "CustomerID=" & CustomerID
       Reports![StrptReport].Caption = [FullReportName]
        Name "StrptReport" As [FullReportName] & ".pdf"
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing
End sub

When I click on the [COLOR=blue ! important][FONT=inherit ! important][COLOR=blue ! important][FONT=inherit ! important]debug [/FONT][/FONT][FONT=inherit ! important][COLOR=blue ! important][FONT=inherit ! important]error[/FONT][/COLOR][/FONT][/COLOR][/COLOR] It highlights the below line from code:

Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)

Private Sub Command25_Click()
Dim strDefaultPrinter As String

' get current default printer.

strDefaultPrinter = Application.Printer.DeviceName

' switch to printer of your choice:

Set Application.Printer = Application.Printers("PDFCreator")


If Option_Ref_Loc_Bad_Debt_By_Paycode = True Then
DoCmd.OpenReport "Strpt Report", acViewNormal
DoCmd.PrintOut
DoCmd.Close acReport, "Strpt Report", acSaveNo

End If

' Switch back to original default printer

Set Application.Printer = Application.Printers(strDefaultPrinter)

End Sub

Can you please look into the above codes and help me with the same.

Thanks for your help in advance

Regards
Abhy

 

[spikepl]

If you switch to VBA I would suggets you post your problems under Queries or Forms. Many experienced people hang out there , whereas Macros is fairly limited.

When you repost, repost your code, and also the query, because that's where there is something going on. Open the query, chose SQL view, and past it in your new post.