Conditional Formating and SetFocus Question

[gngsquared]

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.

 

[John W. Vinson]

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.

 

[gngsquared]

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.

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]

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

 

[Dirk Goldgar]

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