Printing a separate report for each customer via VBA

[Shane Devenshire]

Hi Folks,

I posted this the another newsgroup and got no responses, so I'm rephrasing
it here and trying again. I'm trying to automate the process of printing a
report for each unique customer.

If can't get the sql where clause I need, at least that's what I think is
wrong. Here is the code line that fails: The report opens but is empty,
there is no error message caused by this line.

DoCmd.OpenReport "CustomerReport", acViewPreview, , CustomerID =
myCustomerID

CustomerReport is the name of the report to open
CustomerID is the field in the query for which I want to set the criteria
myCustomerID is a variable where I store each unique customer ID as I cycle
through the open recordset (which is not the same one as where the
CustomerID is coming from in the query.

Do I need to open both record set to get this to work or is the Where
condition written incorrectly. I have tested and the myCustomerID variable
contains the unique ID. After the above line is run CustomerID shows as
Empty. I have tried qualifying it, for example tblOrders.CustomerID but
this generates an object required error message.

TIA,
SD

 

[Larry Daugherty]

You might consider using the features of the Report designer to
achieve the same result in a different way:

In the Report, Group on each customer. Format everything within that
grouping to show the Heading, Titles, date and other things you want
for each customer. That way, you can run the job as though it were a
single Report but each Group (force new page before...) becomes the
individual report that the customer sees. You can even paginate
within the group just as you could over the entire report.

HTH

 

[John W. Vinson]

Do I need to open both record set to get this to work or is the Where
condition written incorrectly.

Yes, it is. It needs to be a Text string, not an expression.

Try

DoCmd.OpenReport "CustomerReport", acViewPreview, ,
"[CustomerID] = " & myCustomerID

concatenating the value of the variable to a text string to construct the
WHERE clause.

 

[Shane Devenshire]

Hi John,

I entered the code you supplied:

DoCmd.OpenReport "CustomerReport", acViewPreview, , _
"CustomerID = " & myCustomerID

This prompts me with "Enter Parameter Value" and displays the value in the
myCustomerID variable as the input box's prompt?

Any ideas?

Thanks again,
SD

Do I need to open both record set to get this to work or is the Where
condition written incorrectly.

Yes, it is. It needs to be a Text string, not an expression.

Try

DoCmd.OpenReport "CustomerReport", acViewPreview, ,
"[CustomerID] = " & myCustomerID

concatenating the value of the variable to a text string to construct the
WHERE clause.

 

[Douglas J. Steele]

Is CustomerID text or numeric? John assumed it was numeric, but if it's
text, use

DoCmd.OpenReport "CustomerReport", acViewPreview, , _
"CustomerID = '" & myCustomerID & "'"

Exagerated for clarity, that last line is

"CustomerID = ' " & myCustomerID & " ' "

That assumes there'll never be an apostrophe in the Customer ID.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John,

I entered the code you supplied:

DoCmd.OpenReport "CustomerReport", acViewPreview, , _
"CustomerID = " & myCustomerID

This prompts me with "Enter Parameter Value" and displays the value in the
myCustomerID variable as the input box's prompt?

Any ideas?

Thanks again,
SD

Do I need to open both record set to get this to work or is the Where
condition written incorrectly.

Yes, it is. It needs to be a Text string, not an expression.

Try

DoCmd.OpenReport "CustomerReport", acViewPreview, ,
"[CustomerID] = " & myCustomerID

concatenating the value of the variable to a text string to construct the
WHERE clause.

 

[Shane Devenshire]

Hi Douglas,

that got it! Thanks so much to all of you for this help. The CustomerID
field is text.

Thanks again,
SD

Is CustomerID text or numeric? John assumed it was numeric, but if it's
text, use

DoCmd.OpenReport "CustomerReport", acViewPreview, , _
"CustomerID = '" & myCustomerID & "'"

Exagerated for clarity, that last line is

"CustomerID = ' " & myCustomerID & " ' "

That assumes there'll never be an apostrophe in the Customer ID.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John,

I entered the code you supplied:

DoCmd.OpenReport "CustomerReport", acViewPreview, , _
"CustomerID = " & myCustomerID

This prompts me with "Enter Parameter Value" and displays the value in
the myCustomerID variable as the input box's prompt?

Any ideas?

Thanks again,
SD

On Mon, 2 Jun 2008 22:12:23 -0700, "Shane Devenshire"

Do I need to open both record set to get this to work or is the Where
condition written incorrectly.

Yes, it is. It needs to be a Text string, not an expression.

Try

DoCmd.OpenReport "CustomerReport", acViewPreview, ,
"[CustomerID] = " & myCustomerID

concatenating the value of the variable to a text string to construct
the
WHERE clause.