Repeat same label mutiple times for individual SKU

[oim]

Hi,

I am developing a DB for a clothing wholesaler. I have created sticky labels (for garment swing tickets giving price,colour, size etc)for each stock keeping unit(SKU). However this only provides me a single label for each SKU. I am currently doing work-around by pasting into excel then copying the required number of SKU's to create a large table that I then import into access and run the report.

This is very time consuming and frustrating b/c there must be a beter way!!

Can you please point me in the correct direction on how I can achieve multiple SKU labels (in a report) based on the quantity sold for the individual SKU. Ideally I would also like to have a buffer of an extra 15% to allow for wastage in factory.

Thanks for your help.

 

[KenReay]

Hi

I am sure there is more than one way to do this, but...

Add a new table, with only two columns (Id, Autonumber; SKU)

Populate this table with one row per label per SKU, ie if you want 10 labels for a given SKU put 10 rows in it)

Base you lable print on a query which is a join between your main table and this new table

This will give you (say) ten rows for the given SKU, and hence 10 labels.

Your wastage problem you can solve by adjusting the arithmetic with calculates how may rows to add (eg 10 * 1.15 or whatever) Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[oim]

Thanks Ken,

I can create a query that gives me the number of SKU required including wastage.

SKU1 50
SKU2 45
SKU3 65
.
.
etc

However, how do I populate the table with 50 rows for SKU1 followed by 45 rows for SKU2 etc...........as you suggest.

This is the missing step for me - I'm just starting out on the VBA side so I'm not sure if I have to use a loop or something like that.

Thanks for your assistance.
Owen

 

[KenReay]

Hi

Well the simplest way I would say is to create a loop which reads through your query, and then appends a record n times depending on the number of labels.

You do not say if you are using Access97 or Access2000 or AccessXP, so I am unsure if youa re using ADO or DAO

If I assume ADO, the you need something like

DIM cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim rst as ADODB.Recordset
Dim i as Integer

Set Cn = CurrentProject.Connection
Set Rs = NEW ADODB.Recordset
Set Rst = New ADODB.REcordset
'
Rs.Open "SELECT * FROM LabelQuery;",cn,...etc
Rst.Open "SELECT * FROM tblTemp;",cn,..etc
If Not Rs.EOF Then
Do Until Rs.EOF
For i = 1 To Rs!NoLAbels
Rst.AddNew
Rst!ProdId = Rs!ProdId
Rst.Upadte
Next i
Loop
End If

Note the above is not tested.

You need to use your own table names, column names etc

There is a neater way to add the records than Rst>AddNwe, using SQL APPEND, but I am just making the switch DAO to ADO and I am not sure of the syntax
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[oim]

Kevin,

Thankyou very much and have a great xmas. I'll give this a go.

Owen

 

[Falcon99]

I have another way that might help.

Each record to print for a label requires a qty field (Qty) for the number of labels to print.

IE:

SKU#1 PartA 3
SKU#2 PartB 7
SKU#3 PartC 1

On your label template (rptLabels) place a text control for the qty field. Place another text control and label it LCount. You can make these invisible.

In the Detail On Print property place the following command:

=DetailOnPrint()

Create a module and create the following function:

Function DetailOnPrint()
Dim rptT As Report

Set rptT = Reports!rptLabels

rptT!LCount = rptT!Qty
If rptT.PrintCount < rptT!LCount Then
rptT.NextRecord = False
End If


rptT!LCount = Str$(rptT.PrintCount)

End Function


I have also created forms where I could enter the quanity as desired.

Hope this helps. It's pretty simple once you understand how it works. Got it out of a book a long time ago.

Falcon99