Macros in Access 2003

[James Silverton]

I have tried Googling but I don't seem to choose the right search terms, so
here goes before I seriously consider updating Office. Is it possible to
*record* a macro in Access 2003? I have always found the inconsistent way
Access deals with macros to be a difficulty since I don't use it enough to
remember the technique.

TIA

 

[Steve Schapel]

James,

If you mean "record a macro" in the same sense as you can in Excel,
for example, then the answer is No, not in Access 2003 or any other
version of Access for that matter. The concept of Macro in Access is
totallly different in Access than it is in other programmes. In
Access, a Macro is a separate type of database object, and is
constructed in a designer window, by assigning a series of "Actions"
selected from a pre-defined list of available actions, and then
applying where applicable, some Conditions or defining Arguments to
these actions. From the Database Window, select the Macros tab, and
then New...

- Steve Schapel, Microsoft Access MVP

 

[James Silverton]

James,

If you mean "record a macro" in the same sense as you can in Excel,
for example, then the answer is No, not in Access 2003 or any other
version of Access for that matter. The concept of Macro in Access is
totallly different in Access than it is in other programmes. In
Access, a Macro is a separate type of database object, and is
constructed in a designer window, by assigning a series of "Actions"
selected from a pre-defined list of available actions, and then
applying where applicable, some Conditions or defining Arguments to
these actions. From the Database Window, select the Macros tab, and
then New...

- Steve Schapel, Microsoft Access MVP

Thanks very much! It does square with what I had expected; pity (g). What I
recently had wanted to do was to change two-valued data in a particular
column with one click which I think I could do in Excel.

 

[Van T. Dinh]

Not enough info for me to give more precise answer but you can use an UPDATE
Query and if you want to run the Update Query from a CommandButton, you can
create a Macro to run the Query.

 

[James Silverton]

Not enough info for me to give more precise answer but you can use an UPDATE
Query and if you want to run the Update Query from a CommandButton, you can
create a Macro to run the Query.

Thanks for the reply but what I'd like to do is pretty simple, I think. The
data base contains a column (item or whatever) which is either YES or NO. I
would like to be able to toggle the value by selecting the cell and then
clicking a button.

Yes, it is a Xmas card list!

Jim.

 

[Greg Kraushaar]

Set the fields cotrol to either a check box, radio button or toggle
button This will do what you want in one swoop

 

[Steve Schapel]

Agreed. If for some reason you really need to toggle the value from a
command button, you could do this with a SetValue macro. This button
would be on a Form. Macro something like:
Condition: [MyField]=0
Action: SetValue
Item: [MyField]
Expression: -1
Condition: [MyField]=-1
Action: SetValue
Item: [MyField]
Expression: 0

- Steve Schapel, Microsoft Access MVP

 

[James Silverton]

Agreed. If for some reason you really need to toggle the value from a
command button, you could do this with a SetValue macro. This button
would be on a Form. Macro something like:
Condition: [MyField]=0
Action: SetValue
Item: [MyField]
Expression: -1
Condition: [MyField]=-1
Action: SetValue
Item: [MyField]
Expression: 0

- Steve Schapel, Microsoft Access MVP

Set the fields cotrol to either a check box, radio button or toggle
button This will do what you want in one swoop

Thank you Greg and Steve!

I am obviously going to have do some updating of what little I know of
Access. My database was started with Access 1993 and has been modified over
the years to work with newer versions of Access: now 2002. For the necessary
annual small changes, it seems just as convenient to make them in Table view
and there I still can't figure out how to quickly toggle a yes/no value
without going to a form.

Do you have any recommendations for a suitable beginner's book? My original
(1993) version had a very good "Getting Started" manual but that does seem
much use now.

 

[Rolls]

What I would do is to add "Sent" field with datatype="Yes/No". Then on your
table or form just click it to toggle (stores values 0 or -1). You don't
need a macro or an update query except maybe to set all the records to "No"
before you start each year.

 

[James Silverton]

What I would do is to add "Sent" field with datatype="Yes/No". Then on your
table or form just click it to toggle (stores values 0 or -1). You don't
need a macro or an update query except maybe to set all the records to "No"
before you start each year.

Thanks! I did that but, after setting the column of Yes/No values to No in
the Data Table view, all that happened when I clicked a field is that the No
changed to 0 (zero).

Jim.

 

[Rolls]

after setting the column of Yes/No values to No in

the Data Table view, all that happened when I clicked a > field is that

the No changed to 0 (zero).

Look for two tabs below the field list:

In your table design view the General Format s/b "Yes/No" and the Lookup
Display Control s/b "Checkbox". You did the first step, now you need to
complete the second step.

 

[Steve Schapel]

Jim,

In the table design, put your cursor in the Yes/No field, then select
the Lookup tab at the bottom, and change the Display Control setting
to Check Box.

Having said that, it is generally regarded that tables are for data
storage, and forms are for data entry/edit. If the data structure is
so simple that you can readily manage it directly in the table(s),
perhaps Access is "overkill" for the purpose.

- Steve Schapel, Microsoft Access MVP

 

[Rolls]

Two steps:

1) Field type = Yes/No; allows 0, -1 as valid values.
2) Lookup Display Control = Checkbox.

In table design vies click on the Yes/No field, then at the bottom below the
field list there are two tabs "General" and "Lookup". Click the Lookup tab
then Check Box

Then switch out of design view bask to the table contents. Instead of 0 you
should see a checked box, -1 = unchecked box.

I think that's what you wanted.

If you create forms you may also have to select the right property for this
field.

 

[James Silverton]

Two steps:

1) Field type = Yes/No; allows 0, -1 as valid values.
2) Lookup Display Control = Checkbox.

In table design vies click on the Yes/No field, then at the bottom below the
field list there are two tabs "General" and "Lookup". Click the Lookup tab
then Check Box

Then switch out of design view bask to the table contents. Instead of 0 you
should see a checked box, -1 = unchecked box.

I think that's what you wanted.

If you create forms you may also have to select the right property for this
field.

My thanks to everyone for considerable help on what may be a rather trivial
problem! To tell the truth, the data base started out as an exercise a long
time ago to learn about an earlier version of Access but I have found it
useful over the years without using Access very much for other things. The
check box method really does the job even if it was not what I set out to
do; simply toggle yes and no. My data base is used not only to detect
whether a Greetings card was received but also to produce listings (by
Queries) for addresses in foreign countries, for local and for long distance
cards since it is appropriate to send these at different dates.

My thanks again and, appropriately I hope, Seasons Greetings!