Printing Several Lables - Several volumes, different quantities.

[Samuel_Silva]

Hi there !

I'm doing a small program to print some expedition labels and i'm stuck in one problem for quite some time!



I read a document's barcode and the fields are filled automatically because i'm using a query which it's linked to a Excell Datasheet (in read only mode).
If i have just one box, i can print a label by using the query's report but my problem is when i have several boxes to print labels!
I found a way to print "n" labels by creating a table that its counting the "n" Volumes that i pretend which is linked to a query. That was tested and works fine.
What i was not able to to do was the following:
- From 1st box to n-1, the quantity is the same. The last box as the remaining units so i need to calc between nr of volumes (boxes) X Quantity on each box until n-1, the last box is automatically calculated.
- All information is printed on each label : "Vol 1 of n" Qty "x" , "Vol 2 of n" Qty "x" and so on......
- When i click on Print button i register to a table (which i was not able to make it work yet) that the order was processed and printed with that specific values. I need to add to the table also the date and hour when the labels were printed.

I have a small image bellow so you can check.

Thanks in advance.




P.S. Sorry my English....

[June7]

A little lost. Total Qty is the number of units that need to be packed into x number of boxes? How do we know how many units in each box?

If you want to provide files for analysis, follow instructions at bottom of my post.

[Samuel_Silva]

Hi June7 !

The total quantity is stored in Excel file and when i read the barcodes i get that number. In this example i have 600 units.
Sometimes the number is not a "round number". Per example, I might have 21 units which i will use 4 boxes, 3 boxes with 6 units and the last box with 3 units.

[June7]

Still don't understand. Where does the value for units per box come from?

[Samuel_Silva]

From nowhere. I can define how many units per box. I have a simple rule, in "n" volumes (boxes), i have the same quantity from the first box until n-1, in the last box i put the rest of the units remaining.

[June7]

Really need to be explicit in describing process.

You enter number of boxes in Volumes textbox then calculate how many units in each box?

Certainly code can handle but I am not going to try constructing without context.

Do you want to print a sheet of labels? How many per sheet? Or do you print to a label printer using label rolls?

Either post attempted relevant code or provide db.

[Samuel_Silva]

