move records to new table one at a time (1 Viewer)

[Ziggy1]

I need to read each record and loop it based on the quantity field, so if a record has a quantity of 3 then the record needs to be duplicated 3 times. The reason I am doing this is because I need to add a serial number to each line.

this is an order file and I am creating a label file with a unique number per item.

I can do this with SQL and recordset loop, but only know how to do by specifying the fields?

this is just a small example with 2 fields... I have a bout 20 and it is a mess to manage with SQL...

strSQL = "insert into tblMK_LabelCOM_BB2(Field,Data)"
strSQL = strSQL + " Values('" & FldNme & "','" & FldDat & "' )"

 

[John Big Booty]

Here's some code I recently wrote for my cellar DB, to allocates purchased wine into empty storage cells in the cellar racking;

[COLOR="SeaGreen"]' Force save of unsaved data[/COLOR]
Me.Dirty = False

[COLOR="SeaGreen"]'Ensure at least one wine has been entered[/COLOR]
If DCount("numberbtls", "tbl_winein") = 0 Then
    MsgBox "Please enter at least one wine to be loaded"
    Exit Sub
End If

[COLOR="SeaGreen"]' Check for wine with no bottle count[/COLOR]
If DCount("numberbtls", "tbl_winein") > 0 And DMin("numberbtls", "tbl_winein") = 0 Then
    MsgBox "There is a wine(s) without a number of bottles. Please correct this before proceeding", vbOKOnly
    Exit Sub
End If

[COLOR="SeaGreen"]' Start Allocation Process[/COLOR]

Dim WineCnt As Integer [COLOR="SeaGreen"] ' Count of wines to be loaded[/COLOR]
Dim BtlCntIn As Integer   [COLOR="SeaGreen"]' Count of bottles for current wine[/COLOR]
Dim strZone As Integer  [COLOR="SeaGreen"]' Current Zone id[/COLOR]
Dim strZonePrior As String [COLOR="SeaGreen"] ' Current Zone Priority[/COLOR]
Dim strZoneID   As String
Dim strCellID As Integer   [COLOR="SeaGreen"] ' Cell Id to populate[/COLOR]
Dim strWineID As Integer    [COLOR="SeaGreen"]' Current wine ID[/COLOR]

DoCmd.Close [COLOR="SeaGreen"]' Close form[/COLOR]

WineCnt = DCount("numberbtls", "tbl_winein")

While WineCnt > 0
    
BtlCntIn = DLookup("NumberBtls", "tbl_winein", "wineid = " & DMin("wineid", "tbl_winein", "Btlcnt = " & DMin("btlcnt", "tbl_winein")))
strZone = DLookup("ZoneID", "tbl_winein", "wineid = " & DMin("wineid", "tbl_winein", "Btlcnt = " & DMin("btlcnt", "tbl_winein")))
strWineID = DLookup("WineID", "tbl_winein", "wineid = " & DMin("wineid", "tbl_winein", "Btlcnt = " & DMin("btlcnt", "tbl_winein")))
    
Select Case strZone [COLOR="SeaGreen"] ' Assign zone sort priority[/COLOR]
    Case 1
    strZonePrior = "Priority1"
    
    Case 2
    strZonePrior = "Priority2"
    
    Case 3
    strZonePrior = "Priority3"
    
    Case 4
    strZonePrior = "Priority4"
End Select
       
    
    While BtlCntIn > 0   [COLOR="SeaGreen"]' Allocate wines[/COLOR]
        strZoneID = DMin(strZonePrior, "tbl_cells", "isnull(wineid)")
        strCellID = DLookup("CellID", "tbl_cells", strZonePrior & "=" & strZoneID)
            
        DoCmd.RunSQL "UPDATE TBL_Cells SET TBL_Cells.WineID = " & strWineID & ", TBL_Cells.DateIn = Date()" & _
                    "WHERE (((TBL_Cells.CellID)=" & strCellID & "));"
                    
        BtlCntIn = BtlCntIn - 1
    Wend
    
   [COLOR="SeaGreen"] ' Delete wine[/COLOR]
    DoCmd.RunSQL "DELETE TBL_WineIn.WineID, TBL_WineIn.NumberBtls, TBL_WineIn.DateIn, TBL_WineIn.ZoneID, TBL_WineIn.BtlCnt " & _
                    "FROM TBL_WineIn " & _
                    "WHERE (((TBL_WineIn.WineID)= " & strWineID & "));"

    WineCnt = WineCnt - 1
    
Wend
 

[COLOR="SeaGreen"]' Generate cell allocation report[/COLOR]

    DoCmd.OpenReport "RPT_WineInToday", acViewNormal, , , acWindowNormal

End Sub

Additionally there are four zones that the wine could be stored in depending on the style.

 

[Ziggy1]

thanks John,

just looking at it ( at a glance) I don't see where it would take an entire row and copy to another table, it just lookes like it is updating values based on criteria.


What I have done for now is stripped down the table I want to create and only used key fields... only 5 fields so easier to manage...so I move the data to the table and apply the serial number in the loop... this table is then re-linked to the query to allow me to get all my fields and because the new mini table will have the correct number of records and will force all the related records to repeat....so far it seems this will work in my automation

I just thought there was a simple way to move records with identical structure to another table

 

[vbaInet]

What field makes each row in the new table unique? Is it an AutoNumber field?

 

[John Big Booty]

thanks John,

just looking at it ( at a glance) I don't see where it would take an entire row and copy to another table, it just lookes like it is updating values based on criteria.


What I have done for now is stripped down the table I want to create and only used key fields... only 5 fields so easier to manage...so I move the data to the table and apply the serial number in the loop... this table is then re-linked to the query to allow me to get all my fields and because the new mini table will have the correct number of records and will force all the related records to repeat....so far it seems this will work in my automation

I just thought there was a simple way to move records with identical structure to another table

With this DB, there is a form bound to a Temp Table, that stores the WineID, the number of Bottles, and the appropriate storage zone, that are to be allocated into storage..

The procedure looks at the first record in the table, cycles through it allocating one bottle at a time to the next available cell in the sort order until it has allocated, the desired number of bottles for that wine into the cellar. It then removes that wine from the temp table moves to the next wineID and repeats the process until the all bottles have been allocated.

 

[Ziggy1]

ok John, I'll test out the logic tomorrow.

vbainet... the way I set it up is there is a table with one field, and one record... a serial number.... in the code I loop through the recordset, and I use the qty field to control a loop that will repeat the current record, at the same time i read the serial number and load it into the table, and increment it while sending an Update sql back to the serial table ( so it just keeps growing whenever a record is added.

I don't want to use an autonumber and i can't store the serial number in the same table as it is a temp table

I'll post the code as an fyi tomorrow, but it is working for that part.... it may be a mute point now as I am focused on getting the entire automation completed.... but I am interested in knowing how to copy the records the way I asked above so I'll be looking at what John said.