Conditional Format code

[rbrule]

Hello, I am trying to set up conditional formatting for a text box in a subform. the subform is in datasheet view the fieldname is [status]. I have entered the following code in the after_update property. I am looking for the background and text to change color under several different conditions. My code isn't working. Can someone check it out and tell me what I did wrong. I get an error message that says "Block if without End if". I have no idea what that means, as you can see I have an End if.

Private Sub Status_AfterUpdate()

If Me!Status = "GO" Then
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlue

If Me!Status = "NOGO" Then
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlue

If Me!Status = "Postponed" Then
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlue

If Me!Status = "GO/NOGO" Then
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlue

If Me!Status = "GATE 3 GO/NOGO" Then
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlue

If Me!Status = "Y" Then
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbYellow

If Me!Status = "K" Then
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlack

If Me!Status = "G" Then
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbGreen

If Me!Status = "Status Readout" Then
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbGreen

If Me!Status = "Plan Readout" Then
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbGreen

If Me!Status = "R" Then
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbRed

Else
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbWhite
End If
End Sub

 

[pbaldy]

The immediate solution to your error is that each If must have an End If.

That said, I don't know if your code will accomplish the desired goal anyway.

 

[boblarson]

Save yourself some pain -

Use select statements instead:

Dim strStatus
strStatus = Me!Status

Select Case strStatus

Case "GO", “NOGO”, “Postponed”, “GO/NOGO”, “GATE 3 GO/NOGO”
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlue

Case "Y" 
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbYellow

Case "K" 
Me!Status.ForeColor = vbWhite
Me!Status.BackColor = vbBlack

Case "G", "Status Readout", "Plan Readout"
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbGreen

Case "R"
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbRed

Case Else
Me!Status.ForeColor = vbBlack
Me!Status.BackColor = vbWhite

End Select

 

[pbaldy]

Bob, will that work in a datasheet form? No question your construct is more efficient, but I have doubts about either method working on a form. Conditional Formatting would be the tool, but there are too many options here.

 

[boblarson]

Oh, I missed the Datasheet part. No it won't work in datasheet mode. I don't think any formatting will work in datasheet mode. It would have to be in continuous form mode and I'm not even sure it would work in that mode either.

 

[RoyVidar]

If conditional formatting doesn't do the trick - which does work in datasheets - then perhaps what can be found here http://www.mvps.org/access/forms/frm0024.htm? This is probably for a continuous form, though (I haven't tried, just guessing )

 

[pbaldy]

It won't. This type of thing works on a report in the detail format event, but forms have no such event (at least not in A2k).

 

[boblarson]

However the fixed original code would work in a single form.

 

[pbaldy]

Yes, it would. I just realized I said I had doubts about it working in a form, when I really meant a form in continuous or datasheet view. Sorry.

 

[rbrule]

Thank you all, your responses will save me a lot of time and aggravation. Happy Holidays!!!

 

[Cosmos75]

You can simulate multiple conditional formats in a continuous form, but not in a datasheet view as others have already pointed out.

Conditional Formatting of controls in a continuous form