Here's the description:
[General info]
-The data is in an excel file and the program is linked to it. (I'm forced to use it that way because its an export file from a commercial ERP program)
-The Excel file as the Article nr, Order nr, Description, Client, Total Quantity and Date. Nothing to be changed on the excel file or in the table that Access creates when its linked.
[Access Program objective]
-An internal printed document as a barcode with the Order nr. I scan it and the information is displayed on the MS Access Program as you can see in the picture that i have placed on my first post. (so far, so good)
- By default, the information to be printed in the label will be 1 label, 1 box and Total quantity. ( i was able to do that also)
- The problem begins when i need several boxes and the last box will be different from the rest. Here begins the "math" thing.... Total Quantity = nr of Vols("n") X Qty per box (from 1 to n-1) + Qty (last box).
What i pretend here is to place the data on the text boxes to define the nr of Boxes and the Qty from 1 to n-1 ..... but to simplify things, forget about automatic calc to fill the text box field of the remaining box, i can input the value manually also....
- After inputting the information, a pretend to click on print button and the labels are printed and the information is stored in a table the keep a record of the Expedition info (i was not being able to do this)

Here's what i did so far on the labels. The bellow example i have tested for 4 boxes.... this is a result of a query which contains the main table (which is linked to excel) and a table that as a CountID (its just a counter).


P.S.: It will be printed in a label printer.

[June7]

It's still not clear to me what you need. You have not provided any code for analysis.

What would you like different on the labels? The image shows 400 Qty on each label. What should each say?

There is likely more than one way to accomplish what you want.

I have a procedure to batch print variable number of labels for multiple records. Printing is to sheet labels on a normal printer. Code writes records to a 'temp' table.

Consider:

Sub PrintLabels()
Dim y, z, t, q, v
t = 100 'total quantity units
y = 12 'units per box
z  = Int(t / y) + IIf(t Mod y > 0, 1, 0)
For v = 1 To z
    q = IIf(v < z, Int(t / y), t Mod y)
    Debug.Print "Qty: " & q & ", Vol: " & v & " of " & z
Next
End Sub

Reference controls on form for the t and y inputs.
Instead of Debug.Print to immediate window, save record to table.

[Samuel_Silva]

Hello June7,

In this example, i should have 100 units in each box because it's a "round number" which i can divide equally, but most of the times that doens't happens. I want the operator to define volumes and quantities.
The number of labels is working, i followed this link :http://allenbrowne.com/ser-39.html , i have build a query like described, and solved the problem.
I'm not able to show the "1 of 4", "2 of 4"... My definition of Quantity is different from the link and i'm struggling to make it work. I will try to write VB code directly and see what happens because if i'm successfully write this part of the code, i will be also successful in doing the math calc to define the rest.
After the above is solved, i will concentrate in recording printing data in a new table.

I will try to place the program in here later on....

Thanks for your help.

[June7]

As I said, more than one way ...

Instead of counter table and Cartesian query, my suggestion writes records to a 'temp' table. Include a line that first deletes records:
CurrentDb.Execute "DELETE FROM tablename"

The example in post 7 shows Qty 400 on each label. Does that mean there is a Total Quantity of 1600?

Still not clear to me exactly what input required by user and what is calculated. I would expect user input to be the Total Quantity and Units Per Box then calculate how many boxes are required as well as the number of units in the final box. With that in mind, consider this query using the 'counter' table:

SELECT Data.Company, Counter.ID, [enter total quantity] AS TotQty, [enter units per box] AS Units, Int([TotQty]/[Units])+IIf([TotQty] Mod [Units]=0,0,1) AS TotBoxes, IIf([Counter].[ID]<=Int([TotQty]/[Units]),[Units],IIf([TotQty] Mod [Units]=0,0,1)) AS Qty
FROM [Counter], Data
WHERE (((Data.Company)=[enter company]) AND ((Counter.ID)<=Int([enter total quantity]/[enter units per box])+IIf([enter total quantity] Mod [enter units per box]=0,0,1)));

[Samuel_Silva]

On post 7, Quantity is not working correctly. Is showing 400 but should be 100 units each box. 400 is the total amount.

I need to define the nr of boxes and its quantity. The same product might be packed in different box sizes by costumers demands.... so, its not always the same. That's why i must place some fields to be able to set them accordingly and then record it for future verification if needed.


My files are here so everyone can check it.

Access is getting the data from the excel file so you'll need both.

Write A17/101 to A17/106 on the Internal Order Field to be able to see data.... "Print Labels" button is actually a print preview at this stage.

[June7]

You keep saying "I need to define .." but this still doesn't make it clear to me what the USER is inputting. You can 'define' by a calculation. I provided an example query that depends on USER inputting the total to be shipped and the number of units a box can hold. Then calculations 'define' the number of boxes and the 'remainder' held by the last box.

[Samuel_Silva]

To simplify things, here's another example.
I have an order of 23 units, we print a document that contains a barcode in it, and we deliver it to a guy that is going to pack it. (The document is printed in another program, so it's not important were and how)
The operator receives that document and starts to pack the 23 units and arrives to a conclusion that he needs to use 3 boxes that contains 6 units each, and one box with the remaining 5 units. (how he arrives to this conclusion is not also important for the program)
The operator must input that information so he can print 4 labels but 3 labels are identified with Qty: 6 units, and the last label with Qty: 5 units and also must contains " 1 of 4", "2 of 4", and so on.
When i prints the labels, the information is recorded in the program so we can consult it if needed in the future.

If the program makes the calcs for the units needed in the last box or if its input manually, for me is not important at this stage.

Thanks.

[June7]

Then my query should work, with a little correction.

SELECT Company, Counter.ID, [enter total quantity] AS TotQty, [enter units per box] AS Units, Int([TotQty]/[Units])+IIf([TotQty] Mod [Units]=0,0,1) AS TotBoxes, IIf([Counter].[ID]<=Int([TotQty]/[Units]),[Units],IIf([TotQty] Mod [Units]=0,[Units],[TotQty] Mod [Units])) AS Qty
FROM [Counter], Data
WHERE (((Company)=[enter company]) AND ((Counter.ID)<=Int([enter total quantity]/[enter units per box])+IIf([enter total quantity] Mod [enter units per box]>0,1,0)));

Enter 23 total quantity and 6 units per box.

Change the parameters to reference controls on form. Use the query as RecordSource for labels report. The data is available for the label output you describe.

[Samuel_Silva]

Hi June7,

I saw your post too late. I did several changes and i have solved several problems.

Here's what i did:
- Data is now stored in a new table. I did a Query to compare two tables and to add the records that are not in common and it did the trick. Its only missing to to make it automatic when i open the program.
- "1 of 1", "1 of 2" and so on, is working.

What is missing:
- On the report the quantity is not working. Since i'm an VBA analphabet (or close to it) , i will try to make a new query and place a conditional formula to be able to get what i want....
- I need to define the label length and height to be printed in a label printer.
- Since the database is linked to an excel file and it has a huge header (which i cannot remove), i get some weird date on the firsts rows... I must define that the data import must be after line 13.

Here's my files. The preview button is not working since is linked to a deleted report (easy fix...)