Macro to act on current record

[Guest]

Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro to act
on the current record without affecting other records which are also present

Any help would be gratefully received. I've not not used Macro's a great deal

Thanks
Paul

 

[tina]

by "tabular" i assume you mean a Continuous form. you're seeing multiple
records, but there is only one of each control in the form. so setting a
property for a control will affect how that single control is displayed in
all the records. this is not a macro issue; the same would hold true if you
were setting the property with VBA code.

in a form, you can only manually interact with one record at a time (the
Current record), no matter how many records are displayed. you can run the
same macro actions from the form's Current event, as well as the control's
Exit event. then, at least, the control properties will be set appropriately
for the record that the user is currently interacting with.

hth

 

[Steve Schapel]

Paul,

Just to add to Tina's excellent advice... Some of the functionality you
want may be available via Conditional Formatting. You can't toggle the
Visible property of a control using Conditional Formatting, but you can
sort of simulate it by making the Fore Color the same as the Back Color,
so in effect you don't see it

In the example you gave, you would select the familyroom control in
design view of the form, select Conditional Formatting from the Format
menu, and use the 'Expression Is' option to enter your conditions such
as [roomid]=1 and make the formatting changes as applicable.

 

[Guest]

Steve

Thanks for the tip - that was certainly something I hadn't thought of.
Unfortunately because its a Combo box I can't find any way of eliminating the
drop down arrow from the Conditional formatting

Paul,

Just to add to Tina's excellent advice... Some of the functionality you
want may be available via Conditional Formatting. You can't toggle the
Visible property of a control using Conditional Formatting, but you can
sort of simulate it by making the Fore Color the same as the Back Color,
so in effect you don't see it

In the example you gave, you would select the familyroom control in
design view of the form, select Conditional Formatting from the Format
menu, and use the 'Expression Is' option to enter your conditions such
as [roomid]=1 and make the formatting changes as applicable.

--
Steve Schapel, Microsoft Access MVP

Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro to act
on the current record without affecting other records which are also present

Any help would be gratefully received. I've not not used Macro's a great deal

Thanks
Paul

 

[Guest]

Hi Tina

Thanks for your comments - let me explain a bit further. I basically have a
form with a subform and it is the subform that is the continuous form (so a
Customer can have many bookings). So on the bookings subform the contol I
want the macro to run on appears many times - the problem I have is that when
the macro runs on the current subform "booking" record it affects all the
other bookings that are visible for that customer which I don't want

by "tabular" i assume you mean a Continuous form. you're seeing multiple
records, but there is only one of each control in the form. so setting a
property for a control will affect how that single control is displayed in
all the records. this is not a macro issue; the same would hold true if you
were setting the property with VBA code.

in a form, you can only manually interact with one record at a time (the
Current record), no matter how many records are displayed. you can run the
same macro actions from the form's Current event, as well as the control's
Exit event. then, at least, the control properties will be set appropriately
for the record that the user is currently interacting with.

hth

Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro to act
on the current record without affecting other records which are also present

Any help would be gratefully received. I've not not used Macro's a great deal

Thanks
Paul

 

[tina]

yes, i understood the situation, Paul. the fact that it's a Continuous view
on a subform makes no difference. as i said before, "you're seeing multiple
records, but there is only one of each control in the form. so setting a
property for a control will affect how that single control is displayed in
all the records."

Hi Tina

Thanks for your comments - let me explain a bit further. I basically have a
form with a subform and it is the subform that is the continuous form (so a
Customer can have many bookings). So on the bookings subform the contol I
want the macro to run on appears many times - the problem I have is that when
the macro runs on the current subform "booking" record it affects all the
other bookings that are visible for that customer which I don't want

by "tabular" i assume you mean a Continuous form. you're seeing multiple
records, but there is only one of each control in the form. so setting a
property for a control will affect how that single control is displayed in
all the records. this is not a macro issue; the same would hold true if you
were setting the property with VBA code.

in a form, you can only manually interact with one record at a time (the
Current record), no matter how many records are displayed. you can run the
same macro actions from the form's Current event, as well as the control's
Exit event. then, at least, the control properties will be set appropriately
for the record that the user is currently interacting with.

hth

Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro

to
act

on the current record without affecting other records which are also present

Any help would be gratefully received. I've not not used Macro's a

great
deal

Thanks
Paul

 

[tina]

the only other Conditional Formatting option you might try is to Disable the
control according to the conditions. it would still be visible, but at least
the user would be prevented from entering the control.

hth

Steve

Thanks for the tip - that was certainly something I hadn't thought of.
Unfortunately because its a Combo box I can't find any way of eliminating the
drop down arrow from the Conditional formatting

Paul,

Just to add to Tina's excellent advice... Some of the functionality you
want may be available via Conditional Formatting. You can't toggle the
Visible property of a control using Conditional Formatting, but you can
sort of simulate it by making the Fore Color the same as the Back Color,
so in effect you don't see it

In the example you gave, you would select the familyroom control in
design view of the form, select Conditional Formatting from the Format
menu, and use the 'Expression Is' option to enter your conditions such
as [roomid]=1 and make the formatting changes as applicable.

--
Steve Schapel, Microsoft Access MVP

Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro to act
on the current record without affecting other records which are also present

Any help would be gratefully received. I've not not used Macro's a great deal

Thanks
Paul

 

[Guest]

Hi Tina.... I can understand what you are saying - and that's how I'd expect
the thing to work. However, trust me here when I say that when I change the
[roomid] on one record of the subform the visibility of the [familyroom]
checkbox is affected on all the other records at the same time.

yes, i understood the situation, Paul. the fact that it's a Continuous view
on a subform makes no difference. as i said before, "you're seeing multiple
records, but there is only one of each control in the form. so setting a
property for a control will affect how that single control is displayed in
all the records."

Hi Tina

Thanks for your comments - let me explain a bit further. I basically have a
form with a subform and it is the subform that is the continuous form (so a
Customer can have many bookings). So on the bookings subform the contol I
want the macro to run on appears many times - the problem I have is that when
the macro runs on the current subform "booking" record it affects all the
other bookings that are visible for that customer which I don't want

by "tabular" i assume you mean a Continuous form. you're seeing multiple
records, but there is only one of each control in the form. so setting a
property for a control will affect how that single control is displayed in
all the records. this is not a macro issue; the same would hold true if you
were setting the property with VBA code.

in a form, you can only manually interact with one record at a time (the
Current record), no matter how many records are displayed. you can run the
same macro actions from the form's Current event, as well as the control's
Exit event. then, at least, the control properties will be set appropriately
for the record that the user is currently interacting with.

hth


Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an
action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item :
Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro to
act
on the current record without affecting other records which are also
present

Any help would be gratefully received. I've not not used Macro's a great
deal

Thanks
Paul

 

[Guest]

and guess what its doing the same thing still and acting on all the records
in the subform

the only other Conditional Formatting option you might try is to Disable the
control according to the conditions. it would still be visible, but at least
the user would be prevented from entering the control.

hth

Steve

Thanks for the tip - that was certainly something I hadn't thought of.
Unfortunately because its a Combo box I can't find any way of eliminating the
drop down arrow from the Conditional formatting

Paul,

Just to add to Tina's excellent advice... Some of the functionality you
want may be available via Conditional Formatting. You can't toggle the
Visible property of a control using Conditional Formatting, but you can
sort of simulate it by making the Fore Color the same as the Back Color,
so in effect you don't see it

In the example you gave, you would select the familyroom control in
design view of the form, select Conditional Formatting from the Format
menu, and use the 'Expression Is' option to enter your conditions such
as [roomid]=1 and make the formatting changes as applicable.

--
Steve Schapel, Microsoft Access MVP


Paul wrote:
Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions with an action
on each condition that changes the visibility of a different object of the
form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the macro to act
on the current record without affecting other records which are also present

Any help would be gratefully received. I've not not used Macro's a great deal

Thanks
Paul

 

[Steve Schapel]

Pardon me for butting in here, Paul. What you are describing is
*exactly* what Tina is confirming. Not sure where the misunderstanding
is, but it's like "the sky is blue" and then "no it's not, it's blue".
Let's try to clarify... you *can't* programmatically change a formatting
property of a control on a continuous view form, and have it only apply
to one record. End of story, it's not possible, sorry.

 

[tina]

thank you, Steve - i was beginning to think that either i was completely
misunderstanding Paul, or that i was completely failing to communicate
clearly! of course, it's easy for you to understand because you already know
what i'm saying...

Pardon me for butting in here, Paul. What you are describing is
*exactly* what Tina is confirming. Not sure where the misunderstanding
is, but it's like "the sky is blue" and then "no it's not, it's blue".
Let's try to clarify... you *can't* programmatically change a formatting
property of a control on a continuous view form, and have it only apply
to one record. End of story, it's not possible, sorry.

--
Steve Schapel, Microsoft Access MVP

Hi Tina.... I can understand what you are saying - and that's how I'd expect
the thing to work. However, trust me here when I say that when I change the
[roomid] on one record of the subform the visibility of the [familyroom]
checkbox is affected on all the other records at the same time.

 

[tina]

then i'd say give up the quest. (seems i've seen some unpredictable results
with the Disabled setting in Conditional Formatting, but i haven't made a
study of it to see if it's a bug in the software or a problem of my own
making.) as a developer, i only spend so much time trying to make a
particular solution work - especially when it's an issue of "look" rather
than "function" - and then i just find another way to achieve the same end,
or i settle for the best i can get and move on to the next development task.

personally, i would go with my first suggestion to you: take your macro

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

and run it on the RoomID control's AfterUpdate event, and also run it on the
form's Current event. (note, i'd use the AfterUpdate event rather than the
Exit event, because the former only fires when the value in the control is
changed, and the latter will fire even if the user just tabs through the
control without changing the value - and there's no point in running the
macro then.)

hth

and guess what its doing the same thing still and acting on all the records
in the subform

the only other Conditional Formatting option you might try is to Disable the
control according to the conditions. it would still be visible, but at least
the user would be prevented from entering the control.

hth

Steve

Thanks for the tip - that was certainly something I hadn't thought of.
Unfortunately because its a Combo box I can't find any way of

eliminating
the

drop down arrow from the Conditional formatting

:

Paul,

Just to add to Tina's excellent advice... Some of the functionality you
want may be available via Conditional Formatting. You can't toggle the
Visible property of a control using Conditional Formatting, but you can
sort of simulate it by making the Fore Color the same as the Back Color,
so in effect you don't see it

In the example you gave, you would select the familyroom control in
design view of the form, select Conditional Formatting from the Format
menu, and use the 'Expression Is' option to enter your conditions such
as [roomid]=1 and make the formatting changes as applicable.

--
Steve Schapel, Microsoft Access MVP


Paul wrote:
Hi Folks

I have created a macro which is being used on the "On Exit" event of a
object on a tabular form. The macro has a number of conditions

with an
action

on each condition that changes the visibility of a different

object of
the

form.

Condition : Action : Item : Expression
[roomid] = 1 : setvalue : [familyroom]. visible : No
[roomid] = 2 : setvalue : [familyroom]. visible : Yes

However, when I change the value of a record on the tabular form, which
makes the object invisible then it affects all the records on the form
including those which should still be visible. How can I get the

macro
to act

on the current record without affecting other records which are

also
present

Any help would be gratefully received. I've not not used Macro's a great deal

Thanks
Paul

 

[Steve Schapel]

Paul,

Do you mean by going to the Format|Conditional Formatting menu in design
view of the subform, or do you mean by using a macro. The macro
approach will not work, for reasons already outlined by Tina, but the
Conditional Formatting approach I would expect to do what you want.