help needed with parameter in procedure

[lucasban]

Hello,

I'm trying to export a report based on a query which has a parameter.
this parameter has to come from the recordset.

now if i run the procedure it asks me for the parameter.
How do i get it to take the parameter from the recordset?
it should take the column 'Company#' from the recordset


here is what i have now:

Public Function mOutstandingInvoices2()
On Error GoTo mOutstandingInvoices2_Err

Dim rst As Recordset

DoCmd.SetWarnings False

'On Error GoTo BREAKOUT

Set rst = CurrentDb.OpenRecordset("tRemindersAllCompanies")

Do Until rst.EOF

DoCmd.OutputTo acOutputReport, "qRemindersB2SelectDataFirst", "PDFFormat(*.pdf)", "C:\Temp\Outstanding invoices " & rst![Company Name] & ".pdf", True, "", , acExportQualityPrint
DoCmd.SetWarnings False

rst.MoveNext
Loop


mOutstandingInvoices2_Exit:
Exit Function

mOutstandingInvoices2_Err:
MsgBox Error$
Resume mOutstandingInvoices2_Exit

End Function

hopfully this makes sense...

thanks,

Lucas

 

[namliam]

You are trying to run the query:qRemindersB2SelectDataFirst
For each company in tRemindersAllCompanies ?

 

[lucasban]

yes, that is correct.
and i want to have the query to pick up the company number from the table tRemindersAllCompanies...

 

[namliam]

Currentdb.Querydefs("qRemindersB2SelectDataFirst").sql = "Select ..."
will let you "fiddle" with the SQL of your query, writing the ID into it and exporting it afterwards

 

[lucasban]

dank je Namlian.
I couldn't get your suggestion working, but I've found another way to do it....

 

[namliam]

Glad I couldnt be of help

What is your other way? Atleast for others to find if they find this thread (as well as satisfying my curiousity!)

 

[lucasban]

I didn't do it in VBA, but in a submacro!

I've got a form open which is the record set; with an If statement to start the submacro i'm able to export the reports with the name from the form.
End of the submacro is to mark the line as exported and refresh the form to see the next active one.

 

[namliam]

Sounds complicated

 

[lucasban]

not as complicated as VBA was for me
i'm still exporting the file by calling a function; but to take the next data from a table i'm refreshing the form in a macro

 

[namliam]

Here is what I would do (probably)

Make a new query with any old select in it, name it qReminderNamliamStyle

Public Function mOutstandingInvoices2()
    On Error GoTo mOutstandingInvoices2_Err

    Dim rst As DAO.Recordset

    DoCmd.SetWarnings False

'On Error GoTo BREAKOUT

    Set rst = CurrentDb.OpenRecordset("tRemindersAllCompanies")

    Do Until rst.EOF

        ' Assuming qRemindersB2SelectDataFirst has a where clause already
        Currentdb.querydefs("qReminderNamliamStyle") = Currentdb.querydefs("qRemindersB2SelectDataFirst") & _
                                                     " AND [Company Name] = '" & rst![Company Name] & "'"
        ' Assuming qRemindersB2SelectDataFirst does NOT have a where clause yet
        Currentdb.querydefs("qReminderNamliamStyle") = Currentdb.querydefs("qRemindersB2SelectDataFirst") & _
                                                     " WHERE [Company Name] = '" & rst![Company Name] & "'"

        DoCmd.OutputTo acOutputReport, "qReminderNamliamStyle", "PDFFormat(*.pdf)", "C:\Temp\Outstanding invoices " & rst![Company Name] & ".pdf", True, "", , acExportQualityPrint
        DoCmd.SetWarnings False

        rst.MoveNext
    Loop


mOutstandingInvoices2_Exit:
    Exit Function

mOutstandingInvoices2_Err:
    MsgBox Error$
    Resume mOutstandingInvoices2_Exit

End Function

FYI, a 'proper' naming convention shouldnt use spaces in column names....

 

[lucasban]

i'll check it out next week!