Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / April 2008

Tip: Looking for answers? Try searching our database.

Changing BackColor Property of Main Form Label from SubForm

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Access User - 02 Apr 2008 19:54 GMT
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 Skabialka - 02 Apr 2008 20:26 GMT
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,
>
[quoted text clipped - 30 lines]
>
> ???
Access User - 02 Apr 2008 21:02 GMT
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.

???

> 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.
[quoted text clipped - 34 lines]
> >
> > ???
Maurice - 02 Apr 2008 21:12 GMT
try this:

       Me.Parent!Label113.BackColor = vbRed

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

hth
Signature

Maurice Ausum

> Well, I guess we tried but still no cigar...I changed my VBA to conform
>
[quoted text clipped - 50 lines]
> > >
> > > ???
Access User - 02 Apr 2008 21:19 GMT
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

> try this:
>
[quoted text clipped - 58 lines]
> > > >
> > > > ???
Tennessee Teabagger - 21 Apr 2008 20:36 GMT
On Apr 2, 4:19 pm, Access User <AccessU...@discussions.microsoft.com>
wrote:
> I tried this w/o oberving any changes:
>
[quoted text clipped - 74 lines]
>
> - Show quoted text -

- Google Groups comes through again, this helped me tremendously...
Thanks! - Aaron
Allen Browne - 03 Apr 2008 02:32 GMT
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

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 28 lines]
>
> ???
Access User - 03 Apr 2008 14:30 GMT
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.

> 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
[quoted text clipped - 52 lines]
> >
> > ???
Access User - 03 Apr 2008 14:40 GMT
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!

> 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
[quoted text clipped - 52 lines]
> >
> > ???
Allen Browne - 03 Apr 2008 14:56 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 70 lines]
>> >
>> > ???
Access User - 03 Apr 2008 15:32 GMT
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.

> I guess I'm not clear about how you have this form connected up.
>
[quoted text clipped - 81 lines]
> >> >
> >> > ???
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.