printing labels to selected rows/columns (1 Viewer)

[DesBlair]

Has anyone found an easy way to print single address labels to a specified row and column on a label sheet? I can print to the first row and column but not to anywhere in a partly used label sheet. One way is to set up separate margins for each position but on a 7 by 3 sheet this would mean having 21 separate settings - not very practical. Any thoughts other thyan exporting to Word??

DesBlair

 

[RichO]

This seems to be a pretty common question around here.

For this situation I have a pop-up form that allows the user to enter the row to begin printing on. Then with a command button I use VBA and SQL to make a temporary table, first inserting blank records into the table (to skip over rows) and then appending the records to be printed on the labels. A label report is then run off this table.

Attached is an example.

 

[DesBlair]

Thanks!

Many thanks RichO. It's easy when you know how!

DesBlair

 

[DesBlair]

Label printing from a query

Thanks to RichO for the help with my earlier problem – Printing to particular label rows works fine when printing all the records. My difficulry now is how to do this when printing from a query which is used to select a subset of the records. What I really need to do is be able top select one or more records and print to any row on a label sheet. I feel sure the answer lies in some VBA code but I can’t figure it out. See qryCategorise and report AddressLabelsSelection.
Can anyone help please? (Access 2002)


Des

 

[RichO]

Here's what I did with your example:

I converted your query to an append query to append the information to the temp table. Then from the form, when you select a category I set the warnings to false (so it doesn't alert you that it's going to paste rows into your table), opened the append query, set the warnings back to true, and opened the report.

You don't need dual reports for this application as long as you are displaying the same information.

Also, it looks like your labels print only 2 per row but you changed the code that skips rows for 3 labels per row. Is that what you wanted?

I also set the form up with only one command button for printing. If there is an entry in the combo box it appends labels based on the selection, otherwise it appends all.

 

[DesBlair]

Thanks again!

Works perfectly. Yes I am using 3 column Avery labels. No problem though - even I can fix that. Thanks again Rich. What a useful forum this is!
Des

 

[roblow]

Hi,
I am verry new to access databases, i am trying to do something similar to this thread.
i have a form that pulls from a table and opens a report that prints on to Avery 8660 labels.
when i print it prints the selected record to the first label on the label sheet, what i would like to be able do is print the selected record to to any where on the labels.
IE print the selected record on to row 2 Colum 3 etc.
thank you in advance for looking and any help you can give me.
Regards
Rob.

 

[Gasman]

Have you looked at that db offered from @RichO
Also look at this thread

I also seem to recall that Pat Hartman created something to do this. Sadly all her posts were removed at her request.

 

[Ken Sheridan]

............what i would like to be able do is print the selected record to to any where on the labels.

The attached file is primarily intended for printing simple unformatted ad hoc single labels, but it also allows each label to be saved to a table. A saved label can be selected from a list box and one or more of that label can be printed, starting at a selected position on the sheet. It works by using an auxiliary Counters table which is simply a list of sequential integer numbers. The following query gets the label text form the form into which the newly composed or selected saved label is inserted. All rows up to the selected starting position for the label return Nulls, so the text is printed only from the starting position onwards.

PARAMETERS
    Forms!frmlabelsDlg!optStartAt SHORT,
    Forms!frmlabelsDlg!txtNumberToprint SHORT;
SELECT
    IIf(Counter>=Forms!frmlabelsDlg!optStartAt,(Forms!frmlabelsDlg!txtLine1 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine2 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine3 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine4 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine5 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine6 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine7 & Chr(13) & Chr(10))
    & (Forms!frmlabelsDlg!txtLine8 & Chr(13) & Chr(10)),Null) AS LabelText
FROM
    Counters
WHERE
    Counters.[Counter] < [Forms]![frmlabelsDlg]![txtNumberToprint] + [Forms]![frmlabelsDlg]![optStartAt];

Tip: When composing a label of 6 lines or fewer, start the label at at the second, third etc line as appropriate, rather than at the first line to balance the position of the text vertically within the label.

 

[Gasman]

Ken, it was that very code of yours that I linked to.

 

[xavier.batlle]

Look at this article by Colin:

Manage Label Printing

This example database provides additional functionality not available using the label report wizard. This includes options to select different reports e.g. name/address, selected label sizes, add optional input text, print duplicate labels and leave blanks for working with partly used label sheets.

www.isladogs.co.uk

 

[Ken Sheridan]

Ken, it was that very code of yours that I linked to.

Doh! I should have looked before I jumped.

 

[Gasman]

Doh! I should have looked before I jumped.

Doesn't hurt to say it twice I suppose.