loop to generate records

[Guest]

I have a simple form to print a special label and a table with a field that
contains the number of labels needed for each specific item. How do I get a
record for each label needed so that they can all be printed at once.
Paul

 

[Jeff O via AccessMonster.com]

something like this should work for you.

Dim strval As String
Dim db As Database
Set db = currentdb()
Dim rcount As Integer
Dim i As Integer
Dim rst As Recordset
Set rst = db.OpenRecordset("item_inv_table")
With rst
..MoveLast
..MoveFirst
rcount = rst.RecordCount

'Enter code to print the first label here
e.g; print !label1

Do While i < rcount
.MoveNext

'Enter code to print the rest of the labels here
print !label1

i = i + 1
Loop

End With

rst.close
Set rst = Nothing
Set db = Nothing

End Function

/Jeff

 

[Guest]

The final result for anyone else trying to do this
it includes a counter field to cross check that the numbers are correct


Option Compare Database

Option Explicit



Function CreateLabel()
'Declare variables
Dim db As DAO.Database
Dim rstSrc As DAO.Recordset
Dim rstTar As DAO.Recordset
Dim sSQL As String
Dim NumLabels As Integer
Dim i As Integer 'counter

'Set database
Set db = CurrentDb

'Read Source
sSQL = "Select * from TblLabelDemnd"

Set rstSrc = db.OpenRecordset(sSQL)

sSQL = "Select * from TblLabelPrint where Priority = 9999"

Set rstTar = db.OpenRecordset(sSQL)

Do Until rstSrc.EOF = True

NumLabels = rstSrc.Fields("HibLabels")

For i = 1 To NumLabels

With rstTar
.AddNew
.Fields("NAME") = rstSrc.Fields("NAME")
.Fields("Farm") = rstSrc.Fields("Farm")
.Fields("Priority") = rstSrc.Fields("Priority")
.Fields("HibLabels") = i
.Update
End With


Next i

rstSrc.MoveNext

Loop

'Close
rstSrc.Close
rstTar.Close

Set rstSrc = Nothing
Set rstTar = Nothing

db.Close
Set db = Nothing


End Function