Access 2010 Copy/Append Record Problem

[NoSmoke]

I have an Access 97 database in which I was able to compose a SendKeys macro to copy the record currently being displayed in a form and append it to create a duplicate record (this being done in Table View). I used the Select Record and Paste Append commands under the Edit tab to do this but cannot come up with an equivalent way of doing it with the same database converted to Access 2010.

I see there is a Paste Append command available by clicking on the down arrow just below the Home/Paste icon and it will append a new record but I can't figure out how to invoke that with only keystrokes. I have also not been able to find the equivalent of a Select Record command so I can't select/copy one record only on the table (ctrl A selects all the records).

Any help would be appreciated with a send keys procedure or, maybe there is a better way of doing it. TIA

 

[NoSmoke]

I have an Access 97 database in which I was able to compose a SendKeys macro to copy the record currently being displayed in a form and append it to create a duplicate record (this being done in Table View). I used the Select Record and Paste Append commands under the Edit tab to do this but cannot come up with an equivalent way of doing it with the same database converted to Access 2010.

I see there is a Paste Append command available by clicking on the down arrow just below the Home/Paste icon and it will append a new record but I can't figure out how to invoke that with only keystrokes. I have also not been able to find the equivalent of a Select Record command so I can't select/copy one record only on the table (ctrl A selects all the records).

Any help would be appreciated with a send keys procedure or, maybe there is a better way of doing it. TIA

OK, I've found a keystroke sequence for copy/append the record and it works properly when performed manually. The key sequence is:

%HWH%HHL%HC%HVN%HWF

where:

%H - got to Home tab (in form view)
W - view
H - datasheet view (cursor at record displayed in form view)
%H - home tab
H - select
L - select (select record)
$H - home tab
C - copy (selected record)
%H - home
V - paste
N - paste append (add selected record to end of table)
%H
W - view
F - return to form view

Trouble is, this doesn't work properly when executed within a macro. The problem arises at the copy command wherein "copy" on the Ribbon is grayed out for some reason (as if there is nothing in the clipboard). The macro statements are:

Submacro: Add Duplicate Record

SendKeys

Keystrokes %HWH%HHL%HC%HVN%HWF

Wait Yes (also tried it with No)

End Submacro

Also, there is an "Unsafe Action" icon beside the SendKeys statement and, the macro was initially created in Access 97 (with a different keystroke sequence). I have also noted Access 2010 macro actions do not include SendKey (for some reason).

Can anyone please help me with this?

 

[Pat Hartman]

SendKeys is dangerous because it posts to the window with the focus which might not actually be the Access window. This can easily be done with a query.

Use QBE to create a query that selects the data you want to copy. Change it to an append query. Add criteria to select the ID of the record you want to copy. If this will always be the record you are viewing then reference that form:

Forms!yourformname!yourcontrolname

Then in the Click event of the button, run the query.

DoCmd.OpenQuery "yourqueryname"

 

[NoSmoke]

SendKeys is dangerous because it posts to the window with the focus which might not actually be the Access window. This can easily be done with a query.

Use QBE to create a query that selects the data you want to copy. Change it to an append query. Add criteria to select the ID of the record you want to copy. If this will always be the record you are viewing then reference that form:

Forms!yourformname!yourcontrolname

Then in the Click event of the button, run the query.

DoCmd.OpenQuery "yourqueryname"

Thanks Pat for the help. I know nothing about QBE (I'm a newbie as you may have guessed) but maybe now's a good time to learn something about it.

With regard to SendKeys though, I've narrowed the problem down to the Copy command (following record Select) which doesn't work for some reason if executed within a macro. If I divide the job into two macros (each invoked by a separate toggle button), one to select the record and one to append- paste the record, with a manual ctrl-C typed in between, the thing works. So, I guess I'll leave it at that until I figure out how to do it with QBE.

 

[Pat Hartman]

If you prefer to stick with macros rather than move up to queries and VBA, then you will probably get better help in the macro forum.

The QBE (query by example) is the design view of the query. Choose create a query from the ribbon and you will be prompted to select one or more tables and queries. Close the selection window after you add the table you need.

 

[scoutime]

I am trying to copy on record ( the current record on a form) and append a copy of that record to the same table the form is based upon.

I thought I could create an Append Query based on that record, but I keep getting an error message. I am not appending the Primary Key. I left that out so that the new record would assign the key automatically.

The error message says I have a Key Violation. It is a simple table with an Auto Number Primary key and about 15 fields. I have the Form Loaded, so that when I view the Query as a Select Query it shows the proper record and information. But when I change it to an Append Query. I get the error. This post below seems to agree with what I was trying to do.

What am I missing?

SendKeys is dangerous because it posts to the window with the focus which might not actually be the Access window. This can easily be done with a query.

Use QBE to create a query that selects the data you want to copy. Change it to an append query. Add criteria to select the ID of the record you want to copy. If this will always be the record you are viewing then reference that form:

Forms!yourformname!yourcontrolname

Then in the Click event of the button, run the query.

DoCmd.OpenQuery "yourqueryname"

 

[JHB]

Show the Append Query.

 

[Pat Hartman]

You may have included the autonumber field in the append query. Remove it. The only time you would have the autonumber column present in an append query is if you really wanted to copy the autonumber values from one table to another because you were doing a conversion. In this case, you want ACE to just generate a new unique autonumber.

 

[scoutime]

Actually I discovered what the problem was, I had a Lookup field in the Apend query. Once I removed that, it worked fine.
Thanks

 

[Pat Hartman]

Yet another problem with lookup fields to add to the list of why you shouldn't use them. Thanks for reporting back.