Repeat detail based on record value

[Ymesei]

Does anybody know if its possible to have the detail of each record on a report repeat itself based on a set number? For example, I have a table with customer information. I want to print customer info and I want a copy for each customer. However, each record includes more than one customer, identified by a field indicating the number of customers for that record.

Is there a way to have access repeat detail for each record on the report as many times as there are customers for each record?

Any help is appreciated.

 

[MichaelRed]

It is possible, but not the easiest. The more general approach would be to place the common info in a header and the unique info in a detail. Saves the trees and generally is 'easier' to read.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Ymesei]

Thanks for the response. The reason why I want detail to repeat is because I am trying to build a customer banding system that prints on printable wrist bands. So, I need access to print as many copies of the detail as there are customers so each customer will get a band for him/herself. For example, the following detail would require that 5 bands be printed, so I want access to repeat the detail for this record 5 times:

Cutstomer Name: Joe Smith
Package Name: Super Pack
Tour Date: January 5, 2001
Customers in group: 5

Don't worry about the trees. Each wristband is only about 1".

 

[MichaelRed]

Public Function basRepeatDetail()

'Sample Data
'   Cutstomer Name: Joe Smith
'   Package Name: Super Pack
'   Tour Date: January 5, 2001
'   Customers in group: 5

'Sample Table Made up for the Exercise
'CustName       PkgName     TourDate    NumInGrp
'Joe Smith      Super Pack  1/5/01      5
'Rene Descarte  Math Fun    2/12/01     4


    Dim dbs As Database         'Datbase Reference
    Dim rst As Recordset        'Recordset reference
    Dim rstDup As Recordset     'Temp/DupRecordset reference

    'Dim rstDup As Recordset
    Dim qryDelTemp As QueryDef  'Reference to query to delete records from Temp

    Dim Idx As Integer          'Counter to use for Duplicating Records

    Set dbs = CurrentDb         'Database Instantiation
                                'Recordset instantation
    Set rst = dbs.OpenRecordset("tblTourGroup", dbOpenDynaset)

    'Delete Query Instantation
    Set qryDelTemp = dbs.QueryDefs("qryDelTemp")
    'qryDelTemp.Sql = "DELETE tblTemp.* FROM tblTemp;"

    qryDelTemp.Execute          'Actually delete the records from the temp recordset

    'Dupliact records Recordset Instantation
    Set rstDup = dbs.OpenRecordset("tblTemp", dbOpenDynaset)

    'Finally get to work
    While (Not rst.EOF)                 'Each Record in Orig tbl
        For Idx = 1 To rst!NumInGrp     'Number of Copies to Make
            With rstDup                 'Use "With" to minimize Typing
                .AddNew                 'Below are the Field Names Used for "My" tables
                    !CustName = rst!CustName
                    !PkgName = rst!PkgName
                    !TourDate = rst!TourDate
                    !NumInGrp = rst!NumInGrp
                .Update
            End With
        Next Idx
        rst.MoveNext
    Wend

    'Open the Reoprt in Preview Mode
    DoCmd.OpenReport "rptWristBand", acViewPreview

'Sample Output
'Rene Descarte  Math Fun    2/12/01     4
'Rene Descarte  Math Fun    2/12/01     4
'Rene Descarte  Math Fun    2/12/01     4
'Rene Descarte  Math Fun    2/12/01     4
'Joe Smith      Super Pack  1/5/01      5
'Joe Smith      Super Pack  1/5/01      5
'Joe Smith      Super Pack  1/5/01      5
'Joe Smith      Super Pack  1/5/01      5
'Joe Smith      Super Pack  1/5/01      5


End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Ymesei]

Thank you MichaelRed. I'll try that out. It should work out perfectly for what I need.