cut and paste script

[kman100]

I have two tables in a database. I have forms set up for both of them.Every now and then I need to cut a record from one table and paste it into another table. Can I create a button on that uses a script to do this for me - right now I am doing it manually.

Hope someone can help
Kman

 

[Andrew Fergus]

Hi, if you were to provide some more details then someone here will be able to help. In particular, what are the table names and the variables in the 2 tables?

Andrew.

 

[kman100]

My tables are called new clients & repeat clients. I use a form for both that is in exactly the same format - the fields all match. When I cut and paste, I select the whole record on new client, cut it and paste it into repeat clients. I was hoping to put a button on my new client form that would do this for me. What other info would you need?

 

[Andrew Fergus]

Hi, thanks for the info - I wanted to know if the tables were identical. I got this to work by doing the following :

Create a new query -> Design View -> Show Table -> New Client Table -> Ok. Click Query -> Append Query -> Repeat Client Table -> Ok. Add the fields from the new client table to the query and the query should automatically assign the same variable names from the Repeat Client Table into the "Append To" line. In the criteria section of the field which is your key (I used [field1]), enter [Forms]![your form name that you want to copy from - new clients].[field1] . Save.

Create a new macro :
1st line : Echo (Echo On = No, leave 2nd item blank) ;
2nd line : SetWarnings (Warnings On = No)
3rd line : OpenQuery (name = your query name from above, view = datasheet, data mode = edit)
4th line : SetWarnings (Warnings On = Yes)
5th line : Echo (Echo On = Yes, leave 2nd item blank) ;
6th : StopMacro
Save.

On your New Client form, go to design view, in the detail section, create a new command button from the toolbox -> Miscellaneous -> Run Macro -> Macro -> Next -> Select your new macro -> Next -> Text (use a meaningful button label such as "Append this Record") -> Next -> Next -> Finish. Save.

When you are viewing the clients in the new client form, the new button should appear beside each client (depending on where you placed it in the detail section), if you click the button it will copy that record from the form into the other table.

HTH, Andrew.

 

[kman100]

thanks Andrew,

I'll give it a go!

Kman

 

[Andrew Fergus]

To avoid confusion, this bit of my suggestion :

[Forms]![your form name that you want to copy from - new clients].[field1]

might read easier if I showed it like this :

[Forms]![your form name].[your key field name]

Let me know how you get on.

Andrew.

 

[kman100]

Andrew,

I gave it a go and it sort of worked. Firstly when I clicked the new button, I got a popup box asking me to enter parameters. I then decided to take out the criteria info from my query - this is what the popup was referring to. That worked, but it ended up copying my whole new client list to the repeat list, not just the record I was in. Also I realled wanted to cut & Paste, not copy and paste. OnceI transfer the record to the repeat List, I don't want it in the new list any longer.

Thanks
Kman

 

[Andrew Fergus]

Hi Kman,

What is the form name that you want to cut from? Also, what is the key / unique variable? Is it something like customer id? Also, what was the criteria you used in the query? If it pops up asking for something then you have used either the wrong form name, the wrong field name or there is something wrong with the syntax. I got this to work without any pop up screens.

Lastly, have you thought about using a field your new client table called "Repeat Client" (Yes/No) and working everything from there? This way you won't need to transfer the client across to a second table.

Andrew.