How to make multiple copies of the whole row in Access by entering a number of copies?

Question

I need to make multiple copies of the data that was entered in the row. So, I need to create a button in Access where first I'm going to select the row that I need to make copies, and when I click on the button, the message will pop out and ask to enter the number of how many copies are needed. When you enter the number, it will create all the copies in that same table.

Macro sample for doing copy only, there I need a message box with input

I was trying to work on code, but it doesn't work. In the picture, you can see that I was able to create the Macros that actually make single copies of the row selected, but I need it to make 20-30 copies sometimes, so I need the Message box where I would input how many copies I need. Help, please!

Duplicate of [this](https://stackoverflow.com/questions/67904640/how-to-make-multiple-copies-of-the-whole-row-in-access). — Gustav, Jun 10 '21 at 17:37
Please heed the duplicate notice and not re-ask exact question. Avoid using macros for complex multi-step needs. Why tag `sql` if you ignore the duplicate's `sql` solutions? — Parfait, Jun 10 '21 at 19:35

score 1 · Answer 1 · edited Jun 10 '21 at 16:09

Maybe you can try some variation of this procedure:

Private Sub Comando11_Click()

  Dim dbs As Database
  Set dbs = OpenDatabase("test.accdb")

  Dim times As Integer
  times = InputBox("How many times", "Times")

  For i = 1 To times
    dbs.Execute "INSERT INTO test (c1, c2) SELECT c1, c2 FROM test WHERE Id=" & Me.Id.Value
  Next i

  dbs.Close

End Sub

Where "tests.accdb" must be replaced by your access file.

The INSERT statement gets the record selected in the form. In my case, the Id is the primary key in the table, so I select the record with Id = Me.Id.Value.

Then, it inserts that record on the same table, excluding the the Id column, which is autoincrementall and primary key, so it couldn't be inserted again if some record already exists with that value.

It executes this query so many times as requested by the inputbox.

How to make multiple copies of the whole row in Access by entering a number of copies?

1 Answers1