How to run a macro from a form for the current record only

[AccessNovice2005 via AccessMonster.com]

Need Help.

How do I tie a macro executed from a button on a form to the current record I
am looking at in that form?

I have a form that shows all the fields for a singe record from a sales table.
There are 3 comment fields which show the last 3 comments you had with a
client so you can keep a history. These fields are called "Comment1",
"Comment2" and "Comment3". (1 is the most recent comment, 3 is the oldest).
So while I am viewing the record for this particular client, I want to update
my comments, preserving the 2 most recent updates. So, I need to move move
Comment2 to Comment3, Comment1 to Comment2 and then clear Comment1. I have a
button which executes a macro running a simple update query, but it updates
the ENTIRE TABLE, not just this record. I do not know VB so I use macros for
everything. I do know SQL.

Any help appreciated.

 

[[MVP] S.Clark]

Assuming that the record has a unique key value, i.e. an ID, you would pass
that to the query for processing.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Wolfgang Kais]

Hello "AccessNovice2005"

How do I tie a macro executed from a button on a form to the current
record I am looking at in that form?

While the record is shown, you can access the fields of the record
by means of the controls on the form.

I have a form that shows all the fields for a singe record from a
sales table. There are 3 comment fields which show the last 3
comments you had with a client so you can keep a history. These
fields are called "Comment1", "Comment2" and "Comment3".
(1 is the most recent comment, 3 s the oldest).
So while I am viewing the record for this particular client, I want to
update my comments, preserving the 2 most recent updates. So, I
need to move move Comment2 to Comment3, Comment1 to Comment2
and then clear Comment1. I have a button which executes a macro
running a simple update query, but it updates the ENTIRE TABLE, not
just this record. I do not know VB so I use macros for everything.

Try a macro with 3 SetValue actions:

Element: Forms!YourForm!Comment3
Expression: Forms!YourForm!Comment2

Element: Forms!YourForm!Comment2
Expression: Forms!YourForm!Comment1

Element: Forms!YourForm!Comment1
Exression: Null

I do know SQL.

Then use a where clause that restricts the update query to the current
record in the form (somewhat Where ID = Forms!YourForm!ID).