number of print copies varies by value in Quantity field

[Lele]

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

Any help is greatly appreciated.

Lele

Dim iCopiesPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

Private Sub Report_Page()
iCopiesPrinted = iCopiesPrinted - 1
End Sub

 

[John Vinson]

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]

 

[Lele]

Thank you so much for your help on this. I have created the n table and
things are working well...but I am having some problems with the work tickets
for pairs of items. I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to edit
the code to read <= as you advised. Therefore if I am producing 2 pairs I
now get 4 work tickets. Great!

Here is the problem.
The tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

Any help is greatly appreciated.
--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]

 

[Lele]

Thank you so much for your help on this. I have created the n table and
things are working well...but I am having some problems with the work tickets
for pairs of items. I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to edit
the code to read <= as you advised. Therefore if I am producing 2 pairs I
now get 4 work tickets. Great!

Here is the problem.
The tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

Any help is greatly appreciated.
--
Lele


--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]

 

[Lele]

I hope you don't mind if I am abit persistent in seekin g your help. I am so
close but do need a bit more assistance.

I have created the n table and
things are working well...but I am having some problems with the work tickets
for pairs of items. I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to edit
the code to read <= as you advised. Therefore if I am producing 2 pairs I
now get 4 work tickets. Great!

Here is the problem.
The tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

Thank you so much for your help
--
Lele



--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]

 

[Lele]

I need to print work tickets for our production people that reflect the
number of items to be produced. For example if my order is for 3 pillows and
2 bedskirts, I need to produce 5 work tickets.

I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

I have been using an auxiliary table as suggested by MVP John Vinson. At
his suggestion I call it Num. It has one Integer (or Long Integer) field N
as its primary
key. I then Create a Query for the report with my needed tables; include
the Num table with (initially) a Join from from the Quantity field to
N. I then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will gives as many repeats as the value
of quantity for each row. I then use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

THis is all John Vinson's suggestion, and it works super. Thanks again to
you John.

My problem happens when I produce pairs of draperies. If the Unit of
measure (UOM) on the work order is pairs I need 2 copies of the work ticket
produced for each pair - one for left and one for the right panel.

I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to edit
the code to read <= as you advised. This works great and if I am producing
2 pairs I get 4 work tickets. Great!

Here is the problem.
The work tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

I am very much hoping help is out there. I am close but really need help to
fully make this system operational for our staff.

Thanks so much.


--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]

 

[HS Hartkamp]

Indeed close. To finish it off, create an extra table Pairs (or something)
that contains two columns. One for the item that comes in pairs and another
with the sort of pairs. In your case it would contain two rows containing
("Drapes" and "Pair-Left") and ("Drapes" and "Pair-Right").

In your getting the data, replace the table by a table LEFT joining this
pairs table, so that for each row, an extra column appears with the
pair-suffix. Left join so that the pillows still get one row in the final
result, but the drapes get two rows.
This query is the one that you should join with your num-table with the
previously explained <= join.

If this explanation is too scant, let me know cause I've seen it work here.
It should not be too hard to change the report to get the appropriate
output.

Bas Hartkamp.

I need to print work tickets for our production people that reflect the
number of items to be produced. For example if my order is for 3 pillows
and
2 bedskirts, I need to produce 5 work tickets.

I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

I have been using an auxiliary table as suggested by MVP John Vinson. At
his suggestion I call it Num. It has one Integer (or Long Integer) field
N
as its primary
key. I then Create a Query for the report with my needed tables; include
the Num table with (initially) a Join from from the Quantity field to
N. I then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will gives as many repeats as the value
of quantity for each row. I then use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

THis is all John Vinson's suggestion, and it works super. Thanks again to
you John.

My problem happens when I produce pairs of draperies. If the Unit of
measure (UOM) on the work order is pairs I need 2 copies of the work
ticket
produced for each pair - one for left and one for the right panel.

I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to
edit
the code to read <= as you advised. This works great and if I am
producing
2 pairs I get 4 work tickets. Great!

Here is the problem.
The work tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2
pair-
Left)

I am very much hoping help is out there. I am close but really need help
to
fully make this system operational for our staff.

Thanks so much.


--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced
and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to
produce
5 work tickets. I have been using this great code written by Graham
Mandeno
and it works super. (see Below). I would like to refined the code as
follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3
of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item
on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items
on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the
UOM
on the work order is pairs I need 2 copies of the work ticket produced
for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]

 

[Lele]

Hello John,
I have been using your "num" table to help me generate the appropriate
number of packing labels based on the quantity of items ordered. IT WORKS
GREAT!! Thanks again so much. I have shown it to allot of people and they
have all been very impressed!

Now, I would like to refine the system a bit. I would like to allow the user
to enter a number which states how many items can go in one package. So if
the customer purchased a quantity of 10 items, my shipper would enter the
number per bag, let's say 5 and the system would generate 2 labels, the first
would say 1-5 of 10 and the second would say 6-10 of 10. Of course if only 2
items can fit in a bag, the system would generate 5 labels which would read:
1-2 of 10, 3-4 of 10 etc.

Seems like a tall order to me. can it be done? Thanks so much
--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

Second: I would also like to have each of the 5 tickets say 1 of 5 items on
the order, 2 of 5 items on the order, etc. (this way the shipper is more
likely not to miss a piece on the order.)

Just include the same textboxes on the shipping ticket report.

Third, I produce draperies either as single panels or in pairs. If the UOM
on the work order is pairs I need 2 copies of the work ticket produced for
each pair - one for left and one for the right.

That will be a bit trickier - you'll need an IIF to identify which
quantities are pairs.

John W. Vinson[MVP]