Replicate function...copy record 'x' times?

[btj]

I have a set of three forms that allow users to add and/or edit data. The forms have Add and Replicate commands built in. These work great except my client just told me that he may want to copy a record a number of times. So, he wants to click on the Replicate button, have a pop-up form ask the user how many times they wish to replicate the record, and, then, go to a datasheet form where the user can modify these copies.

As I am asking for help, I am stumped! As mentioned, I understand the concept of the replicate function - here is what I am using:
Private Sub cmdReplicate_Click()
On Error GoTo Err_cmdReplicate_Click

Dim Category, Description As Variant
Category = Me![Category]
Description = Me![Description]

DoCmd.GoToRecord , , acNewRec

Me![Category] = Category
Me![Description] = Description

Exit_cmdReplicate_Click:
Exit Sub

Err_cmdReplicate_Click:
MsgBox Err.Description
Resume Exit_cmdReplicate_Click

End Sub

I cannot figure out how to allow for multiple copies and, secondarily, how to then export these copies to some type of temporary location so that the user can edit them.

I could really use some help on this and would appreciate any ideas/advice that you can offer.

Thanks in advance...

- Ben

 

[hermanlaksko]

Whey would anyone want to do this is beond me, but here goes:
do while UserNr >0
DoCmd.GoToRecord , , acNewRec

Me![Category] = Category
Me![Description] = Description
Me!OrgID= me!ID 'enable you to locate the org record
UserNr=UserNr-1
loop

The idear of returning the records to "Live" must be a serious security problem as the end user will the insert new data into a record that may have been changed 60 times since it was copyed in the first place. I rest my case

 

[hermanlaksko]

Why would anyone want to do this is beond me, but here goes:
do while UserNr >0
DoCmd.GoToRecord , , acNewRec

Me![Category] = Category
Me![Description] = Description
Me!OrgID= me!ID 'enable you to locate the org record
UserNr=UserNr-1
loop

The idear of returning the records to "Live" must be a serious security problem as the end user will the insert new data into a record that may have been changed 60 times since it was copyed in the first place. I rest my case

 

[btj]

I agree with you...my biggest concern is that this can easily get out of control (although I have a small group of users).

I have a question about your syntax. I understand the Do While, but am unsure about the use of UserNr and its value of ">0" and "-1". I am confused on what this will allow the user to do when they want to replicate a record.

My thought was that upon clicking the Replicate button, a pop-up form would ask how many replicas the user wanted to create (fill in a txt box). Then, it would create those replicas on OK.

I am sorry if this is what you are indicating, but I am not getting it.

Thanks for your quick response.

- Ben

 

[jebry]

Hi Ben!

Try this:

Private Sub cmdReplicate_Click()
On Error GoTo Err_cmdReplicate_Click

Dim Category, Description As Variant
Dim strNumReplicates As String
Dim intLoopCounter As Integer

strNumReplicates = InputBox("Enter the number of records you want to create: &quot
For intLoopCounter = 1 To CInt(strNumReplicates)
Category = Me![Category]
Description = Me![Description]

DoCmd.GoToRecord , , acNewRec

Me![Category] = Category
Me![Description] = Description
Next intLoopCounter

Exit_cmdReplicate_Click:
Exit Sub

Err_cmdReplicate_Click:
MsgBox Err.Description
Resume Exit_cmdReplicate_Click

End Sub

Given that, I must also chime in that this indeed an potentially dangerous approach. Your users will need to trained as to the utility of the replication and will need to have it hammered home that they must then make their changes to each and every one of the new records that they create. One thing you will need to do is add an autonumber to serve as the primary key for the table so that Access will allow the duplicates. The only other way work this would be to have a separate table to hold new records until the user is finished creating them and then use an Update query to transfer the information to the primary table. For that to work will require a lot more coding.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[btj]

Jeff, as always, thanks for your help. If I can get the code working, I can show my boss how it works. Hopefully, I can also show its dangers, which, I agree, are serious.

Thanks to both you and hermanlaksko for your input.

- Ben

 

[btj]

Jeff...
Thought I was done, but I do have a follow-up. I integrated your code into my code - using ID autogeneration code which needed to be included. I had hoped that this autogeneration would make it less likely that problems would occur.

Anyway, entered the code and after I specify the number of records I want to create (tried 2,5,50), I get a message stating "You can't go to the specified record" and it only adds one replicated record.

Do you have any ideas on what may be causing this?

- Ben

 

[jebry]

Hi Ben!

As a guess, maybe you need to add a command to save the new record at the end of the loop. Post your new code and the table structure, if possible and we can go from there.

Jeff Bridgham
bridgham@purdue.edu