Exporting a report that has a parameter using vba 1

[Dan8376]

I am trying to export a report over and over again with code. The report is based on a query that contains a parameter. I fill the parameter by scrolling through a recordset. The report is suppose to export itself after each record change. My problem is that my paremeter is not getting set for the report, I am getting the parameter dialog box when I try to export the first report. I think it may have something to do with the querydef not actually being the record source for my report since a querydef is just a copy of that query. But I don't know how to change my reports recordsource to the querydef in my code.

Sub ExportAuto()
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim Rst1 As DAO.Recordset
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim Rpt As String
Dim FileLocation As String
Dim fundName As String
Set Db = CurrentDb()
Set qdf2 = Db.QueryDefs("qryFundsInDatabase&quot
Set qdf1 = Db.QueryDefs("qryDifferencesSorted1&quot
Set Rst = qdf2.OpenRecordset(dbOpenDynaset)
Rpt = "rptDifferencesSorted1"
FileLocation = "C:\My Documents\Work\"
With Rst
.MoveFirst
Do While Not Rst.EOF
fundName = Rst!Fund
qdf1.Parameters("EnterFund&quot = fundName
DoCmd.OutputTo acOutputReport, Rpt, acFormatXLS, FileLocation & fundName & Rpt & ".xls"
.MoveNext
Loop
End With
MsgBox "Export complete", vbInformation
End Sub

Thanks in advance
Dan

 

[grtammi]

Do you run this code from a command button, or is it automatically run at a certain time in a day? Just for my own curiosity. Now, onto your problem...

The most straight-forward solution that I can think of off the top of my head is to create a string variable (let's call it strSQL); strSQL will contain the SQL statement for your qdf1 variable. Your strSQL statement will be used a little later in my blurb.

Create yourself a QueryDef object (let's call it QDef for simplicity's sake - simple things for my simple mind I guess) - we will also use your Db object that you already have created. Insert the code in green into the places I have listed, and I think your problem will be solved.

...
FileLocation = "C:\My Documents\Work\"
With Rst
    .MoveFirst
        Do While Not Rst.EOF
        fundName = Rst!Fund [COLOR=green]
        on error resume next
        db.querydefs.delete "qryDifferenceSorted1"
        strSQL = "SELECT blah blah blah FROM tblBLAH WHERE 
             tblBLAH.EnterFund LIKE '" & fundName & "';" 
        set qdf1 = db.createquerydef("qryDifferenceSorted1", strSQL)[/color]
        
        DoCmd.OutputTo acOutputReport, Rpt, acFormatXLS, FileLocation & fundName & Rpt & ".xls"
        .MoveNext
        Loop
...

Unfortunately, this code will constantly delete and re-create the query definition and contents, so it may take a while - if anyone out there can expand on my idea here, I would be greatly appreciated.

HTH

Greg Tammi, ATS Alarm Supervisor

PS: Make sure that the report recordsource is left as "qryDifferencesSorted1", else an error will occur.

 

[Dan8376]

Thanks Greg,

This section of code is run from a comman button, but I run a lot of processes before I get to the reports.

Your suggestion worked great. I kept trying to get the recordsource to change for the report, didn't think about changing the query.

Thanks
Dan

 

[grtammi]

Glad I could help.

If you need anything else, you know where to find me!

Greg