Query for price stickers, need to expand based on number

[Brettt3]

I have an inventory table with ITEMID, DESCRIPTION, ITEMCOUNT and ITEMPRICE.

I want to generate labels for each item, but I need multiple labels based on the value in ITEMCOUNT so I can put one label on each item on the shelf.

Any ideas on how to formulate a query that will produce multiple rows based on ITEMCOUNT's value? Or some other way to get the same result?

thanks much
Brett

 

[kathryn]

I would probably use a temporary table and print the labels from the temp table.

If your original table is tblInventory, copy and paste the table to a new table called tblInventoryTemp. Paste just the structure, not the data.

Then in code, you want to step through the original table and loop for each record based on item count.

something like this.


dim rst as recordset
dim rstTemp as recordset
dim x as integer

'delete all records in temp table
currentdb.execute "Delete * from tblInventoryTemp"

set rst = currentdb.openrecordset("tblInventory&quot
set rstTemp = currentdb.openrecordset("tblInventoryTemp&quot
rst.movefirst


do until rst.eof

for x = 1 to rst!itemcount
rstTemp.AddNew
rstTemp!Itemid = rst!itemid
rstTemp!Description= rst!description
rstTEmp!itemprice = rst!itemprice
rsttemp.Update
next

rst.movenext
loop

This is rough, but it should get you started.


Kathryn

 

[Brettt3]

I ended up doing it with a report and putting code in the OnFormat for the Detail:

Option Explicit

Dim LabelsPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim LabelsNeeded As Integer
If Me.InvoiceQty > 0 Then
LabelsNeeded = (Me.InvoiceQty * 1.05) + 1 'provide at least 1 extra
LabelsPrinted = LabelsPrinted + 1
If LabelsPrinted < LabelsNeeded Then
Me.MoveLayout = True
Me.NextRecord = False 'don't get next record yet
Me.PrintSection = True
Else
' ok to start on next one
LabelsPrinted = 0
End If
Else
' don't print this one if 0 quantity
Me.MoveLayout = False
Me.NextRecord = True
Me.PrintSection = False
End If
End Sub