By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

How to copy rows

100+
P: 134
Once I have search the row that I want, How do I copy the entire row content and insert into a new row with the same content using vba .Can the "GetRows" work, how does it work ?, Is there a better way beside define the columns one by one, as my row have more than 20 columns.
Dec 21 '06 #1
Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
Here's the code that Access generates if you create a button using the Command Button Wizard and assign the Duplicate Record function to it.
Expand|Select|Wrap|Line Numbers
  1.    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  2.    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
  3.    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 
  4.  
On a Single Form or Continuous Form you can place it behind a command button. On a Datasheet form you need to place it on something like the DoubleClick event of a text control.

If any of the components of your row is Primary Key or set to no duplicates in the table definitions, after Access inserts the copy you must change the data in these controls before saving the newly created record, otherwise Access will throw an error and the new record won't be saved.
Dec 21 '06 #2

100+
P: 134
Here's the code that Access generates if you create a button using the Command Button Wizard and assign the Duplicate Record function to it.
Expand|Select|Wrap|Line Numbers
  1.    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  2.    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
  3.    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 
  4.  
On a Single Form or Continuous Form you can place it behind a command button. On a Datasheet form you need to place it on something like the DoubleClick event of a text control.

If any of the components of your row is Primary Key or set to no duplicates in the table definitions, after Access inserts the copy you must change the data in these controls before saving the newly created record, otherwise Access will throw an error and the new record won't be saved.
After I clicked the command button, It does create a new row, but an empty row
Dec 21 '06 #3

missinglinq
Expert 2.5K+
P: 3,532
Of course it does! You mean it creates the duplicate row and a blank row, right? The last row of any continuous form or datasheet form is always blank, ready for a new record to be added! That's the same thing it would exhibit if you'd clicked on the New Record button and manually entered a record!
Dec 21 '06 #4

100+
P: 134
Of course it does! You mean it creates the duplicate row and a blank row, right? The last row of any continuous form or datasheet form is always blank, ready for a new record to be added! That's the same thing it would exhibit if you'd clicked on the New Record button and manually entered a record!
I am not sure I got your message right, It does create a blank row, but did not duplicate the record in the table.I couldn't find a duplicate record in the table.

I understand the last row is alway blank ready for a new record.
Dec 21 '06 #5

missinglinq
Expert 2.5K+
P: 3,532
I think the problem is that you're using a continuous form. I was mistaken in saying you could put the code behind a command button on a continuous form, you can only do this on a Single View form. When you click on the button on a continuous form, the focus is now on the button and there is no selected record, so Access doesn't know what to copy. You'll need to place the code behind the DoubleClick event for one of your controls (which is how I tested it.) I'd use whichever control is at the beginning of your row.
Dec 22 '06 #6

100+
P: 134
I think the problem is that you're using a continuous form. I was mistaken in saying you could put the code behind a command button on a continuous form, you can only do this on a Single View form. When you click on the button on a continuous form, the focus is now on the button and there is no selected record, so Access doesn't know what to copy. You'll need to place the code behind the DoubleClick event for one of your controls (which is how I tested it.) I'd use whichever control is at the beginning of your row.
It Alright, I have to use Insert into table to solve, but that serious.
Dec 23 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.