Conditional Formating and SetFocus Question

Discussion in 'Microsoft Access VBA Modules' started by gngsquared, Apr 24, 2010.

  1. gngsquared

    gngsquared Guest

    Thanks in advace. This place has answered a lot of questions before I needed
    to ask. Now, I need to ask.

    I have a Continous Form [DetailSubForm] based on table [DetailTbl] that has
    the following fields. *[DetailID], [DocID], [CompDate],[SubDate],
    [ConfirmDate], and [ExpireDate]. The [DocID] field is a dropdown list that
    draws the data from the [TrainDocTbl].

    I would like to use GetFocus to go to one of the Date fields depending on
    the selection made in [DocID]. It would also be nice to be able to declare
    the backcolor and forecolor of the whole record depending on that choice.
     
    gngsquared, Apr 24, 2010
    #1
    1. Advertisements

  2. On Sat, 24 Apr 2010 09:00:01 -0700, gngsquared
    <> wrote:

    >Thanks in advace. This place has answered a lot of questions before I needed
    >to ask. Now, I need to ask.
    >
    >I have a Continous Form [DetailSubForm] based on table [DetailTbl] that has
    >the following fields. *[DetailID], [DocID], [CompDate],[SubDate],
    >[ConfirmDate], and [ExpireDate]. The [DocID] field is a dropdown list that
    >draws the data from the [TrainDocTbl].
    >
    >I would like to use GetFocus to go to one of the Date fields depending on
    >the selection made in [DocID]. It would also be nice to be able to declare
    >the backcolor and forecolor of the whole record depending on that choice.
    >
    >
    >


    You can use code in the AfterUpdate event of DocID:

    Private Sub DocID_AfterUpdate
    Select Case Me!DocID
    Case "this"
    Me!CompDate.Setfocus
    Case "that"
    Me!SubDate.Setfocus
    Case "TheOther"
    <and so on>
    End Select
    End Sub

    For the color, select any or all of the controls on the subform, and choose
    "Format"... "Conditional Formatting" from the menu.
    --

    John W. Vinson [MVP]
     
    John W. Vinson, Apr 24, 2010
    #2
    1. Advertisements

  3. gngsquared

    gngsquared Guest

    That works great. Thank you.

    Can I use the same list to change the BackColor of the field depending on
    the selection? If I use Conditional Format to the field I am limited to 3
    choices. I would need at least 4 or 5 colors to do it right. And it would be
    useful elsewhere.


    "John W. Vinson" wrote:

    > On Sat, 24 Apr 2010 09:00:01 -0700, gngsquared
    > <> wrote:
    >
    > >Thanks in advace. This place has answered a lot of questions before I needed
    > >to ask. Now, I need to ask.
    > >
    > >I have a Continous Form [DetailSubForm] based on table [DetailTbl] that has
    > >the following fields. *[DetailID], [DocID], [CompDate],[SubDate],
    > >[ConfirmDate], and [ExpireDate]. The [DocID] field is a dropdown list that
    > >draws the data from the [TrainDocTbl].
    > >
    > >I would like to use GetFocus to go to one of the Date fields depending on
    > >the selection made in [DocID]. It would also be nice to be able to declare
    > >the backcolor and forecolor of the whole record depending on that choice.
    > >
    > >
    > >

    >
    > You can use code in the AfterUpdate event of DocID:
    >
    > Private Sub DocID_AfterUpdate
    > Select Case Me!DocID
    > Case "this"
    > Me!CompDate.Setfocus
    > Case "that"
    > Me!SubDate.Setfocus
    > Case "TheOther"
    > <and so on>
    > End Select
    > End Sub
    >
    > For the color, select any or all of the controls on the subform, and choose
    > "Format"... "Conditional Formatting" from the menu.
    > --
    >
    > John W. Vinson [MVP]
    > .
    >
     
    gngsquared, Apr 25, 2010
    #3
  4. On Sat, 24 Apr 2010 16:30:01 -0700, gngsquared
    <> wrote:

    >That works great. Thank you.
    >
    >Can I use the same list to change the BackColor of the field depending on
    >the selection? If I use Conditional Format to the field I am limited to 3
    >choices. I would need at least 4 or 5 colors to do it right. And it would be
    >useful elsewhere.


    Sure. Just set the Backcolor property in the same code; each Case of a Select
    Case block can contain as many lines of code as you need:

    Private Sub DocID_AfterUpdate
    Select Case Me!DocID
    Case "this"
    Me!CompDate.Setfocus
    Me!CompDate.BackColor = vbRed
    Case "that"
    Me!SubDate.Setfocus
    Me!CompDate.BackColor = vbGreen
    Case "TheOther"
    <and so on>
    End Select
    End Sub
    --

    John W. Vinson [MVP]
     
    John W. Vinson, Apr 25, 2010
    #4
  5. gngsquared

    Dirk Goldgar Guest

    "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:...
    > On Sat, 24 Apr 2010 16:30:01 -0700, gngsquared
    > <> wrote:
    >>
    >>Can I use the same list to change the BackColor of the field depending on
    >>the selection? If I use Conditional Format to the field I am limited to 3
    >>choices. I would need at least 4 or 5 colors to do it right. And it would
    >>be
    >>useful elsewhere.

    >
    > Sure. Just set the Backcolor property in the same code; each Case of a
    > Select
    > Case block can contain as many lines of code as you need:
    >
    > Private Sub DocID_AfterUpdate
    > Select Case Me!DocID
    > Case "this"
    > Me!CompDate.Setfocus
    > Me!CompDate.BackColor = vbRed
    > Case "that"
    > Me!SubDate.Setfocus
    > Me!CompDate.BackColor = vbGreen
    > Case "TheOther"
    > <and so on>
    > End Select
    > End Sub



    But be aware that on a continuous form, property changes made in code like
    this -- as opposed to by conditional formatting -- will apply to every
    record, not just the current one.

    Conditional formatting's limitation of 3 conditions per object can be hard
    to get around. This question came up recently in another thread, and I
    posted this (rather cumbersome) workaround, which may possible be useful to
    gngsquared:

    http://groups.google.com/group/microsoft.public.access/msg/0c762dfbf998e669


    --
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    (please reply to the newsgroup)
     
    Dirk Goldgar, Apr 25, 2010
    #5
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    conditional formating

    Guest, Nov 12, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    182
    Guest
    Nov 13, 2004
  2. SAC

    Flag conditional formating

    SAC, Dec 13, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    148
  3. CES
    Replies:
    4
    Views:
    263
  4. Guest

    Must SetFocus! May not SetFocus!

    Guest, Jun 13, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    5
    Views:
    347
    Guest
    Jun 13, 2007
  5. Guest

    Trim() not trim(); SetFocus() not setFocus()

    Guest, Oct 7, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    277
    Guest
    Oct 8, 2007
Loading...

Share This Page