If...then...else - what's wrong?

K

Kirstie Adam

I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
 
C

Carl Rapson

Kirstie Adam said:
I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
Click to expand...

You might try putting the line

DoEvents

right after the End If and before the End Sub. Sometimes changes such as
this don't occur until Windows has a chance to process all outstanding
events, and there may be others in line before yours. At least, this has
worked for me in the past.

As to where to put this, it depends. If STATUS is a field in the current
record on a form, you'd probably want to put this code in the Form_Current
event.

Carl Rapson
 
D

Damon Heron

If that is really your code then you need to start with an IF statement

Damon
 
F

fredg

I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
Click to expand...

You don't have an If in your If ElseIf statement. All I see are
ElseIf's.

Use the Form's Current event so that the Status backcolor will change
according to whatever it's current record value is.

Then place the same code in the Status AfterUpdate event, so that if
it's value is changed it will be reflected in the record immediately.

Private Sub Form_Current()
If Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else
Me.Status.BackColor = vbWhite ' a Default value
End If

I also notice that some values use the same color.

Private Sub Form_Current()
If Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Or Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" or Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else
Me.Status.BackColor = vbWhite ' the Default color value
End If
 
S

SteveS

Hi Kirstie,

It doesn't work because of improper syntax.

It has to be like this:

Private Sub Form_Open(Cancel As Integer)
If Me.STATUS = "IN PLANNING" Then
^^^ -----snip-----

( remove the "ELSE" in the first line.)


It would be easier (IMO) to use "Select Case">
This is what it would look like:

Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN"
Me.Status.BackColor = 32768
Case "CONSTRUCTION"
Me.Status.BackColor = 32768
Case "COMPLETED"
Me.Status.BackColor = 8421504
Case "ON HOLD"
Me.Status.BackColor = 8421504
End Select


Depending on what the form is used for, I would start out with placing the
code in the after update event of "Me.Status" and possibly in the form "On
Current" event.

HTH
 
S

SteveS

After I posted, I saw fredg"s response.

