Printing individual mailing label question

[Dave]

Hi all

I have a student database where I would like to be able to print just
one mailing label, for the student whose record I am currently
accessing on the form. I want to be able to click on the command
button and have the label print out on my labelwriter.

I have done some searching on the web, and found how to print single
labels using a parameter query, but how do I set it up to print the
current record on the form?

Thanks for any help you can provide.

Dave

[pietlinden]

you pass the record ID when you open the report. See DoCmd.OpenRepor t.
One of the arguments is a filter (pass a valid where clause). I think
there's even a wizard for creating a button to do this.

[pietlinden]

Dave wrote:

Hi all

I have a student database where I would like to be able to print just
one mailing label, for the student whose record I am currently
accessing on the form. I want to be able to click on the command
button and have the label print out on my labelwriter.

I have done some searching on the web, and found how to print single
labels using a parameter query, but how do I set it up to print the
current record on the form?

Thanks for any help you can provide.

Dave

Okay, now I'm at home where I can actually look at a database and not
screw things up. Here's an example using Northwind.

I put a button on the "Customer Orders" form. On that form is a
control called "CompanyNam e", which is a text field. Here's the code
that opens the Customer Labels report with a label for only the current
company (on the Orders form).

Option Compare Database
Option Explicit

Private Sub cmdPreviewLabel s_Click()
On Error GoTo Err_cmdPreviewL abels_Click

Dim stDocName As String

stDocName = "Customer Labels"
'---this is the line that opens the report...
'---the filter points at the CompanyName field on the open form...
'---the single quotes are to delimit the text value.
DoCmd.OpenRepor t stDocName, acViewPreview, , "[CompanyName]='" &
Me.CompanyName & "'"

Exit_cmdPreview Labels_Click:
Exit Sub

Err_cmdPreviewL abels_Click:
MsgBox Err.Description
Resume Exit_cmdPreview Labels_Click

End Sub

Does that work for you?

[Dave]

On 7 Sep 2006 19:44:00 -0700, pi********@hotm ail.com wrote:

>Okay, now I'm at home where I can actually look at a database and not
screw things up. Here's an example using Northwind.

I put a button on the "Customer Orders" form. On that form is a
control called "CompanyNam e", which is a text field. Here's the code
that opens the Customer Labels report with a label for only the current
company (on the Orders form).

Option Compare Database
Option Explicit

Private Sub cmdPreviewLabel s_Click()
On Error GoTo Err_cmdPreviewL abels_Click

Dim stDocName As String

stDocName = "Customer Labels"
'---this is the line that opens the report...
'---the filter points at the CompanyName field on the open form...
'---the single quotes are to delimit the text value.
DoCmd.OpenRepor t stDocName, acViewPreview, , "[CompanyName]='" &
Me.CompanyNa me & "'"

Exit_cmdPrevie wLabels_Click:
Exit Sub

Err_cmdPreview Labels_Click:
MsgBox Err.Description
Resume Exit_cmdPreview Labels_Click

End Sub

Does that work for you?

I think that makes sense. So does this mean I should base the label
report on the table the form is based on rather than the parameter
query?

The table and form are called "Students" and the report for producing
the labels is called "Student Mailing Labels."

The code for mine looks a lot more complicated, and I'm thinking
that's because it's based on a parameter query.

Thank you very much.

Dave

[pietlinden]

No, you don't need the parameters in there. As a matter of fact, you
don't need any filters at all. You can pass _all_ those in the Open
event of your report. So you can build them on the fly. Have a look
at the Customer Labels form in Northwind. Basically all the form does
is collect parameters and then pass them to the report. The report
itself doesn't have any.

[Dave]

On 7 Sep 2006 19:44:00 -0700, pi********@hotm ail.com wrote:

After looking at my database, changing the report to remove the
parameter query, and trying to include the code you gave me, I do have
a couple of questions.

>Okay, now I'm at home where I can actually look at a database and not
screw things up. Here's an example using Northwind.

I put a button on the "Customer Orders" form. On that form is a
control called "CompanyNam e", which is a text field. Here's the code
that opens the Customer Labels report with a label for only the current
company (on the Orders form).

>Option Compare Database
Option Explicit

I have multiple buttons on my form (Find, First Record, Previous
Record, Next Record, Last Record, Duplicate Record), so it has code
for all of these. Does the "Option Explicit" go at the very top of
all this, right after "Option Compare," or does it go in the section
pertaining to the specific "print label" button I'm creating?

>Private Sub cmdPreviewLabel s_Click()
On Error GoTo Err_cmdPreviewL abels_Click

Dim stDocName As String

stDocName = "Customer Labels"
'---this is the line that opens the report...
'---the filter points at the CompanyName field on the open form...
'---the single quotes are to delimit the text value.
DoCmd.OpenRepor t stDocName, acViewPreview, , "[CompanyName]='" &
Me.CompanyNa me & "'"

My mailing labels will be individual students, so they'll have first
name (Control: First Name) and last name (Control: Last Name), not a
company name. Can I put [Last Name] in there instead? The only
reason I ask is that "Last Name" doesn't seem to fit where your
"Me.CompanyName " is. Should it be Last_Name? Or should it be
included with the brackets?

Please bear with me. Most of the stuff I've learned about Access has
been self-taught from a book, so I'm sure I've got some holes in my
knowledge, especially where programming it is concerned.

>Exit_cmdPrevie wLabels_Click:
Exit Sub

Err_cmdPreview Labels_Click:
MsgBox Err.Description
Resume Exit_cmdPreview Labels_Click

End Sub

Does that work for you?

It looks like the only two "new" pieces of code are the "Option
Explicit" and the "DoCmd" line. Everything else that you posted seems
to be already there just from creating the button. So those two new
additions are what makes Access print just the one label?

Thank you again.

Dave

[pietlinden]

No, this is what causes the report to only contain one label:

DoCmd.OpenRepor t stDocName, acViewPreview, , "[CompanyName]='" &
Me.CompanyName & "'"

[Dave]

On 8 Sep 2006 12:31:13 -0700, pi********@hotm ail.com wrote:

>No, this is what causes the report to only contain one label:

DoCmd.OpenRepo rt stDocName, acViewPreview, , "[CompanyName]='" &
Me.CompanyNa me & "'"

I think I got it to work. Now to figure out why it's wanting to print
two "pages" (labels) rather than just the one, but I'm sure that's
just a label formatting problem.

Thank you for your help.

Dave