Duplicate Data from Selected Fields on a Form

INvicta

Board Regular
Joined
May 18, 2007
Messages
108
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
Code:
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Bernard, try...

Code:
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

Board Regular
Joined
May 18, 2007
Messages
108
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
May 18, 2007
Messages
108

ADVERTISEMENT

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

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

Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
****, my fault! Change this
Code:
For x = i To nCopies
to this
Code:
For x = 1 To nCopies

Denis
 

INvicta

Board Regular
Joined
May 18, 2007
Messages
108
Denis,
You are an absolute star - thank you.
Following works fine.
Bernard

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top