X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Loop Macro With Paramter Values Set To Field In Table, Access 2010    
post Mar 23 2018, 05:49 AM

Posts: 17
Joined: 4-May 17

Hi all

I have a macro (detailed below) that I want to run multiple times. The macro runs a report which requires a parameter value to be entered, on reports Page 1 & Page 2 (same value for each) but all of the possible values are listed in a table under field name Booking Client.

Is it possible to set the parameter value to this field on each occasion and run it for as only unique values (of which there are about 2,000!)

Function Standard_contract()
On Error GoTo Standard_duplicate_contract_pre_price_freeze_Err
DoCmd.OutputTo acOutputReport, "rpt_102 Page 1", "PDFFormat(*.pdf)", "h:\desktop\New contracts" & Forms!frm_Main_Menu!CRNo & " - 102 - Page 1.pdf", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_102 Page 2", "PDFFormat(*.pdf)", "h:\desktop\New contracts" & Forms!frm_Main_Menu!CRNo & " - 102 - Page 2.pdf", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_102 Page 3", "PDFFormat(*.pdf)", "h:\desktop\New contracts" & Forms!frm_Main_Menu!CRNo & " - 102 - Page 3.pdf", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_ProForma", "PDFFormat(*.pdf)", "G:\common\Pro Forma Invoices (JF Only)" & Reports!rpt_ProForma!Text16 & " - Proforma Invoice (" & Reports!rpt_ProForma!Text174 & ").pdf", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_ProForma", "PDFFormat(*.pdf)", "h:\desktop\New contracts" & Reports!rpt_ProForma!Text16 & " - Proforma Invoice (" & Reports!rpt_ProForma!Text18 & ").pdf", False, "", , acExportQualityPrint
DoCmd.Close acReport, "rpt_ProForma"
MsgBox "Contracts exported. Please visit your ""New Contracts"" folder on your desktop and combine into 1 PDF document.", vbInformation, "New contract"

Exit Function
MsgBox Error$
Resume Standard_contract_Exit
End Function
Go to the top of the page
post Mar 23 2018, 05:56 AM

Posts: 874
Joined: 25-April 14

sub RepeatMacro()
dim x, i

    'get the # times you want to run macro
x = Dlookup("[field]" , "query")
for i = 1 to X
   call Standard_contract()
end sub
Go to the top of the page
post Mar 23 2018, 06:16 AM

Posts: 17
Joined: 4-May 17

Thanks Ranman256

I have just tried this, but I forgot to mention that currently the macro runs a report based on a query that gets the parameter value from a field in a form that normally the user would manually add in. I assume I need to remove this, but what would I replace this with?

e.g. Reports are based on Query1, with the Booking Client field having a parameter set to a value box on the form - Booking Client



Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    17th August 2018 - 06:11 PM