Form print as a report

[Gkirkup]

I have a form that I need to print, and as suggested here, to have more control over the printing, I have saved the form as a report. Now I have a 'Print' button on the form, which prints the report.
However, when I click the print button it prints the report many times over, for different records. (It would print thousands of pages if I let it). How do I get the report to print just the records that were selected on the form, when the print button was pressed?

Robert

 

[jbphoenix]

Look at this post - I have done exactly what you are asking about. You need to create a query first -

http://www.access-programmers.co.uk/forums/showthread.php?t=128958&highlight=print+current+record

 

[boblarson]

Look at this post - I have done exactly what you are asking about. You need to create a query first -

http://www.access-programmers.co.uk/forums/showthread.php?t=128958&highlight=print+current+record

Actually, this isn't quite accurate. You do NOT need anything but the ID field to pull a report based on what is in the form:

DoCmd.OpenReport "YourReportNameHere, acViewPreview,,"[YourIDFieldOnTheReport]=" & Me!YourIDFieldOnYourForm

 

[Rich]

Actually you don't even need a parameter query, you can just use the Where clause of the Open Report method and then use the query for other purposes without any restriction

 

[Gkirkup]

Bob: Thanks I almost have it. I entered this code:

stDocName = "MY REPORT NAME""
DoCmd.OpenReport stDocName, acNormal, , "[PONUM]=" & Me!PONUM

What happened was that I got a parameter box with '45788' which is the value of PONUM. I entered '45788' in the parameter box, and the report printed correctly. What am I missing?
Robert

 

[boblarson]

If you don't have PONUM on your form, or in the recordset then it would ask for it. Or you might not have PONUM in the recordset of your Report? Make sure that the spellings are correct in both places.

Alternatively, if you have PONUM on your form as a text box name as well as the field name, Access doesn't quite know which to use so rename your text box to something else and you could refer to it via "[PONUM]=" & Me.YourTextBoxNameHere

Or, if your PONUM is actually text in the table, and not a number, then you have to use:

DoCmd.OpenReport stDocName, acNormal, , "[PONUM]='" & Me!PONUM & "'"

 

[Gkirkup]

DoCmd.OpenReport stDocName, acNormal, , "[PONUM]='" & Me!PONUM & "'"

Bob: Many thanks, that worked - PONUM is text. I looked at your combination of single and double quotes for a long time, and still don't understand them! Finally I just copied your line of code, and that worked perfectly.
Robert

 

[boblarson]

DoCmd.OpenReport stDocName, acNormal, , "[PONUM]='" & Me!PONUM & "'"

Bob: Many thanks, that worked - PONUM is text. I looked at your combination of single and double quotes for a long time, and still don't understand them! Finally I just copied your line of code, and that worked perfectly.
Robert

It's fairly easy but the explanation is thus:

You need to put the Where clause (which is what it is) as a string, which requires it to be in double quotes. Because we are referencing an item on the form we don't include that part within the double quotes, but because it is text we also have to let Access know that we are looking for text, so instead of going wild with double quotes, we just use single quotes and since we have the form part outside of the double quotes we have to concatenate in the other single quote by using the ampersand (&) and then double quote, single quote to end the part that says what we have between single quotes is text, and then another double quote to end the string.

I hope that helps explain it.