help understanding code for printing

[datahunter]

I found this code that sounds exactaly like wat i need but i dont undersatnd
the code... VBA is new to me.. the code i found is:
(Q) I'm trying to print out only the current record from my form in a report
but each time all the records print out. How can I print only one record
using a report?
(A) Use the OpenReport's Where condition to specify your record. For
example, the following code placed behind a command button on the form would
use the RunID control on the form to restrict the Report to only one record.
'******************** Code Start ************************
Dim strDocName As String
Dim strWhere As String
strDocName = "rptSomeReport"
strWhere = "[RunID]=" & me!RunID
DoCmd.OpenReport strDocName, acPreview, , strWhere
'******************** Code End ************************
I tryed this but i am not sure what to change so it will work in my
database. My table name is MainTable the report name is Contract and my prime
key is CustomerId and my form that the print button would be in is named
MainForm... I know vary original names lol.. Thanks to everyone for helping
the less educated!

 

[IndianSummer]

Hi datahunter,

The key instruction in the code you have found is the line that starts
"DoCmd..". It is calling the OpenReport method on the DoCmd object. The
strDocName, acPreview and strWhere parts of that instruction are
respectively:
- a variable that contains the name of the report you want to open,
- an Access constant that specifies the report is to be opened in print
preview (not design view, or go straight to the printer), and
- a variable that contains the filter you wish to apply to the data
populating the report, in this case to ensure it only shows the record that's
displayed in your form. It specifies that the customerid of the record in the
report will match the customerid you have showing in the form ("me").

If none of that makes sense don't worry Essentially there are 2 lines
you need to change, the third and fourth lines:
- substitute the name of your report for rptSomeReport, so the third line
would become
strDocName = "Contract"
- substitute your field name for RunID. So the fourth line would become
strWhere = "[CustomerID] = " & me!CustomerID.

The next issue is whether you feel you have the code in the right place? It
must be on the click event of the command button on your form, the one you
are going to use to open the report.

See how you go with the above and come back if you aren't sure about the
location of the code? Hope this helps

Helen

 

[IndianSummer]

sorry datahunter, just quickly correcting one thing before one of the other
knowledgeable people here picks me up I used the word "filter" in my
description of the OpenReport arguments being used. strictly speaking the
strWhere variable is specifying the WhereCondition of the OpenReport method,
not a filter..

 

[Rick Brandt]

sorry datahunter, just quickly correcting one thing before one of the
other knowledgeable people here picks me up I used the word
"filter" in my description of the OpenReport arguments being used.
strictly speaking the strWhere variable is specifying the
WhereCondition of the OpenReport method, not a filter..

But all the WhereCondition does is apply a filter so there really is no
distinction to make.

 

[IndianSummer]

Rick I disagree but only from the point of view of datahunter beginning to
learn to understand the syntax, named arguments, etc.?

datahunter if you're reading this the OpenReport method has arguments
ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs - always
in that order. Which is why the code you found has the two commas next to
each other in that key line, did you notice? THey act as like placeholders so
Access knows that the strWhere variable in your code sample contains the
Where argument, not the Filtername argument.

But having said that I do take your point Rick, in reality the where arg
is a filter.

And thanks for your time and bringing your MVP status to the thread! You and
your cohorts do such great work helping all of us, and creating an
environment which fosters the sharing of knowledge thank you.

 

[datahunter]

I changed the lines of code as decribed and when I "click" my button I get a
error message saying... Access can't find the field'customerID' referred to
in your expression.
THe CustomerID is in my table and it is also used in the form and the report
and the 'autonumber' data shows correctly.
Any ideas what the problem is? Thanks again to everyone!

 

[IndianSummer]

Locate the textboxes on each of your form and report and check the "Name"
property of each. This is located in the "Other" category of properties in
the property sheet. Then check the line of code where you set your criteria.

strWhere = "[RunID]=" & me!RunID really means
strWhere = "[name of textbox on your report] =" & me!Name of textbox on your
form (regardless of the Control Source for each).

If your name/s have spaces in them, you will need to enclose the name in
square brackets after the me! as well.

Also double check that same line of code to make sure you have all of your
talking marks, commas etc. in place, exactly matching the original sample.

 

[datahunter]

Thank you IndianSummer for all your help that did the trick!!

Locate the textboxes on each of your form and report and check the "Name"
property of each. This is located in the "Other" category of properties in
the property sheet. Then check the line of code where you set your criteria.

strWhere = "[RunID]=" & me!RunID really means
strWhere = "[name of textbox on your report] =" & me!Name of textbox on your
form (regardless of the Control Source for each).

If your name/s have spaces in them, you will need to enclose the name in
square brackets after the me! as well.

Also double check that same line of code to make sure you have all of your
talking marks, commas etc. in place, exactly matching the original sample.

I changed the lines of code as decribed and when I "click" my button I get a
error message saying... Access can't find the field'customerID' referred to
in your expression.
THe CustomerID is in my table and it is also used in the form and the report
and the 'autonumber' data shows correctly.
Any ideas what the problem is? Thanks again to everyone!

 

[IndianSummer]

woohoo!! Great news - so glad you got your report working and happy to help

Thank you IndianSummer for all your help that did the trick!!

Locate the textboxes on each of your form and report and check the "Name"
property of each. This is located in the "Other" category of properties in
the property sheet. Then check the line of code where you set your criteria.

strWhere = "[RunID]=" & me!RunID really means
strWhere = "[name of textbox on your report] =" & me!Name of textbox on your
form (regardless of the Control Source for each).

If your name/s have spaces in them, you will need to enclose the name in
square brackets after the me! as well.

Also double check that same line of code to make sure you have all of your
talking marks, commas etc. in place, exactly matching the original sample.

I changed the lines of code as decribed and when I "click" my button I get a
error message saying... Access can't find the field'customerID' referred to
in your expression.
THe CustomerID is in my table and it is also used in the form and the report
and the 'autonumber' data shows correctly.
Any ideas what the problem is? Thanks again to everyone!

:

Rick I disagree but only from the point of view of datahunter beginning to
learn to understand the syntax, named arguments, etc.?

datahunter if you're reading this the OpenReport method has arguments
ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs - always
in that order. Which is why the code you found has the two commas next to
each other in that key line, did you notice? THey act as like placeholders so
Access knows that the strWhere variable in your code sample contains the
Where argument, not the Filtername argument.

But having said that I do take your point Rick, in reality the where arg
is a filter.

And thanks for your time and bringing your MVP status to the thread! You and
your cohorts do such great work helping all of us, and creating an
environment which fosters the sharing of knowledge thank you.



:

IndianSummer wrote:
sorry datahunter, just quickly correcting one thing before one of the
other knowledgeable people here picks me up I used the word
"filter" in my description of the OpenReport arguments being used.
strictly speaking the strWhere variable is specifying the
WhereCondition of the OpenReport method, not a filter..

But all the WhereCondition does is apply a filter so there really is no
distinction to make.