Loop macro using list of different parameters

[rgwood86]

Hi All,

I have a query which I want to run as many times as there are unique values in a field within a table in the same database.

My query is set to look at a field on a form, but I would like to run this macro for all unique values in the table of which there are approximately 2,000!

My table name is tbl_All Data and the query name is Macro 1. There is only 1 parameter value needed each time, and they are all listed in a field within tbl_All Data called Booking Client.

Please could someone assist me in writing the appropriate macro to do this?

Many thanks as always

Rob

 

[isladogs]

Why can't the query read all 2000 or so records in one 'run'
Suggest you explain better what the query does
In fact, provide the SQL and some sample data

If it needs a routine to do this, you are more likely to get a response using VBA rather than a macro as very few of us use macros

 

[rgwood86]

Thanks Colin.

Here is the code for one of the macros that I want to loop:

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


The reports are based on Query1 which has just one parameter box prompt for the user to input which is the Booking Client field.

 

[arnelgp]

copy your Query1 (ctrl-c)(ctrl-v).
name the copy, "_qryTemplate"
now edit _qryTemplate query, rename the parameter as "[p1]".
save the query.

go back to your vba code:


Public function Standard_Contract()
'' add this on the beginning of code
dim strInput as string
dim SQL as string
dim qd as dao.querydefs
strInput = Inputbox("put here the prompt same as in the query")
If strInput <> "" then
set qd=currentdb.querydefs("_qryTemplate")
sql = qd.sql
set qd=nothing
sql = replace(sql,"[p1]",Chr(34) & strInput & Chr(34))
set qd=currentdb.querydefs("Query1")
qd.sql=sql
set qd=nothing
'' now the rest of your code below
On Error Goto Standard_....
KillMyFile
Docmd.Output ...blah...blah
..
..
End If
end Sub