Copy a record via a macro

[Mrs_B]

Hello there,

Please be gentle as I am new to all this!

I have written a macro in Access 2007 that finds a record in a query (AVG of a number of figures) and I need to be able to somehow COPY this figure and then paste it into a record in a table. I have built all the elements of the macro and am just missing the COPY and PASTE elements.

To a novice such as myself it is causing headaches but to experts like you it is probably very trivial.

Help would appreciated to an old bird like myself.

Thanks in advance,

Mrs B

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Ken Snell \(MVP\)]

Is there a strong reason for storing a calculated value in a table? Normally
(there are exceptions, of course), one would just recalculate the value
using a query whenever it's needed.

A macro will not let you write data directly to a table or through a query.
It's possible to open a form that is based on the table, use a macro's
SetValue action to write a value into a control that is bound to the
appropriate field, and then close the form. But, normally one would not use
a macro and would instead use VBA programming to either run an update or
append query that you create in the code (to add or edit data in the table),
or would open a recordset and write the data to the table that way.

So, give us a bit more information about what you're wanting to accomplish,
and most likely we can suggest the best way to accomplish that.

 

[Matt]

I can't seem to sendtext to a particular column or in my case i am working on a form.

my goal is to programatically set a value on all items in table. I am setting meta data for a sharepoint site, LoL. i dont want to select each one on some 3000 records. id like to solve this so i can use it again latter.

for example. i have a column called catagory and i want to set it to Picture. I will be uploading another set that is all non-Pictures so i want to change those to Non-Pictures. i have everything built on a form but all i need is the ability to set the value of the catagory item. The stupid Macro Actions does not have anything that i can find. The SendKeys action disapeared and i dont know were it went as that worked once but now i dont have it, dont understand why.

i have tried prgramming this but the syntax is different and i cant seem to figure what is going on. documentation is not helping me yet, i just need to get more familar with the interface and dig into it abit but i need a starter. PLease help

sorry to steal your thread but i think we have the same question.

for yours you should be able to store the value in a setTempVar and when someone is kind enough to reply with how to write it out then we can write it out.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Matt Stone]

Ken Snell \(MVP\) Said:
"use a macro's SetValue action to write a value into a control that is bound to the appropriate field, and then close the form. But, normally one would not use a macro and would instead use VBA programming to either run an update or append query that you create in the code (to add or edit data in the table), or would open a recordset and write the data to the table that way."

My question is simply how! I have tried your suggestion but I can not seem to get SetValue or any other macro action to set a value for a drop down box called catagory on my form. My VBA skills are lacking in Access and I can't get started. I was ok with Excel 2003 and some basic Access 2003 stuff but I can't even comprehend where to start with Access 2007. How do you record a macro in Access 2007, I don't see the option. Can I export to Excel and import from excel, just overwriting existing records? I can accomplish everthing I need in excel I just don't understand how this Access 2007 works yet. (I hate this new Access 2007, can't find anything I need) (I will prob like it latter when I get used to it.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Ken Snell \(MVP\)]

ACCESS does not allow "recording" of macros. In fact, a macro in ACCESS is
not VBA code, whereas a macro in EXCEL is VBA code.

To use the SetValue action, the form must be open, and then you must use
this syntax in the Control Name argument of the SetValue action (replace
ActualNameOfForm with the actual name of the form):

Forms!ActualNameOfForm!catagory