UtterAccess.com
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    
 
   
rgwood86
post Mar 23 2018, 05:49 AM
Post#1



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
KillMyFile
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"
Copy_One_File
MsgBox "Contracts exported. Please visit your ""New Contracts"" folder on your desktop and combine into 1 PDF document.", vbInformation, "New contract"

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



Posts: 856
Joined: 25-April 14



CODE
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()
next
end sub
Go to the top of the page
 
rgwood86
post Mar 23 2018, 06:16 AM
Post#3



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

Thanks

Rob


Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:41 AM