How to change format on one record (continuous forms)

[bsnalex]

The long and short of this week's problem is this:

I have a list of patients that are seeing various clinicians in a clinic. Currently everything is on paper and when a patient arrives we highlight their name on the list. So now that I'm finally moving everything paperless, I want to have the record become "highlighted" when I select patient has arrived. The only problem is as it's a continuous form, the formatting changes on all records. Here is the screenshot before anyone arrives:

Those are two separate records that you see. Behind all of those fields is a box coloured #E7F442 (optic yellow-ish). What I want is when I select "Arrived" from the dropdown, for the fields on the left (name, number, appointment type, etc) to become transparent so the yellow shows through and looks highlighted. I've used this code:

Private Sub ATCST_AfterUpdate()

If Me.ATCST = "Arrived" Then
    Me.RGBCHID.BackStyle = 0
    Me.PATNAME.BackStyle = 0
    Me.TCDESC.BackStyle = 0
    Me.VISPURP.BackStyle = 0
    Me.ATCMNT.BackStyle = 0
    Me.ATTIME.BackStyle = 0
    Me.ATCST.BackStyle = 0

ElseIf Me.ATCST = "Not Arrived" Then
    Me.RGBCHID.BackStyle = 1
    Me.PATNAME.BackStyle = 1
    Me.TCDESC.BackStyle = 1
    Me.VISPURP.BackStyle = 1
    Me.ATCMNT.BackStyle = 1
    Me.ATTIME.BackStyle = 1
    Me.ATCST.BackStyle = 1

End If
End Sub

However that code leads to this happening:

Making the whole code utterly useless, as it's changing the formatting on every record. Is there a way to program this so the formatting changes only affect the record I'm working on at that moment?

 

[CJ_London]

you need to use conditional formatting - note this only applies to certain controls such as text and comboboxes but not others such as labels and check boxes.

it would be something like

Expression Is....[ATCST] = "Arrived"

and set the back colour to colour required

 

[bsnalex]

So where would I put that conditional formatting? In the query that collects the form data or in the VB?

 

[bsnalex]

Have now altered the code to this:

Private Sub ATCST_AfterUpdate()

Dim Arr As Long
Dim WNB As Long
Dim Wht As Long

Arr = RGB(231, 244, 66) 'Colour Code is #E7F442
WNB = RGB(0, 255, 0) 'Colour Code is #00FF00
Wht = RGB(255, 255, 255) 'Colour Code is #FFFFFF


If Me.ATCST = "Arrived" Then
    Me.RGBCHID.BackColor = Arr
    Me.PATNAME.BackColor = Arr
    Me.TCDESC.BackColor = Arr
    Me.VISPURP.BackColor = Arr
    Me.ATCMNT.BackColor = Arr
    Me.ATTIME.BackColor = Arr
    Me.ATCST.BackColor = Arr

ElseIf Me.ATCST = "WNB" Then
    Me.RGBCHID.BackColor = WNB
    Me.PATNAME.BackColor = WNB
    Me.TCDESC.BackColor = WNB
    Me.VISPURP.BackColor = WNB
    Me.ATCMNT.BackColor = WNB
    Me.ATTIME.BackColor = WNB
    Me.ATCST.BackColor = WNB

Else: Me.RGBCHID.BackColor = Wht
    Me.PATNAME.BackColor = Wht
    Me.TCDESC.BackColor = Wht
    Me.VISPURP.BackColor = Wht
    Me.ATCMNT.BackColor = Wht
    Me.ATTIME.BackColor = Wht
    Me.ATCST.BackColor = Wht

End If
End Sub

And the result is the same (but this time with three colours!)

 

[CJ_London]

in form design - you will see conditional formatting on the ribbon. You can have more than one condition

 

[bsnalex]

Thanks, CJ. Worked a treat. I honestly never registered that there was conditional formatting in access--I was always under the impression things like this had to be done with VBA (which I would actually prefer, for the practice in writing it).

 

[CJ_London]

you can use code if you want

here is a link to an example

http://blogs.msdn.com/b/frice/archive/2004/06/08/151178.aspx