Changing BackColor Property of Main Form Label from SubForm

A

Access User

Hi,

My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following thinking I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it have to
do with the fact that Aneurism gets its values automatically from this VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
 
M

M Skabialka

Try changing 'Me' to 'Forms' as you are on a subform and that subform is Me
and the main form is an entirely separate object.
Forms![MRA Form_JPS]!Label113.BackColor
 
A

Access User

Well, I guess we tried but still no cigar...I changed my VBA to conform

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Forms![MRA Form_JPS]!Label113.BackColor = vbRed
Else
Forms![MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but nothing seemingly has been affected.

???
M Skabialka said:
Try changing 'Me' to 'Forms' as you are on a subform and that subform is Me
and the main form is an entirely separate object.
Forms![MRA Form_JPS]!Label113.BackColor

Access User said:
Hi,

My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it have
to
do with the fact that Aneurism gets its values automatically from this VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
 
M

Maurice

try this:

Me.Parent!Label113.BackColor = vbRed

the main form can be refered to as parent...

hth
--
Maurice Ausum


Access User said:
Well, I guess we tried but still no cigar...I changed my VBA to conform

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Forms![MRA Form_JPS]!Label113.BackColor = vbRed
Else
Forms![MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but nothing seemingly has been affected.

???
M Skabialka said:
Try changing 'Me' to 'Forms' as you are on a subform and that subform is Me
and the main form is an entirely separate object.
Forms![MRA Form_JPS]!Label113.BackColor

Access User said:
Hi,

My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it have
to
do with the fact that Aneurism gets its values automatically from this VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
Click to expand...
 
A

Access User

I tried this w/o oberving any changes:


Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.Parent!Label113.BackColor = vbRed
Else
Me.Parent!Label113.BackColor = vbWhite
End If

End Sub

Maurice said:
try this:

Me.Parent!Label113.BackColor = vbRed

the main form can be refered to as parent...

hth
--
Maurice Ausum


Access User said:
Well, I guess we tried but still no cigar...I changed my VBA to conform

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Forms![MRA Form_JPS]!Label113.BackColor = vbRed
Else
Forms![MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but nothing seemingly has been affected.

???
M Skabialka said:
Try changing 'Me' to 'Forms' as you are on a subform and that subform is Me
and the main form is an entirely separate object.
Forms![MRA Form_JPS]!Label113.BackColor

Hi,

My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it have
to
do with the fact that Aneurism gets its values automatically from this VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

If you expect this to change color in the main form as you move record in
the subform, you would need to use the subform's Current event, as well as
the AfterUpdate event of the *form* (not control.) Example below.

Private Sub Form_Current()
Dim lngColor As Long

If Me.Aneurism > 4 Then
lngColor = vbRed
Else
lngColor = vbWhite
End If

With Me.Parent!Label113
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

Private Sub Form_AfterUpdate()
Call Form_Current()
End Sub
 
A

Access User

Yes, thanks lots, this works on the main form, but I wanted to show you the
current state of the VBA in that sub-form because it doesn't work quite as I
would've thought it could. You may be able to suss out the cunundrum by
perusing it (see below)

Option Compare Database
Option Explicit

Private Sub Aneurism_AfterUpdate()

Call Form_Current

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

Private Sub Form_Current()

Dim lngColor As Long

If Me.Aneurism > 4 Then
lngColor = vbRed
Else
lngColor = vbWhite
End If

With Me.Parent!Label113
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With

End Sub

Private Sub Form_AfterUpdate()

Call Form_Current

End Sub


I think you'll probably agree that there's a need for a modification or two
or three.

Thanks in any event - it's an improvement.



Allen Browne said:
If you expect this to change color in the main form as you move record in
the subform, you would need to use the subform's Current event, as well as
the AfterUpdate event of the *form* (not control.) Example below.

Private Sub Form_Current()
Dim lngColor As Long

If Me.Aneurism > 4 Then
lngColor = vbRed
Else
lngColor = vbWhite
End If

With Me.Parent!Label113
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

Private Sub Form_AfterUpdate()
Call Form_Current()
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access User said:
My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it have
to
do with the fact that Aneurism gets its values automatically from this VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
 
A

Access User

I guess I should be less vague about how I think it will work: when the
threshold value of 4 is exceeded, I think the main form label should go red
and remain red until the offending record gets removed.

The other aspect of the behavior in this sub-form that I'm finding is
unexpected is as follows: if the user returns to the sub-form at a later
point and modifies an entry on one of the records, then the value of Aneurism
of that record is incremented, which I don't want happening. Is there a
workaround to that?

Thx!

Allen Browne said:
If you expect this to change color in the main form as you move record in
the subform, you would need to use the subform's Current event, as well as
the AfterUpdate event of the *form* (not control.) Example below.

Private Sub Form_Current()
Dim lngColor As Long

If Me.Aneurism > 4 Then
lngColor = vbRed
Else
lngColor = vbWhite
End If

With Me.Parent!Label113
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

Private Sub Form_AfterUpdate()
Call Form_Current()
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access User said:
My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it have
to
do with the fact that Aneurism gets its values automatically from this VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
 
A

Allen Browne

I guess I'm not clear about how you have this form connected up.

Typically one record in a main form has many records in the subform. It
would be much easier just to use Conditional Formatting on the bad record in
the subform itself, rather than to try to mess with code that changes colors
in the main form. You could do it without any code at all.

I didn't follow as to the Aneurism record self-incrementing. That's not
normal in Access, so there may be come code that's assigning a value to the
field, perhaps in Form_Current or some other event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access User said:
I guess I should be less vague about how I think it will work: when the
threshold value of 4 is exceeded, I think the main form label should go
red
and remain red until the offending record gets removed.

The other aspect of the behavior in this sub-form that I'm finding is
unexpected is as follows: if the user returns to the sub-form at a later
point and modifies an entry on one of the records, then the value of
Aneurism
of that record is incremented, which I don't want happening. Is there a
workaround to that?

Thx!

Allen Browne said:
If you expect this to change color in the main form as you move record in
the subform, you would need to use the subform's Current event, as well
as
the AfterUpdate event of the *form* (not control.) Example below.

Private Sub Form_Current()
Dim lngColor As Long

If Me.Aneurism > 4 Then
lngColor = vbRed
Else
lngColor = vbWhite
End If

With Me.Parent!Label113
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

Private Sub Form_AfterUpdate()
Call Form_Current()
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access User said:
My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following
thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it
have
to
do with the fact that Aneurism gets its values automatically from this
VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
Click to expand...
 
A

Access User

You are quite ritght....there is a one to many child/parent form/sub-form
relationship established on these two. I have used the enabled=no, locked=yes
properties on the Aneurism subform control AND also done a CF on it when it
reached >4 BUT (and here's the rub), it turns out there's some kind of 'bug'
in Access that releases the enabled/locked features when a CF is reached, so
even though the Aneurism ctrl turns beet red when it's > 4, user can enter it
with his cursor. Soooo... I thought I would remove the CF in order to protect
the Aneurism ctrl via the enable/protect properties and place a label on the
main form with suitable verbiage advising against advancing beyond the
threshold amount and turning it red when'er it's transcended and until it's
deleted.

I will look into your hypothesized cause and get back.

Allen Browne said:
I guess I'm not clear about how you have this form connected up.

Typically one record in a main form has many records in the subform. It
would be much easier just to use Conditional Formatting on the bad record in
the subform itself, rather than to try to mess with code that changes colors
in the main form. You could do it without any code at all.

I didn't follow as to the Aneurism record self-incrementing. That's not
normal in Access, so there may be come code that's assigning a value to the
field, perhaps in Form_Current or some other event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access User said:
I guess I should be less vague about how I think it will work: when the
threshold value of 4 is exceeded, I think the main form label should go
red
and remain red until the offending record gets removed.

The other aspect of the behavior in this sub-form that I'm finding is
unexpected is as follows: if the user returns to the sub-form at a later
point and modifies an entry on one of the records, then the value of
Aneurism
of that record is incremented, which I don't want happening. Is there a
workaround to that?

Thx!

Allen Browne said:
If you expect this to change color in the main form as you move record in
the subform, you would need to use the subform's Current event, as well
as
the AfterUpdate event of the *form* (not control.) Example below.

Private Sub Form_Current()
Dim lngColor As Long

If Me.Aneurism > 4 Then
lngColor = vbRed
Else
lngColor = vbWhite
End If

With Me.Parent!Label113
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

Private Sub Form_AfterUpdate()
Call Form_Current()
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


My main form is called "MRA Form_JPS" and has a child sub-form called
"subfrm_Aneurism". When the value of a control on the subform called
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on the main form to go vbred, so I coded the following
thinking
I
had it

Private Sub Aneurism_AfterUpdate()

If Me.Aneurism > 4 Then
Me.[MRA Form_JPS]!Label113.BackColor = vbRed
Else
Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
End If

End Sub

but it doesn't work yet.

Is there something goofy about the way the above's wrote, or does it
have
to
do with the fact that Aneurism gets its values automatically from this
VBA
code below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1

End Sub

???
Click to expand...
Click to expand...
Click to expand...
 
T

Tennessee Teabagger

I tried this w/o oberving any changes:

Private Sub Aneurism_AfterUpdate()

 IfMe.Aneurism > 4Then
    Me.Parent!Label113.BackColor = vbRed
    Else
    Me.Parent!Label113.BackColor = vbWhite
 EndIf

End Sub



Maurice said:
try this:
Click to expand...
        Me.Parent!Label113.BackColor = vbRed
Click to expand...
themainformcan be refered to as parent...
Click to expand...
hth
Click to expand...
Well, I guess we tried but still no cigar...I changed my VBA to conform
Private Sub Aneurism_AfterUpdate()
 IfMe.Aneurism > 4Then
    Forms![MRA Form_JPS]!Label113.BackColor = vbRed
    Else
    Forms![MRA Form_JPS]!Label113.BackColor = vbWhite
 EndIf
End Sub
but nothing seemingly has been affected.
???
:
Try changing 'Me' to 'Forms' as you are on asubformand thatsubformisMe
and themainformis an entirely separate object.
Forms![MRA Form_JPS]!Label113.BackColor
Hi,
Mymainformis called "MRA Form_JPS" and has a child sub-formcalled
"subfrm_Aneurism". When thevalueof a control on thesubformcalled
"Aneurism" reaches more than 4, I want the backcolor of a label called
"Label113" on themainformto go vbred, so I coded the following thinking
I
had it
Private Sub Aneurism_AfterUpdate()
IfMe.Aneurism > 4Then
   Me.[MRA Form_JPS]!Label113.BackColor = vbRed
   Else
   Me.[MRA Form_JPS]!Label113.BackColor = vbWhite
EndIf
End Sub
but it doesn't work yet.
Is there something goofy about the way the above's wrote, or does it have
to
do with the fact that Aneurism gets its values automatically from this VBA
code below:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Aneurism] = Nz(DMax("[Aneurism]", "qry_Aneurism_JPS", "ID=" _
& Me.ID), 0) + 1
End Sub
???- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

- Google Groups comes through again, this helped me tremendously...
Thanks! - Aaron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populate main form from subform record 0
Changing subform backcolor from main form 8
Interacting with label control of checkbox's 3
Change backcolor of comboox in continue form 4
filtering a form based on the values of a subform 0
Changing BackColor in Subform 2
Using VBA to change background color of a field on a form 2
Is Null Testing for Combo On Opening Form 11

Top