'Print Report' command button on form

[juicybeasty]

I have a form which I want to be printed, and as forms are not designed for printing I have designed an identical report (with subreports) displaying the data in all the fields on the form, for all records.

I have put a command button on the form with the caption "Print this page", but am not sure of the code to use to make it print the report for ONLY that page, not them all.

Any ideas?

 

[Tiro Cupidus]

You could limit the record source of the report based on a current field in the form... I think. =) I'll try to be more specific if that doesn't help.

 

[juicybeasty]

I think you'll have to be more specific - I can be a bit dense!

I was thinking vaguely along the lines of:

DoCmd.OpenReport "rptPrintFormMain" where
Me!CoID = Reports!rptPrintFormMain.CoID

in the OpenClick event of the command button.

(I'm aware that the syntax is prob not quite right there)

But will the ability to do this be affected by the fact that rptPrintFormMain has about 3 subreports?

 

[Tiro Cupidus]

In a module I have this:

Public strRptRecordSource As String

Then in the OnClick event of the button I have this:

   strRptRecordSource = "SELECT * FROM tbl1 WHERE TeamName Like '[abc]';"
   DoCmd.OpenReport "rpt3", acViewPreview

Then in the OnOpen event of the report (rpt3) I have this:

   Me.RecordSource = strRptRecordSource

This way you can set strRptRecordSource to whatever query you'd like. With yours, though, you would probably always want "SELECT fldNames FROM tblName WHERE fldName Like '" & someFormControlText & "';"

I ran out of time, so I'll have to go into more detail on someFormControlText when I get home. =P I think that's where you would use Me!CoID, though.

 

[juicybeasty]

Thanks v much.

I can see where you're going with this and I'll try it tomorrow at work. It looks really useful as a general purpose strategy and for other things I'm doing.

Although I foresee problems with the subreports using this method...

 

[Tiro Cupidus]

Oh, by the way, you would use acViewNormal instead of acViewPreview if you wanted to print it automatically.

 

[juicybeasty]

I've just tried to do it by setting up a macro and attaching it to my command button (because it seemed simple) and it still hasnt worked.

My where condition is:

ID=Forms!frmMain!ID

Where ID is the name of the text box control with the ID number on it on both the form and the report.

It doesn't work, and I copied it exactly from KB article 98801.

It should, shouldn't it?

Now I'm going to try Tiro Cupidus's recordsource solution to see if I have more success.

 

[juicybeasty]

I have made it work using a different method:

In the query my report was based on i put in the criteria for CoNumber

=Forms!frmMain!CoID

And it worked!

So no where condition necessary. Would still like to know why this didn't work though, if anyone knows.

 

[Pat Hartman]

The best method is to use the where argument of the OpenForm Method. That way it doesn't matter which form opens the report. Modifying the query to refer to a form field limits you to running the report from only that form.

 

[PegasusBR]

I think the solution can be even more simple.

On the click command of your "Print this record" button:

DoCmd.OpenReport "rptPrintFormMain", acViewNormal, , "[CoID]=" & Me.CoID

This assumes that CoID is a number and that you are printing the report and not just previewing it.

 

[juicybeasty]

Thanks, I'll try that. Looks like I just got my syntax wrong.

Out of interest, why is "CoID=" in inverted commas, and what does the & do?

CoID is a number, and I do want to print not just preview it.

 

[juicybeasty]

Unfortunately, it doesn't work.

I get a box which says "Enter parameter value" for CoID.

This is the same result I got when it didn't work earlier in this thread.

 

[Pat Hartman]

PegasusBR, that is what I said.

juicybeasty, "[CoID]=" & Me.CoID is the equivalent of the where clause of a query without the word where. Replace the [ColID] with your field name and the Me.ColID with the name of the form control that contains the data you are searching for.

 

[juicybeasty]

That's what I did. I put that syntax in the where argument of the OpenReport method of the command button on the form.

But you mentioned earlier putting it in the OpenForm method, which I was slightly unclear about. Isn't the form already open?

(Although it's just occurred to me that OpenForm may have been a typo.)

CoID IS the name of my text box.

 

[Pat Hartman]

I misread your question. I thought you were opening a form. Since you are opening a report, I should have said OpenReport Method. Why not post the code as it is now?

 

[juicybeasty]

Thanks for your help with this.

I can't post it until tomorrow because it's at work and I'm not! But I will then. No doubt I'm making a stupid mistake.

 

[juicybeasty]

Here is the code:

Private Sub cmdPrintPage_Click()

On Error GoTo Err_cmdPrintPage_Click

Dim stDocName As String

stDocName = "rptPrintFormMain"
DoCmd.OpenReport stDocName, acViewNormal, , "CoID=" & Me.CoID

Exit_cmdPrintPage_Click:
    Exit Sub

Err_cmdPrintPage_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintPage_Click

End Sub

 

[PegasusBR]

Your code looks perfectly OK to me.

So I started backtracking and re-reading the previous posts more closely because I remembered you had it working with a more complicated method. I saw one inconsistency that might be a problem or might just be the way you phrased it.

Your post of 10-21-2003 03:38 PM says that you entered =Forms!frmMain!CoID in the criteria of CoNumber (not CoID). That may mean that the field (or the ControlSource of that field) in the report is CoNumber and not CoID. If that is so, the troublesome line of code would be:

DoCmd.OpenReport stDocName, acViewNormal, , "CoNumber=" & Me.CoID

If both the fields in the report and the form is CoID then I am stumped with the info you've provided so far.

 

[juicybeasty]

Thanks for looking at the code.

I don't know why something so straightforward is turning out so difficult.

To clarify: the names of the text boxes on both the form and the report are CoID. CoNumber is the control source in qryPrintFormMain on which rptPrintFormMain is based.

So the code should work. But when I press the button I get an Enter Parameter Query box asking for CoID, and when I enter a valid number it prints a completely blank form.

This any help.

I'll try zip up the db and attach it, though I haven't got WinZip installed so I may not be able to.

 

[PegasusBR]

Let's see if I got this: You have a report named rptPrintFormMain. It's Record Source is qryPrintFormMain. That query contains a field CoNumber.

If this is correct, then here's a question. In your report, you have a text box named CoID. What is that text box's Control Source?

If the first paragraph is not correct, I'll wait for the zip file