Record Multiply 2

[theif68]

I have a table that has 10 records in it with a quantity feild I need to create that record that many times.

Field1 Field2 Qty
001 Dog 10
002 Cat 5

I need to have an output file that will have Line 001 10 times and line 002 5 times to export to excel.

 

[Ed2020]

So what's the question?

Ed Metcalfe.

Please do not feed the trolls.....

 

[theif68]

How do I take that table using a query to output a new table with the feilds multiple times based off of the quantity field?

 

[rjoubert]

Create a query to add it once, then in code, create a loop that will run it x number of times, with x being the value of your Qty field.

 

[theif68]

Thanks for the help. I guess this is a lil bit over my head. Hours of Cut and Paste will have to do the trick.

 

[rjoubert]

I've created a procedure that will append the necessary records to a new table (Table2). Table1 in my code is teh source table that you talked about in your OP...

Public Sub CreateOutput()

    Dim objSourceRS As Recordset
    Dim objDestRS As Recordset
    Dim strAppendSQL As String
    Dim i As Integer
    Dim intLoop As Integer
                 
    Set objSourceRS = CurrentDb.OpenRecordset("Table1")
    Set objDestRS = CurrentDb.OpenRecordset("Table2")
    
    objSourceRS.MoveFirst
    
    Do Until objSourceRS.EOF
        
        intLoop = objSourceRS("Field3")
              
        For i = 0 To intLoop - 1
            objDestRS.AddNew
                objDestRS("Field1") = objSourceRS("Field1")
                objDestRS("Field2") = objSourceRS("Field2")
            objDestRS.Update
        Next
            
        objSourceRS.MoveNext
        
    Loop
    
    objSourceRS.Close
    objDestRS.Close
    Set objSourceRS = Nothing
    Set objDestRS = Nothing
    
End Sub

 

[theif68]

Thanks , but I get a runtime error 13

on:
Set objSourceRS = CurrentDb.OpenRecordset("Test")

Type Mismatch.

 

[Ed2020]

Change the recordset declarations to:

Dim objSourceRS As DAO.Recordset
Dim objDestRS As DAO.Recordset

Ed Metcalfe.

Please do not feed the trolls.....

 

[theif68]

Works Great, Thanks alot guys. Saved me alot of time.