Looking for VBA code to duplicate records

[P38]

For one special report that must meet the specification of a third party we must create a database that duplicates each multiple item individually as shown below:

Our records:

Item	Quantity
Red widget	1
Yellow Widget	5
Blue Widget	1
Green Widget	3

How it is to be reported:

Item	Quantity
Red widget	1
Yellow Widget	1
Yellow Widget	1
Yellow Widget	1
Yellow Widget	1
Yellow Widget	1
Blue Widget	1
Green Widget	1
Green Widget	1
Green Widget	1

We have accomplished the duplication in Excel. Here is the Excel code (Quantity in Column "F"):

 
Sub RepeatRows()
Dim lRow As Long
Dim RepeatFactor As Variant
 
    lRow = 1
    Do While (Cells(lRow, "A") <> "")
       
        RepeatFactor = Cells(lRow, "F")
        If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
               
           Range(Cells(lRow, "A"), Cells(lRow, "F")).Copy
           Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "F")).Select
           Selection.Insert Shift:=xlDown
          
           lRow = lRow + RepeatFactor - 1
        End If
   
        lRow = lRow + 1
    Loop
End Sub

I am not up on VBA code for Access so I am looking for help on the code that will do the duplication within Access 2007.

[help_me_with_access]

depends what the tables look like...

[P38]

Here is some additional information about the original table and the repeated rows table. The original table has 18 fields and 902 records. The repeated rows table does not require all the fields from the original table. A new table would be created which has the necessary fields plus one calculated field. The repeated rows table should grow to 2,664 records with duplication.

Repeated rows table
Field1 - record number (auto-number in original table)
Field2 - text (255 characters max)
Field3 - number (this field contains the quantity amount)
Field4 - calculated number field (currency)
Field5 - date (short date)
Field6 - number linked to lookup table
Field7 - number linked to lookup table

Fields 1 and 3 do not appear in the report.

[P38]

Since no one here had any ideas I went looking elsewhere and found Microsoft code for repeating records in a report http://support.microsoft.com/kb/207664. Modifying that code a little gave me my results. Red code deleted and bold code added.

Option Explicit
    Dim intPrintCounter As Integer
    Dim intNumberRepeats As Integer

    Private Sub Report_Open(Cancel As Integer)
       intPrintCounter = 1
       intNumberRepeats = Forms!PrintForm!TimesToRepeatRecord
       intNumberRepeats = 0
    End Sub
                       
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
       ' Note: intNumberRepeats and intPrintCounter are initialized
       ' in the report's OnOpen event.
    On Error GoTo -1
    If IsNull(Field3) = "True" GoTo Out
    intNumberRepeats = Field3
       If intPrintCounter < intNumberRepeats Then
          intPrintCounter = intPrintCounter + 1
          ' Do not advance to the next record.
          Me.NextRecord = False
       Else
          ' Reset intPrintCounter and advance to next record.
          intPrintCounter = 1
          Me.NextRecord = True
       End If
    Out:
    End Sub

[P38]

Since no one here had any ideas I went looking elsewhere and found Microsoft code for repeating records in a report http://support.microsoft.com/kb/207664. Modifying that code a little gave me my results. Red code deleted and bold code added.

Option Explicit
    Dim intPrintCounter As Integer
    Dim intNumberRepeats As Integer

    Private Sub Report_Open(Cancel As Integer)
       intPrintCounter = 1
       intNumberRepeats = Forms!PrintForm!TimesToRepeatRecord
       intNumberRepeats = 0
    End Sub
                       
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
       ' Note: intNumberRepeats and intPrintCounter are initialized
       ' in the report's OnOpen event.
    On Error GoTo -1
    If IsNull(Field3) = "True" GoTo Out
    intNumberRepeats = Field3
       If intPrintCounter < intNumberRepeats Then
          intPrintCounter = intPrintCounter + 1
          ' Do not advance to the next record.
          Me.NextRecord = False
       Else
          ' Reset intPrintCounter and advance to next record.
          intPrintCounter = 1
          Me.NextRecord = True
       End If
    Out:
    End Sub