Automatic reports

[Patrick521]

I am using Access 2007 to generate monthly sales reports for a number of different clients. The report is drawing form a query that shows pulls up all current (not expired) contracts that had activity on them in the last month. Right now it puts all the clients into one report, which I can export to a pdf to send to clients, but I don't want to send a report that has all the clients to each one--it wouldn't do for one client to see that another got a better deal etc.

I imagine there is a way that I can set this up to automatically generate these different reports for me--thoughts on how to do it? My instinct is to go with VBA coding, but I don't know what I would code!

Thanks!

 

[lameid]

Docmd.openReport allows you to open a report and even specify criteria.

So all you need is to identify the records you want...

Dim RS as DAO.Recordset 'DAO Code is faster in Access 
                        'than ADO and I can write it cold :)

Set RS = Currentdb.Openrecordset "<Qry or table that has all clients>"

While Not RS.EOF
    docmd.OpenReport "<ReportName>",acViewPreview,,"<Client Key in report>" = RS![<Client field>]
    RS.movenext
Wend

So substitute all the stuff as delimeted by < and > and put it in an appropriate procedure and you will be good.

 

[Patrick521]

lameid-

is there a way I can modify the code so that rather than needing to manually enter the client key it will use a findnext or some other command to fill that in?

 

[lameid]

That's what this code is intended to do. Although since I told it to preview it would not update the report... if you replace acViewPreview with acViewNormal, it will print all the reports in series. To clarify <Client field> in
RS![<Client field>] means the Name of the client field in the table or query being used.

You probably want to substitute docmd.openreport with whatever code that exports the PDF's. You may have to set the value of the client to a form textbox and use it for criteria in the query that is the recordsource of the report.

 

[Patrick521]

I have been trying for the last couple of days to get this code to work, but I still can't get it quite right. It keeps putting together a report that includes all the clients still, rather than a number of different reports. I even tried hard entering the indentifier in the <client key in report> and it continues to return all the values.

Thoughts?

 

[lameid]

Can you post your updated version of the code?

 

[Patrick521]

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("fixed sales")

While Not RS.EOF
DoCmd.OpenReport "fixed sales", acViewNormal, , "[Contract_number" = RS![uct_contract_no]
RS.MoveNext

Wend

 

[lameid]

DoCmd.OpenReport "fixed sales", acViewNormal, , "[Contract_number" = RS![uct_contract_no]

You have an extra square bracket in there... and oops, I put the equal sign in th wrong place and left out the string concatenation Try...

DoCmd.OpenReport "fixed sales", acViewNormal, , "Contract_number = " & RS![uct_contract_no]

 

[lameid]

Sorry about the wrapping text, I forgot to preview. Obviously everything should be on one line.

 

[Patrick521]

Still no dice...

with acViewPreview, no records show up on the report.
with acviewNormal, all records appear on the printed report, and it prints a number of times.

 

[lameid]

Since the below is not working for you...

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("fixed sales")

While Not RS.EOF
    DoCmd.OpenReport "fixed sales", _
        acViewNormal, , "Contract_number = " & RS![uct_contract_no]
RS.MoveNext

Wend

Try this

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("fixed sales")

While Not RS.EOF
    Debug.print "Contract_number = " & RS![uct_contract_no]
RS.MoveNext

Wend

When it is done press Ctrl+G and look at the reults in the immediate window (bottom pane). You should see a list of different contract numbers. This should be a list of the contracts you want to run for. If it is not something else, something is wrong. If that works, I have to wonder if Contract_number is in the report.

 

[Patrick521]

It displayed the list of contract numbers, and the field contract_number is in the report. It seems to me that the filter is not actually being applied to the report--is this possible? I don't know that it makes a difference, but this command is set to run "on click" anywhere in the report.

 

[lameid]

Something is just amiss. I'm thinkging and I can't think of anything that prevents filtering in an MDB application.

The only thing I can think of is some naming issue...
Try building the recordsource of the report. Look at the SQL view of the report and note the where clause (or copy it or the entire SQL and post it here). Next modify the query to run for one of the contract numbers and note the additional criteria for the contract (or post it).

Based on that, you should be able to tell if the criteria being built by the code is right according to the test we did with debug.print.

Barring that the only other idea I have is code on the report that is effecting the filter.

If none of that turns anything up, try making a new report and filtering on it just to see if everything seems to work otherwise.