printing labels

yami56 · April 22nd, 2004, 04:21 AM

Hello every one.

Ok i have a table with 800 records
one of the option is my user can choose the record and then print it into envelope but how can i do it for labels
i am using this code for envelope
SELECT main.cn, main.cadd, main.pc, main.id FROM main WHERE (((main.id)=Forms!editform!ID));
and its working fine for envelope but when i am using it for labels it only print one label not 14
please help.

SerranoG · April 22nd, 2004, 06:55 AM

I assume that the envelope you're printing was really a report where the margins and paper size were set accordingly for an envelope. You'll have to create a new report that is tailored to mailing labels. Use the Report Wizard.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

yami56 · April 22nd, 2004, 07:15 AM

i did it and i have a new report(label) using the wizard but the problem is when i use the sql code it will only show one record.

Best Regards,
Hamed Gholamian

SerranoG · April 22nd, 2004, 11:50 AM

Your envelope had this SQL:

SELECT main.cn, main.cadd, main.pc, main.id FROM main WHERE (((main.id)=Forms!editform!ID));

And you got one envelope. But if you use the same WHERE clause for your labels, you'll also get one label. Remove the WHERE clause.

SELECT main.cn, main.cadd, main.pc, main.id FROM main;

Actually, ideally you should have a new WHERE clause to make sure the address is not blank. You don't want labels with no addresses!

yami56 · April 23rd, 2004, 03:53 AM

Dear serran

I tried it and it worked but with a small problem.
But what i need is just print the current record i can draw it for you.

with my current code thats how it look like

Company name
Company address
Post Code

the problem is there is only one instead of having 14 same in one page

the other example is msword i dont whether you use labeling system in msword or no you just type your address and it will do the rest

SerranoG · April 23rd, 2004, 06:54 AM

Oh, you want RETURN ADDRESS labels, not labels going to the recipients. What I do is create a table with my name and return address and I copy the one record 30 times (usually one sheet of labels is three across and ten down) and use the label wizard on that table.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

yami56 · April 23rd, 2004, 07:08 AM

no you are right i want to print senders address not my own address
thats the problem

SerranoG · April 23rd, 2004, 07:13 AM

Now, you're confusing me. You said

Quote:

quote:the problem is there is only one instead of having 14 same in one page

If you want 14 of the same label on one page, to me that says you want to print return address labels... that is, YOUR name 14 times.

Do you want your return address on the 14 labels, do you want one recipient 14 times, or do you want 14 different recipients one time each? If you want 14 different people one time each then my original answer should work unless your underlying table has only one record.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

yami56 · April 23rd, 2004, 07:20 AM

i am so sorry
i want recipient 14 times

sorry again

SerranoG · April 23rd, 2004, 07:41 AM

Oh, you want ONE recipient 14 times... OK... what you would do then is on your form, you have a button to print mulitple labels for that person. For the button's ON CLICK event you write the particular record to a NEW and TEMPORARY table 14 times. Then you open the report based on that temporary table. After you close the report, delete the temporary table.

The code in the ON CLICK event would look something like this. Remember to replace YOUR field names instead of the ones I put here:

Code:

   Dim bytCount as Byte

   'Add the first record.
   DoCmd.RunSQL "SELECT tblMyTable.strName, tblMyTable.strAddress, _
      tblMyTable.strCity, tblMyTable.strState, _
      tblMyTable.strPostalCode INTO tblTempLabel FROM tblMyTable _
      WHERE tblMyTable.lngRecNo = " & Me.txtRecNo & ";"

   'Add the other 13 records of the same thing.
   For bytCount = 1 To 13
      DoCmd.RunSQL "INSERT INTO tblTempLabel ( strName, strAddress, _
         strCity, strState, strPostalCode ) SELECT _
         tblMyTable.strName, tblMyTable.strAddress, _
         tblMyTable.strCity, tblMyTable.strState, _
         tblMyTable.strPostalCode INTO tblTempLabel FROM _
         tblMyTable WHERE tblMyTable.lngRecNo = " & _
         Me.txtRecNo & ";"
   Next bytCount

   'Open the report, i.e. the labels.
   DoCmd.OpenReport "rptMyLabels", acViewPreview

In the report's ON CLOSE event, you have this:

Code:

DoCmd.RunSQL "DELETE * FROM tblTempLabel;"

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division