I missed the duplicate color values, :(
so here is the modified "Select Case" construct:


Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN", "COMPLETED"
Me.Status.BackColor = 32768
Case "COMPLETED", "ON HOLD"
Me.Status.BackColor = 8421504
Case Else
Me.Status.BackColor = vbWhite ' the Default color value
End Select



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SteveS said:
Hi Kirstie,

It doesn't work because of improper syntax.

It has to be like this:

Private Sub Form_Open(Cancel As Integer)
If Me.STATUS = "IN PLANNING" Then
^^^ -----snip-----

( remove the "ELSE" in the first line.)


It would be easier (IMO) to use "Select Case">
This is what it would look like:

Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN"
Me.Status.BackColor = 32768
Case "CONSTRUCTION"
Me.Status.BackColor = 32768
Case "COMPLETED"
Me.Status.BackColor = 8421504
Case "ON HOLD"
Me.Status.BackColor = 8421504
End Select


Depending on what the form is used for, I would start out with placing the
code in the after update event of "Me.Status" and possibly in the form "On
Current" event.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kirstie Adam said:
I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
Click to expand...
Click to expand...
 
K

Kirstie Adam

Ooops sorry damon, that was just a copy and paste thing, it does start with
an if statement!


Damon Heron said:
If that is really your code then you need to start with an IF statement

Damon
Kirstie Adam said:
I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
Click to expand...
Click to expand...
 
K

Kirstie Adam

actually, i want to have duplicate colours for some values. is that a
problem or can that be done?

basically, we are going for 3 colours - red, amber and green. and the
various status's will fall into one of those colours.

i do want to stress it really does start with an if statement, just i missed
a bit when i was copying it!

off to try all your suggestions, will let you know....

thanks to all...


kirstie

SteveS said:
After I posted, I saw fredg"s response.

I missed the duplicate color values, :(
so here is the modified "Select Case" construct:


Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN", "COMPLETED"
Me.Status.BackColor = 32768
Case "COMPLETED", "ON HOLD"
Me.Status.BackColor = 8421504
Case Else
Me.Status.BackColor = vbWhite ' the Default color value
End Select



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SteveS said:
Hi Kirstie,

It doesn't work because of improper syntax.

It has to be like this:

Private Sub Form_Open(Cancel As Integer)
If Me.STATUS = "IN PLANNING" Then
^^^ -----snip-----

( remove the "ELSE" in the first line.)


It would be easier (IMO) to use "Select Case">
This is what it would look like:

Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN"
Me.Status.BackColor = 32768
Case "CONSTRUCTION"
Me.Status.BackColor = 32768
Case "COMPLETED"
Me.Status.BackColor = 8421504
Case "ON HOLD"
Me.Status.BackColor = 8421504
End Select


Depending on what the form is used for, I would start out with placing
the
code in the after update event of "Me.Status" and possibly in the form
"On
Current" event.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kirstie Adam said:
I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Hi Kirstie,

I went back and read you first post again. Seeing the code fragment, I saw a
problem and responded to what I thought was a syntax error.


I now realize that:
Can someone please tell me why this is not working?
Click to expand...

was a question on conditional formatting, not about the code.
(Does "not working" mean that the control in all records changes color??)

The code using the IF..Else..End If function and the code using the Select
Case function will "work" if the form is in "Single Form" view. When you
switch to "Continuous Forms" view, you start having problems.

For color changes on Continuous Forms, you have to use conditional
formatting (see the Format menu), but you are limited to 4 formats; 3
conditions plus a default format.

There is a work around, but it takes a little work to get the colors to
change.

Here is a link to an example for A2K and higher: (should be one line)

http://rogersaccesslibrary.com/download3.asp?SampleName=ConditionalFormatting.mdb


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kirstie Adam said:
actually, i want to have duplicate colours for some values. is that a
problem or can that be done?

basically, we are going for 3 colours - red, amber and green. and the
various status's will fall into one of those colours.

i do want to stress it really does start with an if statement, just i missed
a bit when i was copying it!

off to try all your suggestions, will let you know....

thanks to all...


kirstie

SteveS said:
After I posted, I saw fredg"s response.

I missed the duplicate color values, :(
so here is the modified "Select Case" construct:


Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN", "COMPLETED"
Me.Status.BackColor = 32768
Case "COMPLETED", "ON HOLD"
Me.Status.BackColor = 8421504
Case Else
Me.Status.BackColor = vbWhite ' the Default color value
End Select



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SteveS said:
Hi Kirstie,

It doesn't work because of improper syntax.

It has to be like this:

Private Sub Form_Open(Cancel As Integer)
If Me.STATUS = "IN PLANNING" Then
^^^ -----snip-----

( remove the "ELSE" in the first line.)


It would be easier (IMO) to use "Select Case">
This is what it would look like:

Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN"
Me.Status.BackColor = 32768
Case "CONSTRUCTION"
Me.Status.BackColor = 32768
Case "COMPLETED"
Me.Status.BackColor = 8421504
Case "ON HOLD"
Me.Status.BackColor = 8421504
End Select


Depending on what the form is used for, I would start out with placing
the
code in the after update event of "Me.Status" and possibly in the form
"On
Current" event.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not sure
which event or events it is best to put it under?

Kirstie
Click to expand...
Click to expand...
Click to expand...
 
K

Kirstie Adam

that's great thanks very much

next time will double check my post to make sure it's all there and clear!

kirstie

SteveS said:
Hi Kirstie,

I went back and read you first post again. Seeing the code fragment, I saw
a
problem and responded to what I thought was a syntax error.


I now realize that:
Can someone please tell me why this is not working?
Click to expand...

was a question on conditional formatting, not about the code.
(Does "not working" mean that the control in all records changes color??)

The code using the IF..Else..End If function and the code using the Select
Case function will "work" if the form is in "Single Form" view. When you
switch to "Continuous Forms" view, you start having problems.

For color changes on Continuous Forms, you have to use conditional
formatting (see the Format menu), but you are limited to 4 formats; 3
conditions plus a default format.

There is a work around, but it takes a little work to get the colors to
change.

Here is a link to an example for A2K and higher: (should be one line)

http://rogersaccesslibrary.com/download3.asp?SampleName=ConditionalFormatting.mdb


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kirstie Adam said:
actually, i want to have duplicate colours for some values. is that a
problem or can that be done?

basically, we are going for 3 colours - red, amber and green. and the
various status's will fall into one of those colours.

i do want to stress it really does start with an if statement, just i
missed
a bit when i was copying it!

off to try all your suggestions, will let you know....

thanks to all...


kirstie

SteveS said:
After I posted, I saw fredg"s response.

I missed the duplicate color values, :(
so here is the modified "Select Case" construct:


Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN", "COMPLETED"
Me.Status.BackColor = 32768
Case "COMPLETED", "ON HOLD"
Me.Status.BackColor = 8421504
Case Else
Me.Status.BackColor = vbWhite ' the Default color value
End Select



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Hi Kirstie,

It doesn't work because of improper syntax.

It has to be like this:

Private Sub Form_Open(Cancel As Integer)
If Me.STATUS = "IN PLANNING" Then
^^^ -----snip-----

( remove the "ELSE" in the first line.)


It would be easier (IMO) to use "Select Case">
This is what it would look like:

Select Case Me.Status
Case "IN PLANNING"
Me.Status.BackColor = 33023
Case "DESIGN"
Me.Status.BackColor = 32768
Case "CONSTRUCTION"
Me.Status.BackColor = 32768
Case "COMPLETED"
Me.Status.BackColor = 8421504
Case "ON HOLD"
Me.Status.BackColor = 8421504
End Select


Depending on what the form is used for, I would start out with placing
the
code in the after update event of "Me.Status" and possibly in the form
"On
Current" event.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have a combo box [Status] which i want to be a different BackColor
dependant on it's value

eg If [Status] = "Planning", backcolor = vbRed

the code i am using is:

Private Sub Form_Open(Cancel As Integer)
ElseIf Me.STATUS = "IN PLANNING" Then
Me.STATUS.BackColor = 33023
ElseIf Me.STATUS = "DESIGN" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "CONSTRUCTION" Then
Me.STATUS.BackColor = 32768
ElseIf Me.STATUS = "COMPLETED" Then
Me.STATUS.BackColor = 8421504
ElseIf Me.STATUS = "ON HOLD" Then
Me.STATUS.BackColor = 8421504
Else

End If
End Sub

Can someone please tell me why this is not working? also, i am not
sure
which event or events it is best to put it under?

Kirstie
Click to expand...
Click to expand...
Click to expand...
 

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

Continuous Form: Change the color of one row 1
Code help with IsNot( ) operator 3
Update one text box based on another text box 2
Else If Directive 5
Update field w/ string based on service due dates 4
BackColor change 5
creating "map" based on field values 15
If value selected in one field the enter date in another field 3

Top