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!

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.

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:
> 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!

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" wrote:

> 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.
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
> Access Newbie Nick wrote:
> > 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!
>

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. :-)

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:
> 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 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.

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.

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:
> 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.

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 :D

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

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

Access Newbie Nick wrote:
> 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 :D
> 
> Thanks for your time and help and sorry to keep bothering you.

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.

"Steve Schapel" wrote:

> 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
> 
> Access Newbie Nick wrote:
> > 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 :D
> > 
> > Thanks for your time and help and sorry to keep bothering you.
>

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?

-- 
Steve Schapel, Microsoft Access MVP


Access Newbie Nick wrote:
> 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.

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

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

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.

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:
> oh yes and how can i find out about the numerical values for different colours?

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" wrote:

> 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.
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
> Access Newbie Nick wrote:
> > oh yes and how can i find out about the numerical values for different colours?
>

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.

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:
> 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.....

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 :D

the current one is base 16 i suppose?

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, Microsoft Access MVP

Access Newbie Nick wrote:
> the current one is base 16 i suppose?

Nick,

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

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:

> 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 :D

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 :D

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

Access Newbie Nick wrote:
> 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 :D

Always keen to find an excuse not to get on with my own work, Nick! <grin>

-- 
Steve Schapel, Microsoft Access MVP

If thats the case then please take a look at my newest post! haha

Ok, Nick.  Where is it?

-- 
Steve Schapel, Microsoft Access MVP

Access Newbie Nick wrote:
> If thats the case then please take a look at my newest post! haha

I think i have had it solved now, but here u go 

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.formscoding&mid=a6609bc6-4c9a-48e0-9639-dee40c906ceb

hope u can use this