Forcing printing of a record multiple times

[MustBNuts via AccessMonster.com]

Access 2003 linking to a SQL dbase.

I have a query that pulls info based on Job #. A job can have various types of parts, and on some occassions, more than one of a specific part. Example: Job 1 has 3 different part types, A, B, and C; but this job has 3 B's.

My table holds the quantity of each type (not each individual part) so when I pull a query to print a label for each type, no problem. Now I need to print one label for A and C, but 3 identical labels for B.

For the life of me I cannot figure out how to force a report to print the given labels based on the Qty field (ie, if Qty=3 then loop the output three times.). Access is not letting me use me.PreviousRecord (not available, even though me.NextRecord is available.)

What I am trying to do is print A, B, B, B, C in a report.

Any takers?

MBN

 

[PC Datasheet]

I can only answer generally! You need to design the query your label report
is based on to give you one record for "A", three records for "B" and one
record for "C".

Your solution is in the query and not in the report!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Access 2003 linking to a SQL dbase.

I have a query that pulls info based on Job #. A job can have various

types of parts, and on some occassions, more than one of a specific part.
Example: Job 1 has 3 different part types, A, B, and C; but this job has 3
B's.

My table holds the quantity of each type (not each individual part) so

when I pull a query to print a label for each type, no problem. Now I need
to print one label for A and C, but 3 identical labels for B.

For the life of me I cannot figure out how to force a report to print the

given labels based on the Qty field (ie, if Qty=3 then loop the output three
times.). Access is not letting me use me.PreviousRecord (not available,
even though me.NextRecord is available.)

 

[MustBNuts via AccessMonster.com]

Would that it were that easy. The data available is the part, the quantity, and some misc. specs. This is not a calculated query - I do not have a table with the individual items listed out....that is the problem. I was hoping someone knew of some VB code that might be able to look at each record, determine the value of QTY and dup the record x number of times.

I've tried, to no avail.

Thanks anyway

 

[Wayne Morgan]

Yes, it is possible. If the qty value is available, VB code can find it and
adjust the number of times that record is printed. It would be similar to
printing labels with multiple copies. There is a sample in a Microsoft
Knowledge Base article that shows how to print multiple labels or skip used
labels on a sheet. You will need to modify this to adjust the copy value for
each record.

http://support.microsoft.com/default.aspx?scid=kb;en-us;299024

 

[MustBNuts via AccessMonster.com]

Yup, Wayne, the qty is available for each item in the query. Thank you so much, I am going to take a look at the link...I'll follow up with how I make out on this.

Again, thanks

MBN

 

[MustBNuts via AccessMonster.com]

Follow up: yes this code works great - I pass QTy over and all is well. However, I am encountering an interesting problem that I'm not sure how to fix: The database is being called by a separate program. It is sometimes closed, will open for preview of a requested report. I've created reports without any problems before (but never with these layout properties in a module before). If the database is already open, the report shoots off without any problem. If the dbase is closed, it errors out.

If I request two reports to preview, and this report is the second, again, everything is okay, even if it starts out as closed.

Am I missing something...where/how can I trap the error if it only occurs when the dbase is closed? Any and all suggestions appreciated.

MBN

 

[MustBNuts via AccessMonster.com]

For anyone who is remotely interested in this, here is the answer to the latest problem: once you create a module for the layout, it appears you cannot have additional layout instructions in the report itself, move everything to the module, and all is right with the world!

MBN