How to... Produce more than one copy of a label 1

[ProgramError]

Hi

I'm based at Manchester Metropolitan University and we're using Access to create a TV off air tape recording database. We create a report preview of a label, using the current record. these labels are used for the dvds and tapes recordings asked for by the academic staff.
The problem I need help with is to produce more than one copy of the label from the current record.
EG I want 4 copies of the current record printed out on a VHS spine label sheet (there are 16 labeles on the sheet) What I don't want the user to do is have to use 4 sheets of labels and end up with one printed label at the top of each sheet. any Ideas?

current routine uses this code but doesn't work

    strWhere = strWhere & " & " & Me.[ProgNo]
    For i = i To copies
    DoCmd.OpenReport strReportName, acViewPreview, , strWhere

If the progno was 1 and copies was 3 then "[progno] = 1 & 1 & 1 & 1" is placed in the reports filter by perameter and won't show the report.
Anyone know the right Syntax.
This thread will be added to the VBA SQL and VBA form forums.


Program Error
Why is it, I still think in terms of spectrum BASIC!

 

[67peshawar294]

Program,
I have posted this several times. I copied the following from another post several months ago.

This solution, thanks to CosmoKramer works very well for me. Add a table to your database that only has one number field. Populate this table "Number" to contain sequential numbers 1 to whatever the maximum number of labels you would ever print.
Add this table to the query that is the record source for your report. Don't join it.
Add the field that contains the numbers to the query grid and set the criteria for this field to:
Between 1 And [Qty]
Jim

 

[ProgramError]

Thanks!

I managed to get the query to work correctly but the report didn't.

I'm using a vba statement to open the query driven report.

strReportName = "VHS label preview"
DoCmd.OpenReport strReportName, acViewPreview, , "qrynumberofcopies"

it seems to select allthe records not just the current one

query parameters are...
Between 1 And [Number of Copies]
and
[Forms]![frmControlPanel2]![frmsubamendsearch].[Form]![ProgNo]

as I said, the query runs ok but the report lists all records.

Program Error
Why is it, I still think in terms of spectrum BASIC!

 

[67peshawar294]

PE,
Try a parameter in your query to select the [progno]. In the "criteria" section of the [progno] field type

Like[Enter program Number]

Use the query as the "Record Source" for the report and have the "numbers" table field in the same query with

Between 1 and [Enter number of labels you want to print]

That will
1st ask you for the [progno] you want to print
2nd it will ask for the number of labels to print.

If you have all the fields in the new query matching the old one it should work without any problem. I scan a UPC number
jim

 

[ProgramError]

Sorry to bring this one up again - sill not fixed.

Im getting a message box asking for 'tblcounter.counter' besides the 'number of copies'. the resultant records produces a no records found report yet it works fine just as a query.

this occurs when i make the call...

DoCmd.OpenReport strReportName, acViewPreview, "qryCopiesCurrentLabel"

underlying sql for the query

SELECT tblOArequests.ProgNo, tblOArequests.DateEntered, tblOArequests.Contact, tblOArequests.Department, tblOArequests.Location, tblOArequests.Title, tblOArequests.BcastDate, tblOArequests.StartTime, tblOArequests.EndTime, tblOArequests.Channel, tblOArequests.Series, tblOArequests.OneTape, tblOArequests.OUno, tblOArequests.NoProgs, tblOArequests.NoTapes, tblOArequests.FutureSeries, tblOArequests.Special, tblOArequests.Comments, tblOArequests.Content, tblOArequests.LabelType, tblOArequests.Printed, tblOArequests.M
FROM tblCounter, tblOArequests
WHERE (((tblOArequests.ProgNo)=[Forms]![frmControlPanel2]![frmsubamendsearch].[Form]![ProgNo]) AND ((tblCounter.Counter) Between 1 And [number of copies]));

Program Error
Programmers do it one finger at a time!

 

[Remou]

Hi
I do not understand why you need this filter: "qryCopiesCurrentLabel" if the SQL is attached to the report. I tried something very similar to the above, but without the filter, and it seemed ok.

 

[TheAceMan1]

How are ya ProgramError . . . . .

Have a look below:

How To Skip Used Mailing Labels and Print Duplicates in Access 2000

See Ya! . . . . . .

 

[ProgramError]

Hi Remou

The reason for the query/filter is the report is a multifunctional report, called by various controls with different criteria.

Hi aceman1

Thanks, that'll be useful for future use bt not on this occasion

Program Error
Programmers do it one finger at a time!

 

[67peshawar294]

PE,
just saw your post this morning. I am not real sharp on SQL but it seems like the "Where clause needs to be

WHERE (((tblOArequests.ProgNo)Like [ProgNo]AND ((tblCounter.Counter) Between 1 And [number of copies]));

 

[ProgramError]

thanks xaf294. Tred the 'like' but produced the same results - query alone shows he correct results ie several copies of the same record
But the report still throws up an inputbox asking for 'tblcounter.counter' !!!!

for additional information counter is a numbered field in the table tblcounter.


Program Error
Programmers do it one finger at a time!

 

[ProgramError]

Halleluya

It finally works...
what I did was capoy the report, put the query directly in the reports 'recordsource' and it worked. Now i can vba code it so I cant programmatically enter the query as the recordsource each time it is called.



Program Error
Programmers do it one finger at a time!

 

[67peshawar294]

PE,
Surrrre wish I knew VB enough to do the programming. Glad you got it to work.
jim