Duplicating a selected amount of records

[DMT Dave]

Good Day all, @Orange, The Sun is up in Wales so WGM will beat you to this one



I am trying add a selected amount of records based on 1 order number

So if a Customer has got 20 x orders on 1 x Order No i am wanting to for example add 3 more so we end up with 23

when i try the code below, I have added 3 when prompted by iInput then 35 records are added and not 23!!!

Dim x As Integer, iInput As Integer
Dim strSQL As String, strOrderNo as String


If Me.cboStatus = "Duplicate" Then


iInput = InputBox("Enter How Many Records To Duplicate ?")


    For x = 1 To iInput


            strSQL = "INSERT INTO tblEdit " _
                & "SELECT tblEdit.* " _
                & "From tblEdit " _
                & "WHERE (((tblEdit.OrderNo)= '" & strOrderNo & "'));"
    
            DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
    
            Me.Status = "Planning"
            
    Next
            If Me.Dirty Then Me.Dirty = False
            
               Me.cboDelTo.SetFocus
               Me.cboDelTo.SelStart = 0
            Forms!frmMainMenu!frmIndex1DS.Requery
    
    Debug.Print strSQL


End If

[Welshgasman]

Break it down into steps.
See how many records you get with the Select.

FWIW if the orderno has 20 lines, then you will add 3 * 20? which I do not think is what you want.
Normally a Cartesian query would solve this.

[orange]

Dave,

Please give us an example of an Order and OrderDetails.
Paul is guessing as to what you want/need.
So an example will help with context.

I'm not sure what you mean by "duplicating".

[DMT Dave]

Hi Guy's just passed my semi functioning brain, an input of 3 on a current order of 6 will be 18 + 3 (21 Records)

I ned to a the primary key record number field to narrow the calculation down to 1 Record No x input of duplicates

SO i have x 2 criterias, 1 OrderNumber and 1 x Record No from primary key field ?

Will need to come back later on it...

Kindest

[Welshgasman]

Nope
First Pass 6 + 6 = 12
Second Pass 12 + 12 = 24
Third Pass 24 + 24 = 48

[orange]

Dave,
" an input of 3 on a current order of 6 will be 18 "?????

Consider: (Please revise to match your requirement)

You have an Order(orderno 200) and that order currently involves 6 parts.
You want to add 3 more parts to Orderno 200, so you end up with 9 parts on Orderno 200.

[DMT Dave]

Hi Guy's i think i just have the calculation wrong like you have stated

Like WGM has mentioned each selected value amount will just keep multiplying...

I think i need to add:

Multiply input selected by Dlookup RecordNo WHERE OrderNo = String

I think that's solution, will try a little later but as always, thank you guy's for your input....