Pasting records (2000)

Topic

New Reply

I have a record which l would like to duplicate 5 times , is there any way l can duplicate this record rather than type it out five times?
see attachment. Obvioulsy l could copy and paste each line , but is there an easier way?

Justin.

Reply | Quote

Replies

How does the screenshot relate to your question? I see 11 different records, so it is not clear to me what you want to be duplicated 5 times.

Reply | Quote

I would like to put the all the information in the rows in another record.
I would like to duplicate the record 5 times with this information in.
Is that any clearer?

Justin.

Reply | Quote

No, I still don’t understand. Which record would you like to duplicate 5 times?

Reply | Quote

Attachment deleted by HansV – identical to first one

If you see the attachment. I would like to make another 4 records which contains this information.
Therefore duplicating the information another 4 times.

Justin.

Reply | Quote

Justin, there is no point in attaching the same screenshot twice.

Do you mean that you want to copy the row at the top (with Dave Regan etc.) 4 times together with the 11 subrecords, so that you end up with 5 identical rows, with 5×11=55 subrecords in total?

Reply | Quote

I would like to copy the 11 subrecords into another record so they are duplicated 5 times.
I tried to copy and paste the records but it just takes to long is there any way of copying and pasting the 11 sub records?

Justin.

Reply | Quote

You want to duplicate the 11 subrecords. Should the copies be

a) under the same main record (Dave Regan, Westland Helicopters, etc.)
or
under another existing main record – if so, which one
or
c) under a new main record?

Reply | Quote

c)

Justin

Reply | Quote

Should the fields in the current main record (Dave Regan etc.) be copied into the new main record?

What is the name of the table that contains the main record?
What is the name of the table that contains the subrecords?

On what field or fields are the main record and the subrecords linked? I want to know the field name(s) and data type(s) on the side of the main record, and also on the side of the subrecords.

Reply | Quote

Should the fields in the current main record (Dave Regan etc.) be copied into the new main record? (Yes)

What is the name of the table that contains the main record? (Despatch Note)
What is the name of the table that contains the subrecords? (Despatch Note Items)

On what field or fields are the main record and the subrecords linked? I want to know the field name(s) and data type(s) on the side of the main record, and also on the side of the subrecords.
They are linked by Despatch Note Number which is an auto number.

Justin.

Reply | Quote

I would put a text box txtNumber on the form in which the user can enter the number of copies to be made, and a command button cmdCopy that performs the copy action. The On Click event procedure for the command button is below. Since I don’t know the names of all fields in the subtable, you must complete the list of fields in the code.

Private Sub cmdCopy_Click()
Dim lngOldPK As Long
Dim lngNewPK As Long
Dim i As Long
Dim lngNumber As Long
Dim strSQL As String

On Error GoTo ErrHandler

If IsNull(Me.[Despatch Note Number]) Then
MsgBox “Can’t copy blank record.”, vbExclamation
Exit Sub
End If

If Val(Nz(Me.txtNumber, 0)) <= 0 Then
MsgBox "Please enter a valid number.", vbExclamation
Me.txtNumber.SetFocus
Exit Sub
End If

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

lngNumber = CLng(Me.txtNumber)
lngOldPK = Me.[Despatch Note Number]

DoCmd.SetWarnings False
Echo False

For i = 1 To lngNumber
RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend
lngNewPK = Me.[Despatch Note Number]
RunCommand acCmdSaveRecord
' Note: add the fields you want to copy in both lists
strSQL = "INSERT INTO [Despatch Note Items] " & _
"( [Despatch Note Number], Item, Description ) " & _
"SELECT " & lngNewPK & " As [Despatch Note Number], Item, Description " & _
"FROM [Despatch Note Items] " & _
"WHERE [Despatch Note Number] = " & lngOldPK
DoCmd.RunSQL strSQL
Next i

ExitHandler:
DoCmd.SetWarnings True
Echo True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

I have attached a working demo with a limited number of fields.

Reply | Quote

Thats great.

Thanks once again Hans.

Justin.

Reply | Quote

I would put a text box txtNumber on the form in which the user can enter the number of copies to be made, and a command button cmdCopy that performs the copy action. The On Click event procedure for the command button is below. Since I don’t know the names of all fields in the subtable, you must complete the list of fields in the code.

Private Sub cmdCopy_Click()
Dim lngOldPK As Long
Dim lngNewPK As Long
Dim i As Long
Dim lngNumber As Long
Dim strSQL As String

On Error GoTo ErrHandler

If IsNull(Me.[Despatch Note Number]) Then
MsgBox “Can’t copy blank record.”, vbExclamation
Exit Sub
End If

If Val(Nz(Me.txtNumber, 0)) <= 0 Then
MsgBox "Please enter a valid number.", vbExclamation
Me.txtNumber.SetFocus
Exit Sub
End If

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

lngNumber = CLng(Me.txtNumber)
lngOldPK = Me.[Despatch Note Number]

DoCmd.SetWarnings False
Echo False

For i = 1 To lngNumber
RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend
lngNewPK = Me.[Despatch Note Number]
RunCommand acCmdSaveRecord
' Note: add the fields you want to copy in both lists
strSQL = "INSERT INTO [Despatch Note Items] " & _
"( [Despatch Note Number], Item, Description ) " & _
"SELECT " & lngNewPK & " As [Despatch Note Number], Item, Description " & _
"FROM [Despatch Note Items] " & _
"WHERE [Despatch Note Number] = " & lngOldPK
DoCmd.RunSQL strSQL
Next i

ExitHandler:
DoCmd.SetWarnings True
Echo True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

I have attached a working demo with a limited number of fields.

Reply | Quote

Should the fields in the current main record (Dave Regan etc.) be copied into the new main record? (Yes)

What is the name of the table that contains the main record? (Despatch Note)
What is the name of the table that contains the subrecords? (Despatch Note Items)

On what field or fields are the main record and the subrecords linked? I want to know the field name(s) and data type(s) on the side of the main record, and also on the side of the subrecords.
They are linked by Despatch Note Number which is an auto number.

Justin.

Reply | Quote

Should the fields in the current main record (Dave Regan etc.) be copied into the new main record?

What is the name of the table that contains the main record?
What is the name of the table that contains the subrecords?

On what field or fields are the main record and the subrecords linked? I want to know the field name(s) and data type(s) on the side of the main record, and also on the side of the subrecords.

Reply | Quote

c)

Justin

Reply | Quote

You want to duplicate the 11 subrecords. Should the copies be

a) under the same main record (Dave Regan, Westland Helicopters, etc.)
or
under another existing main record – if so, which one
or
c) under a new main record?

Reply | Quote

I would like to copy the 11 subrecords into another record so they are duplicated 5 times.
I tried to copy and paste the records but it just takes to long is there any way of copying and pasting the 11 sub records?

Justin.

Reply | Quote

Justin, there is no point in attaching the same screenshot twice.

Do you mean that you want to copy the row at the top (with Dave Regan etc.) 4 times together with the 11 subrecords, so that you end up with 5 identical rows, with 5×11=55 subrecords in total?

Reply | Quote

Attachment deleted by HansV – identical to first one

If you see the attachment. I would like to make another 4 records which contains this information.
Therefore duplicating the information another 4 times.

Justin.

Reply | Quote

No, I still don’t understand. Which record would you like to duplicate 5 times?

Reply | Quote

I would like to put the all the information in the rows in another record.
I would like to duplicate the record 5 times with this information in.
Is that any clearer?

Justin.

Reply | Quote

How does the screenshot relate to your question? I see 11 different records, so it is not clear to me what you want to be duplicated 5 times.

Reply | Quote