DoCmd.DoMenuItem copy and paste

[phill86]

Hi I have a main form that holds records for scheduled meetings, date time location etc... in that form i have a sub form that has a list of equipment resources that you can assign to the meeting in the main form. I have two buttons in the sub form one for selecting and copying all the records and another for pasting the records this enables me to copy and paste the equipment resources from one scheduled meeting to another.

The buttons are coded as follows

Select and copy:-

DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, acCopy, , acMenuVer70

Paste:

    DoCmd.GoToRecord , , acNewRec

DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, acPaste, , acMenuVer70

This all works fine if i follow these steps.
1: open form
2:locate meeting 1
3:copy equipment from meeting 1
4: locate meeting 2
5:paste equipment from meeting1 to meeting 2


it will not work if i edit meeting 2 first ie. if i take the following steps
1:open form
2:locate meeting 2
3:delete equipment record from list
4:locate meeting 1
5:copy equipment from meeting1
6:locate meeting 2
7:paste equipment from meeting1 to meeting 2


when the records have been pasted using the first example the MS Access message box "you are about to paste 2 records ok cancel" appears

when the second example is used the MS Access message box "you are about to paste 2 records ok cancel" does not appear
and if you look at the table behind the forms it has not updated even though it looks like the new records have been added in the form.

if i do not use the buttons and do the tasks "manualy" either using the copy and paste icons on the toolbar or use the edit copy and paste menu

or if i use short cut keys ctrl c and ctrl v

it works fine which is why i think that it may be related to the butons.

This is the sql statement in the subform

SELECT T_EquipBookTemp.EquipBookTmpID, T_EquipBookTemp.EquipID, T_EquipBookTemp.StartTimeID, T_EquipBookTemp.EndTimeID, T_EquipBookTemp.SessionID, T_EquipBookTemp.EquipBooked, T_ActiveSession.StartDate, T_ActiveSession.EndDate, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_EquipBookTemp.box09, T_EquipBookTemp.EquipBooked

i also have cascade update and cascade delete applied in the relationship between the two tables

Please help

Regards Phill

[nico5038]

I must warn you for using the "DoCmd.DoMenuItem" command.
When the user does click on the form of another application the execution will fail... :-(

For the general "move" problem I created in the past a sample database to show how to move items in a list and in a subform. Both "Up/Down" to create a user defined sequence and "Left/Right" for filling a selection like Access does in several wizards with [<] and [>} buttons.

Just checkout the attached sample.

Nic;o)

[phill86]

hi nico,

thanks for the reply i dont understand why it works fine if i do not edit the record first or if i edit the record then close and reopen the form and then paste it also works fine it seems to be that it is not updating the underlying table for some reason is there any way to reload the form before i paste the records?

Cheers Phill

[nico5038]

Hi Phill,

The data on the form will be in a "pre update" status. When moving to another record the BeforeUpdate event will be triggered and then the data will be saved in the database.
You can "force" this save of the record by issuing a save command like:
DoCmd.RunCommand acCmdSaveRecord

As you see this is no "DoMenuItem" command and all "DoMenuItem" commands can best be "translated" into "RunCommand" statements.

Nic;o)

[phill86]

Hi nico,

thanx for the reply that still did not work i have changed all the code on the buttons to runcommands and for some reason the records do not get pasted in the table if the records have been edited previously the second time you try and paste the records you do not get the ms access mesaage box that says "you are about to paste 2 records ok cancel" so this points to something that is happening in the table or query if you paste it manually ie ctrl v or via the edit menu it works please help

thanks phill

Hi Phill,

The data on the form will be in a "pre update" status. When moving to another record the BeforeUpdate event will be triggered and then the data will be saved in the database.
You can "force" this save of the record by issuing a save command like:
DoCmd.RunCommand acCmdSaveRecord

As you see this is no "DoMenuItem" command and all "DoMenuItem" commands can best be "translated" into "RunCommand" statements.

Nic;o)

[nico5038]

I think your delete combined with the cascading links is causing this "trouble"

So I guess we need a new approach to handle the move.Obviously you want to move single and multiple records from one "main" to another "main" table.
This can be achieved in multiple ways. For my left/right move I use a Yes/No field for the selection and that's also an option for you.
The other approach would be to handle the matter from a multi select listbox, although the coding will be harder in this manner.

To start it might be a good thing you attach a sample .mdb to your next comment (write your reply and after saving follow the "edit" link at the bottom to manage an attachment).

Nic;o)