Forcing printing of a record multiple times

  • Thread starter Thread starter MustBNuts via AccessMonster.com
  • Start date Start date
M

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
 
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


MustBNuts via AccessMonster.com said:
Access 2003 linking to a SQL dbase.

I have a query that pulls info based on Job #. A job can have various
Click to expand...
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
Click to expand...
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
Click to expand...
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.)
 
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
 
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
 
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
 
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
 
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
 
Back
Top