Duplicating Record problem
Hi every one, I need to create a new record via button on the form will copy details and paste it for the new record, I’m using the code below, It doesn't paste anything, it’s doing the copying but it’s copying the form controls names as well, I pasted the content of the clipboard in excel sheet and there was 2 rows the top row having the names of form controls instead of the fields names and the bottom Row have the copied record well but not in order,
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Also I used the command Button Wizard still doing same thing
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Also I used the command Button Wizard still doing same thing
It is usually much faster to use DAO to duplicate a record, and it requires no update of the form.
An example is here:
How do I write VBA code to duplicate a record
An example is here:
How do I write VBA code to duplicate a record
Thank you Gustav for the reply,
Yes I pasted the code of the link but still giving me exactly same result as I mentioned above
Yes I pasted the code of the link but still giving me exactly same result as I mentioned above
What does not in order mean?
If the pasted record is expected to be position with the current records, you must requery the form after the copying:
If the pasted record is expected to be position with the current records, you must requery the form after the copying:
Me.Requery
What I meant is when I pasted the clipboard content into the Excel sheet It showed me 2 Rows the top one has all the names of the controls on the Form.
the Second Row has the form entered details records but not in the same sequence as it is in the table, anyway I think if I fix the problem and prevent it from copying the controls names of the form then that more likely would solve the problem
the Second Row has the form entered details records but not in the same sequence as it is in the table, anyway I think if I fix the problem and prevent it from copying the controls names of the form then that more likely would solve the problem
(It's MS Access Form),
Yep should do it, but for some reason it's pasting nothing in the new record, because what's pasting is conflicting with the table so it gets rejected, anyway It's late Midnight now I'll leave it till tomorrow I will try it on a different form, MS Access,
Yep should do it, but for some reason it's pasting nothing in the new record, because what's pasting is conflicting with the table so it gets rejected, anyway It's late Midnight now I'll leave it till tomorrow I will try it on a different form, MS Access,
That's what the middle section is for - to adjust or skip some fields for various reasons:
With fld
If .Attributes And dbAutoIncrField Then
' Skip Autonumber or GUID field.
ElseIf .Name = "Qtr" Then
' Pick user selected value from unbound textbox named, say, NewQtr.
.Value = Nz(Me!NewQtr.Value, 0)
ElseIf .Name = "SomeOtherField" Then
' Insert other value.
.Value = SomeOtherValue
Else
' Copy field.
.Value = rst.Fields(.Name).Value
End If
End With
Ronald,
When you copy the form and paste it into Excel, what you are pasting is what is on the FORM not what is in a table. So, I would expect to see the fields pasted based on the tab order of the form. Keep in mind that the tab order might be different from the visible order. So, if the form shows fld1, fld2, fld3 but they paste as fld2, fld1, fld3, then look at the tab order and you will see that that is the reason. You can reset the tab order globally by pressing the tab order icon or you can do it for individual controls by changing the tab index value.
Once you move into a database world, copy and paste are not the preferred method of copying data. You can use the RecordsetClone to insert new rows or you can use an append query. Just keep in mind that it makes no sense to insert duplicate records so you need to consider that in your code logic and make sure you don't duplicate the primary key or a unique index.
When you copy the form and paste it into Excel, what you are pasting is what is on the FORM not what is in a table. So, I would expect to see the fields pasted based on the tab order of the form. Keep in mind that the tab order might be different from the visible order. So, if the form shows fld1, fld2, fld3 but they paste as fld2, fld1, fld3, then look at the tab order and you will see that that is the reason. You can reset the tab order globally by pressing the tab order icon or you can do it for individual controls by changing the tab index value.
Once you move into a database world, copy and paste are not the preferred method of copying data. You can use the RecordsetClone to insert new rows or you can use an append query. Just keep in mind that it makes no sense to insert duplicate records so you need to consider that in your code logic and make sure you don't duplicate the primary key or a unique index.
Also, if you are trying to copy a record in Access, I prefer to use a SQL Statement, something like (assumes that Field1 is the ID - autonumber) field:
HTH
Dale
lngID = me.txt_ID
strSQL = "INSERT INTO yourTable (Field2, Field3, Field4,..., FieldN) " _
& "SELECT Field2, Field3, Field4,...FieldN " _
& "FROM yourTable WHERE ID = " & me.txt_ID
currentdb.execute strsql, dbfailonerror
me.requery
HTH
Dale
Thanks for both reply, actually I was trying to get the code from Dale because it would give me the option of select what I need,
But I couldn't get it working ,
Please Dale or Someone tells me what is wrong with it.
Dim lngID As Long
Dim strSQL As String
lngID = Me.TxOrdID
strSQL = "INSERT INTO Tbl_Stock_Ordrs (Field2, Field3, Field4) " _
& "SELECT Field2, Field3, Field4" _
& "FROM yourTable WHERE OrdID = " & Me.TxOrdID
CurrentDb.Execute strSQL, dbFailOnError '<<Showing error here
Me.Requery
But I couldn't get it working ,
Please Dale or Someone tells me what is wrong with it.
Dim lngID As Long
Dim strSQL As String
lngID = Me.TxOrdID
strSQL = "INSERT INTO Tbl_Stock_Ordrs (Field2, Field3, Field4) " _
& "SELECT Field2, Field3, Field4" _
& "FROM yourTable WHERE OrdID = " & Me.TxOrdID
CurrentDb.Execute strSQL, dbFailOnError '<<Showing error here
Me.Requery
You miss a space here:
So, first try manually to duplicate a record, identifying the offending field(s).
Then adjust the code here to skip or modify the values of those fields to allow for an insert:
& "SELECT Field2, Field3, Field4 " _
what's pasting is conflicting with the table so it gets rejected,
So, first try manually to duplicate a record, identifying the offending field(s).
Then adjust the code here to skip or modify the values of those fields to allow for an insert:
With fld
If .Attributes And dbAutoIncrField Then
' Skip Autonumber or GUID field.
ElseIf .Name = "FirstOffendingField" Then
' Insert non-offending value.
.Value = SomeValue
ElseIf .Name = "NextOffendingField" Then
' Insert other non-offending value.
.Value = SomeOtherValue
' etc.
Else
' Copy field.
.Value = rst.Fields(.Name).Value
End If
End With
Finally Go it working, Thank you Gustav,
Here is my code
Dim rst As DAO.Recordset
Dim rstAdd As DAO.Recordset
Dim fld As DAO.Field
Dim Count As Integer
Dim Item As Integer
Dim Bookmark As Variant
Dim NewId As Long
'Copy record.
Set rstAdd = Me.RecordsetClone
Set rst = rstAdd.Clone
'Move to current record.
rst.Bookmark = Me.Bookmark
With rstAdd
.AddNew
For Each fld In .Fields
With fld
If .Attributes And dbAutoIncrField Then
'Skip Autonumber or GUID field.
ElseIf .Name = "OrdNm" Then
ElseIf .Name = "DtPaid" Then
ElseIf .Name = "Paid" Then
'Pick user selected value from unbound textbox named, say, NewQtr.
' .Value = Nz(Me!NewQtr.Value, 0)
Else
'Copy field.
.Value = rst.Fields(.Name).Value
End If
End With
Next
.Update
End With
'Store location of new record.
Bookmark = rstAdd.Bookmark
rst.Close
rstAdd.Close
'Move to the new recordcopy.
Me.Bookmark = Bookmark
Set fld = Nothing
Set rstAdd = Nothing
Set rst = Nothing
DoCmd.RunCommand acCmdRecordsGoToLast
Me.TxInvNm.Value = Nz(DMax("OrdNm", "[Tbl_Stock_Ordrs]")) + 1
But I had to disable this line: .Value = Nz(Me!NewQtr.Value, 0)
Here is my code
Dim rst As DAO.Recordset
Dim rstAdd As DAO.Recordset
Dim fld As DAO.Field
Dim Count As Integer
Dim Item As Integer
Dim Bookmark As Variant
Dim NewId As Long
'Copy record.
Set rstAdd = Me.RecordsetClone
Set rst = rstAdd.Clone
'Move to current record.
rst.Bookmark = Me.Bookmark
With rstAdd
.AddNew
For Each fld In .Fields
With fld
If .Attributes And dbAutoIncrField Then
'Skip Autonumber or GUID field.
ElseIf .Name = "OrdNm" Then
ElseIf .Name = "DtPaid" Then
ElseIf .Name = "Paid" Then
'Pick user selected value from unbound textbox named, say, NewQtr.
' .Value = Nz(Me!NewQtr.Value, 0)
Else
'Copy field.
.Value = rst.Fields(.Name).Value
End If
End With
Next
.Update
End With
'Store location of new record.
Bookmark = rstAdd.Bookmark
rst.Close
rstAdd.Close
'Move to the new recordcopy.
Me.Bookmark = Bookmark
Set fld = Nothing
Set rstAdd = Nothing
Set rst = Nothing
DoCmd.RunCommand acCmdRecordsGoToLast
Me.TxInvNm.Value = Nz(DMax("OrdNm", "[Tbl_Stock_Ordrs]")) + 1
But I had to disable this line: .Value = Nz(Me!NewQtr.Value, 0)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial