Using the SetProperty Macro action in a subform

[Access Newbie Nick]

When setting the font colour of a control in a subform Upon Change, i get an
error message about an incorrect control name. The name is correct, the macro
is embedded, and when i add SelectObject for the subform it says it is not
open, probably bc it is within another form.
Is there any way to get around this without using Code or Opening the form
seperately?
Any help is greatly appreciated!

 

[Steve Schapel]

Nick,

I think you will need to give more specific details before anyone can
give a meaningful answer here.

What is the name of the control? What is the name of the form? What is
the name of the subform? What Event is the macro running on (there is
no such thing as Upon Change). What are the exact details of the macro,
i.e. Actiosn and Arguments. What is the exact wording of the error
message? Etc.

 

[Access Newbie Nick]

Temp PO Item Query Subform is nested in 'Temp PO Main Query' form. Perhaps i
need to select the subform as a control of the main form first?

Form: Temp PO Item Query Subform
Control Name: Cost
Event: On Change (run macro)
Action: SetValue
Arguments: Cost, Fore Colour, Black

Error Header: The Control name "Cost" is misspelled or refers to a control
that does not exist.

With select object...

Form: Temp PO Item Query Subform
Control Name: Cost
Event: On Change (run macro)
Action1: Select Object
Arguments1: Temp PO Item Query Subform
Action2: SetValue
Arguments2: Cost, Fore Colour, Black

Error Header: Object "Temp PO Item Query Subform is not open" (or something
to that effect)

Thanks for the pointers steve

 

[Steve Schapel]

Nick,

Probably the On Change event is not really appropriate here. Sorry to
pepper you with questions, but... the Change event of what? Of a
control? If so, is this control on the main form of the subform?

Without knowing quite what you are trying to achieve, I would guess that
you are running the macro on a control on the main form, and I expect
the After Update event is possibly more applicable.

And the Cost control is on the subform, right?

Ok, if my assumptions are correct, try it like this:

Action: SetValue
Item: [Temp PO Item Query Subform].[Form]![Cost].[ForeColor]
Expression: Black

Having said that, I should also comment that you are probably doing this
the hard way. You can probably use Conditional Formatting to achieve
what you want here, with less effort.

 

[Access Newbie Nick]

Thanks for the input again. The on Change i think is appropriate for me as i
want the colour to change as soon as the user changes the information in a
field, i think i also tried the macro in after update also but to the same
affect. Basically it is a copy of a previous record, many things may be the
same but some may be different, so i want the text to be red if unchanged, so
the user is aware that it may need to be changed. The macro runs at the
correct time, and i have no problems running a similar macro in the main
form. The problem only arises from it being a subform and therefore access
doesnt seem to acknoledge that the subform is infact open. The On Change
Event that begins the macro is on the control that is being changed itself.
Conditional formatting couldnt be used in this case i think: There is no
expression or value that would make it dependant on the information in the
control being changed.
In Short the instigaion of the macro is not such a problem, but the running
of the macro in a subform throws up problems of finding the control.

 

[Steve Schapel]

Nick,

You have misunderstood the meaning of the On Change event. Now that you
have explained a bit more, I strongly recommend you review this practice.

I understand the situation with the form and subform. That's why I made
my earlier suggestion about the syntax to use for the Item argument of
your SetValue action. Did you try it? How did it go?

You are incorrect in your statement about Conditional Formatting.

 

[Access Newbie Nick]

Ahhh ok then. So After Update would be better it seems. I tried the
suggestion you gave me, however the 'Black' is automatically changed to
'[Black]', and the message appears as follows-

------
The object doesn't contain the Automaton object 'Black'

You tried to ru a Visual Basic procedure to set a property or method for an
object. However, the component doesn't make the property or method available
for Automation operations.

Check the component's documentation for information on the properties and
methods it makes available for Automation operations.
------

Also if i could use conditional formatting that would be a lot easier
definately, so if you have a suggestion for an expression i can use then by
all means inform me

Thanks for your time and help and sorry to keep bothering you.

 

[Steve Schapel]

Nick,

You are not bothering me.

But equally, you are not helping me! You're not relating to the idea
that I'm not looking over your shoulder at your database. I need more
information. I asked you what control you are evaluating, and where it
is, but you didn't answer that. I am guessing you have a control on the
main form, and you want its value to toggle the colour of the Cost
control on the subform. But you haven't confirmed this suspicion. And
now you are asking me to suggest a Conditional Formatting expression
without me knowing anything about what control it is or what your
conditions are or what kind of data it has or anything.

As for the 'Black', I'm sorry I was wrong about that. I think it will
work to use the numerical value for black, which is 0.

 

[Access Newbie Nick]

Ok well.....

I vaguely explained it before, be it not very well. Taking the 'cost' as an
example, A previous order might have cost £30.00. So £30 is there as a guide,
and possibly it is still the same as that. Most likely it has changed though.
So the user who inputs the new information can see the text in red as a
default. Being red they can see that they may still need to change it, and
once they put a new value in (say £20.00) the text will then be shown in
black.

So changing the value changes the colour in that field.
The same thing has to apply for fields without a numerical value.

The cost control is only in the subform, the colour is not controlled by
anything external of the form.

Regarding the other thing, i dont get a errror message about black as it is
now 0, but i get the same error message but with 'Temp PO Item Query subform'
instead of 'Black'

Sorry that i am not so good at explaining things.

Nick,

You are not bothering me.

But equally, you are not helping me! You're not relating to the idea
that I'm not looking over your shoulder at your database. I need more
information. I asked you what control you are evaluating, and where it
is, but you didn't answer that. I am guessing you have a control on the
main form, and you want its value to toggle the colour of the Cost
control on the subform. But you haven't confirmed this suspicion. And
now you are asking me to suggest a Conditional Formatting expression
without me knowing anything about what control it is or what your
conditions are or what kind of data it has or anything.

As for the 'Black', I'm sorry I was wrong about that. I think it will
work to use the numerical value for black, which is 0.

--
Steve Schapel, Microsoft Access MVP

Ahhh ok then. So After Update would be better it seems. I tried the
suggestion you gave me, however the 'Black' is automatically changed to
'[Black]', and the message appears as follows-

------
The object doesn't contain the Automaton object 'Black'

You tried to ru a Visual Basic procedure to set a property or method for an
object. However, the component doesn't make the property or method available
for Automation operations.

Check the component's documentation for information on the properties and
methods it makes available for Automation operations.
------

Also if i could use conditional formatting that would be a lot easier
definately, so if you have a suggestion for an expression i can use then by
all means inform me

Thanks for your time and help and sorry to keep bothering you.

 

[Steve Schapel]

Nick,

Aha! Now I'm starting to get it! So you want a control's colour to
change to black based on *its own* value being changed? Well, my
apologies, but I would never have guessed that from the earlier discussion.

So, if I now understand correctly, on the After Update event of the Cost
textbox, your macro will be like this:

Action: SetValue
Item: [Cost].[ForeColor]
Expression: 0

Let us know how that goes.

Prtobably jumping ahead here, but does this mean you have to also change
them all back to red again ready for the next entry?

 

[Access Newbie Nick]

Yes! Worked perfectly thanks. I suppose i can use a macro to set it back to
red when i move to a new record or something can i? Or there may be a better
way.
Cheers mate

 

[Access Newbie Nick]

oh yes and how can i find out about the numerical values for different colours?

 

[Steve Schapel]

Nick,

I'm sure there must be a chart or listing somewhere, but... the way I do
it is to set the colour of a control in design view using the colour
picker on the toolbar or the property sheet, and then have a look at the
number that has been put in to that property of the control.

 

[Access Newbie Nick]

Yeh I tried to do that, but as the number also contained letters then it
showed it like this [BA1419]. I just settled with using 1419 but it is a
slightly different colour, perhaps the letters determine how dark it is. Its
not a big problem so i dont really need it sorted, but i havent found a
listing yet.....

 

[Steve Schapel]

Nick,

Ah! Sorry, I forgot you are using Access 2007. Access 2007 uses a
different system than previous versions, and I haven't adapted yet! ;-)
I'm pretty sure black will show in Access 2007 as #000000. I don't
have Access 2007 on this computer, but I'll have a look at it later.

 

[Access Newbie Nick]

ahh i guessed it may be something like that. I think the old one uses
hexidecimal base 10 or something. The thing is 0 for black works in the
macro, so i think for the macro the previous numbers seem to work, and the
current type doesnt. Seems an oversight in making 2007, but there could be
another reason.

On a unrelated matter, ass you seem a knowledgable gentleman i thought i
would ask you if u know how to use the record number in a form, or subform,
in an expression. I thought it was something like Forms_Current() but that
didnt really work. I'm currently searching about it but if you could help
that would be fantastic

 

[Access Newbie Nick]

the current one is base 16 i suppose?

 

[Steve Schapel]

Nick,

Yes, you are correct, it is a hexadecimal number, which is what has
become the standard for web site colours etc, and I suppose it is cool
that Access is using this now, *except*... I can't find an easy way to
use them in a macro. As you quite correctly point out, the SetValue and
SetProperty actions seem to still need the decimal values.

So, unless you are using colours whose value you know (like 0 for black
and 255 for red), at the moment it looks like you have to convert it,
for which you can use a converter tool such as
http://www.translatum.gr/converter/hexadecimal.htm and then use the RGB
function to get the decimal color value.

So for example, if you select a colour in an Access 2007 control that
shows you #3B8194, put that into the converter and it gives you red=59,
green=129, blue=148. So then in the Vlaue argument for the SetProperty
or SetValue action, you can put like this:
=RGB(59,129,148)

Best I can come up with so far!

 

[Steve Schapel]

Nick,

Depends a bit what you want to do and how you are using it. But anyway,
[CurrentRecord] is the ticket.

 

[Access Newbie Nick]

Golden information thanks! I'm starting to wonder why you are helping me so
much? maybe if you have a lot of time on your hands or u just like to help
people

Did u have any ideas about using the record number of a form in an
expression? I have had no luck so far....