Duplicate Data from Selected Fields on a Form

[INvicta]

I've used the Duplicate Record option on a form's command button but can't work out how to adapt it so that it only copies a couple of text fields.

I've also been asked if it's possible to stipulate the number of copies but thats not a prerequisite.

Using the standard code I've got as far as the following, any suggestions would be very welcome.

Bernard

Private Sub CmdDuplicateRecord_Click()

On Error GoTo Err_CmdDuplicateRecord_Click

Dim txtStockCodeNo As String
Dim txtFixtureNumberCom As String

Dim dbs As Dtabase
Dim rst As Recordset
Dim i As Integer


'Following Duplicates ALL fields - from in-built duplicate record "Wizard" function in Access
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_CmdDuplicateRecord_Click:
    Exit Sub

Err_CmdDuplicateRecord_Click:
    MsgBox Err.Description
    Resume Exit_CmdDuplicateRecord_Click

'How to duplicate two specific Text fields? eg StockCodeNo and FixtureNumberCom
'How to duplicate any number of these?

End Sub

 

[SydneyGeek]

Hi Bernard, try...

Private Sub CmdDuplicateRecord_Click() 

On Error GoTo Err_CmdDuplicateRecord_Click 

Dim txtStockCodeNo As String 
Dim txtFixtureNumberCom As String 
Dim nCopies As Integer, x as Integer

nCopies = CInt(InputBox("How many copies?", "Duplicate Record", 1))

'**Assign values to variables
txtStockCodeNo = Me.StockCodeNo.Value
txtFixtureNumberCom = Me.FixtureNumberCom.Value

'**Create the copies
For x = i to nCopies
  DoCmd.GoToRecord , , acNewRec
  Me.FixtureNumberCom.Value = txtFixtureNumberCom
  Me.StockCodeNo.value = txtStockCodeNo
  DoCmd.RunCommand acCmdSaveRecord
Next x 

Exit_CmdDuplicateRecord_Click: 
    Exit Sub 

Err_CmdDuplicateRecord_Click: 
    MsgBox Err.Description 
    Resume Exit_CmdDuplicateRecord_Click 

End Sub

Untested, but the idea is that you put the values in the 2 variables and then loop through creating and saving records.
If the ID field is not an AutoNumber, this will fail.

Denis

 

[INvicta]

Denis,
Thank you for your help.

I applied your code to the command button on the form.
When it ran no Input box appeared and code errored out at
txtFixtureNumberCom = Me.FixtureNumberCom.Value
on the FixtureNumberCom between Me. and .Value.

Error message = Compile error: Method or data member not found.

Each record number is an Autonumber
The two fields (out of 12) I am trying to copy are both text.

Any thoughts please?
Bernard

 

[SydneyGeek]

Bernard, try taking out that line after the = sign.
Then enter Me. and use the Intellisense dropdown to select the control from the list.
Also, try it with and without the .Value bit

Denis

 

[INvicta]

Denis,
Sorry for being such a plank - I miss spelt the field name

After changing this and updating the field in the form I then got it to run but after a number has been entered to indicate the number of copies required the following error message now pops up
Error:94
Description; Invalid use of Null

Bernard

Revised code follows

Private Sub CmdDuplicateRecord_Click()

On Error GoTo Err_CmdDuplicateRecord_Click

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim txtStockCodeNo As String
Dim txtFixtureNumberCom As String
Dim nCopies As Integer, x As Integer

nCopies = CInt(InputBox("How many copies?", "Duplicate Record", 1))

'**Assign values to variables
txtStockCodeNo = Me.StockCodeNo.Value
txtFixtureNumberCom = Me.FixtureNumberCom.Value

'**Create the copies
For x = i To nCopies
  DoCmd.GoToRecord , , acNewRec
  Me.FixtureNumberCom.Value = txtFixtureNumberCom
  Me.StockCodeNo.Value = txtStockCodeNo
  DoCmd.RunCommand acCmdSaveRecord
Next x

'Exit_CmdDuplicateRecord_Click:
    'Exit Sub

Err_CmdDuplicateRecord_Click:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    'Resume Exit_CmdDuplicateRecord_Click


End Sub

 

[SydneyGeek]

****, my fault! Change this

For x = i To nCopies

to this

For x = 1 To nCopies

Denis

 

[INvicta]

Denis,
You are an absolute star - thank you.
Following works fine.
Bernard

Private Sub CmdDuplicateRecord_Click()

On Error GoTo Err_CmdDuplicateRecord_Click

Dim txtStockCodeNo As String
Dim txtFixtureNumberCom As String
Dim nCopies As Integer, x As Integer

nCopies = CInt(InputBox("How many copies?", "Duplicate Record", 1))

'**Assign values to variables
txtStockCodeNo = Me.StockCodeNo.Value
txtFixtureNumberCom = Me.FixtureNumberCom.Value

'**Create the copies
For x = 1 To nCopies
  DoCmd.GoToRecord , , acNewRec
  Me.FixtureNumberCom.Value = txtFixtureNumberCom
  Me.StockCodeNo.Value = txtStockCodeNo
  DoCmd.RunCommand acCmdSaveRecord
Next x

Exit_CmdDuplicateRecord_Click:
    Exit Sub

Err_CmdDuplicateRecord_Click:
    MsgBox Err.Description
    Resume Exit_CmdDuplicateRecord_Click


End Sub