Printing multiple copies of a report

[Dr_Worm]

Hi

I have a database that produces stickers for items we make. If I want to produce one item X, 5 item Y and 8 item Z, and then print the stikers for them, I want 1 X sticker, 5 Y stickers and 8 Z stickers. There is a quantity feild in each record. is there a VB script to do this.

Dr_Worm

 

[Monkey]

I Think you can start with something like this. I think you'll be able to start with this.




Sub Monkey()
Dim Db As DAO.database
Dim Rs As DAO.Recordset

Set Db = Application.CurrentDb
Set Rs = Db.OpenRecordset("Yourtable here", dbOpenTable, dbReadOnly)
If Rs.RecordCount = 0 Then
Exit Sub
Else
Rs.MoveFirst
Do
If Rs.fields("quantity feild") = "reference in report" Then
''"Rest about printing here I've got no clue and it's weekend"
Else
Rs.MoveNext
End If
Loop Until Rs.EOF
End If
End Sub


Greetings monkey

 

[SydneyGeek]

You could try this strategy:
Use a dummy table to hold the records for printing.

Once you have your query indicating which items to print labels for...
Sticker X 5
Sticker Y 8
Sticker Z 1

etc

...you'll need a routine that does the following --

(1) Deletes all existing records in the dummy table (this can be accomplished with a Delete query)
(2) Loops through your recordset, creating one record in the dummy table for each label that you want to print.
(3) Opens a report based on the dummy table -- this is your sticker layout -- and prints it.

Away from Access at the moment but this may give some food for thought.

 

[SydneyGeek]

To clarify:

(2) Loops through your recordset, creating one record in the dummy table for each label that you want to print.

You'd use the Quantity field in each record to decide how many records to create.

 

[Dr_Worm]

I’m a novice when it comes to writing Code, I know what needs to be done, but I don’t know how to use the quantity field in the table “Cutlist” to loop through the recordset, and create one record for each sticker. The replies so far have helped me (Thankyou Monkey and SydneyGeek). Now all I need is some help with the code.

Thanks in advance

Dr_Worm
ray:

 

[bat17]

Are Stickers X,Y,Z all from the same report or do you have seperate reports for each type of sticker?

Peter

 

[SydneyGeek]

Here's a bit of code that loops through a table, creating multiple records in another table:

Function MakeLabels()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Dim Counter As Integer
Dim txt1 As String
Dim txt2 As String
Dim txt3 As String
Dim txt4 As String
Dim txt5 As String
Dim txt6 As String
Dim i As Integer

'Initialise objects
Set db = CurrentDb()
Set rst1 = db.openrecordset("People")Set rst2 = db.openrecordset("LabelData")
'Create Delete statement to clean out existing LabelData records
strSQL = "DELETE * FROM LabelData;"
DoCmd.SetWarnings False 'Turn deletion warning off
DoCmd.RunSQL (strSQL)
With rst1
  .MoveFirst 'go to first record
  Do Until .EOF 'move through all records in table
    txt1 = !firstname
    txt2 = !lastname
    txt3 = !Address
    txt4 = !city
    txt5 = !State
    txt6 = !postcode
    Counter = !labels
    With rst2
      For i = 1 To Counter
        .AddNew
        !firstname = txt1
        !lastname = txt2
        !Address = txt3
        !city = txt4
        !State = txt5
        !postcode = txt6
        .Update
      Next i
    End With
    .MoveNext
  Loop
End With
DoCmd.SetWarnings True 'Turn deletion warning back on
End Function

Some explanation:
People is the source table
LabelData is the destination table
Both have an ID field; People also has a Labels (Integer) field.
Both have in common the following fields (all text);
FirstName
LastName
Address
City
State
Postcode

The code goes to the first record in People, writes the field values out to the 6 txt variables and the Counter, and creates as many records in LabelData as the value in Counter.


I cheated a bit because I should have based the source records on a query, but I wsa trying to show how you loop through and build the records.

The bit in red could be replaced by the following, to use a query instead:

Place these in the declarations

Dim qdf as DAO.QueryDef
Dim prm as DAO.Parameter

Replace the red text with this

Set qdf = db.QueryDefs("qryWhichLabels")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set rst1 = qdf.OpenRecordset

When the records are created you can display a report based on LabelData