Question How to change colours in access

[Ahmed@soundevo]

Specific font colours on forms in access

Howdy everyone,

I had a read on this thread w w w.access-programmers.co.uk/forums/showthread.php?t=34499 (please remove the spaces)

which helped me alot! however I'm come across a problem on my side. I've made a database where you input relevant information when a customer comes in store to enquire about products (you enter in their information, what they enquired about and a quotation). Then there is a series of check boxes where if the enquiry has been solved then you tick the box, if the enquiry is on going then you tick the solved box and the on going box, if the enquiry is closed and no further action needed then you tick on the solved, on going and no further action box.

As you move through and check the relevant boxes you press on a button called "process enquiry" (this is where I have the problems). Depending on what check boxes are ticked the colours change, for example red is a no go, yellow is a maybe and green is a go.

I've coded the button to change the boxes based on what is ticked, (if all 3 are ticked then red, if two are ticked then yellow and if one is ticked then green).

Now the problem I have is that the colours change for every single record in my form. So if record 2 is yellow and record 3 and green then both of them change to the same colour. How can I change it so each record changes independetly based on what is ticked ?

Here is how my code looks below (I've included some of it, not all of it)

Private Sub Process_Enquiry_Click()
If Tickbox_Actioned = True And Tickbox_ChasedUp = True And Tickbox_Forget = True Then [EnquiryBox].ForeColor = vbRed Else If Tickbox_Actioned = True And Tickbox_ChasedUp = True Then [EnquiryBox].ForeColor = vbYellow Else If Tickbox_Actioned = True Then [EnquiryBox].ForeColor = vbGreen Else [EnquiryBox].ForeColor = vbWhite
If Tickbox_Actioned = True And Tickbox_ChasedUp = True And Tickbox_Forget = True Then [QuotationBox].ForeColor = vbRed Else If Tickbox_Actioned = True And Tickbox_ChasedUp = True Then [QuotationBox].ForeColor = vbYellow Else If Tickbox_Actioned = True Then [QuotationBox].ForeColor = vbGreen Else [QuotationBox].ForeColor = vbWhite
End
End
End Sub

If required I can put up a picture of the form

My question to you guys is how can i code the button so that when i click on it, it changes each record individually and not every single one.

Thank you for any help!

 

[Ahmed@soundevo]

A further question I have is how do I keep the font color the way it is when I load the form ? I dont really want to have to click on process for every record. Is there a way to keep it the way it was before (i.e. if two boxes were ticked then it should go yellow and that record should stay yellow every time the form is opened, the same for 3 boxes and red and 1 box and green)

 

[John Big Booty]

Re: Specific font colours on forms in access

...

Now the problem I have is that the colours change for every single record in my form. So if record 2 is yellow and record 3 and green then both of them change to the same colour. How can I change it so each record changes independetly based on what is ticked ?

...

From this description, I'm guessing you have a continuous form. This behaviour is typical of Continuous forms. The record that currently holds focus will determine how all records on the form behave when you use code the way you are doing.

You will have better luck using Conditional Formatting to change the appearance of controls for each record.

 

[Ahmed@soundevo]

Hi

Thanks for that link!.

I tried it out however it only seems to change sections of the forms that have number inputs. I want it to change both text and numbers, how would I go about that ? I want it to change the text to the relevant colours when the relevant check box is ticked

 

[Ahmed@soundevo]

Re: Specific font colours on forms in access

You will have better luck using office.microsoft.com/en-us/access-help/change-the-appearance-of-a-control-by-using-conditional-formatting-HA010208133.aspx"]Conditional Formatting to change the appearance of controls for each record.

I used that description but it only seemed to change number fields and not text fields. I followed the tutorial exactly and it didnt seem to work! the tick boxes did work and change it based on the relevant colours however it only changed the number fields. Not text or dates

 

[John Big Booty]

Conditional formatting should work for any type of field, text or numeric

 

[Ahmed@soundevo]

Conditional formatting should work for any type of field, text or numeric

It only applied it to the numeric fields. do you want me to send the database to you so you can see ?

what expression would i need to use ?

Ive used "Tickbox_Actioned=True"

 

[John Big Booty]

Can you post a copy of your DB?

 

[Ahmed@soundevo]

Can you post a copy of your DB?

I certainly can, here it is:
w w w.mediafire.com/?32kxjlod1sgdkis

(remove the spaces because I'm not allowed to post links because I have a post count less than 10)

I've deleted the previous button that i coded but the code is still there in VB just in case

 

[Simon_MT]

Try this must be OnCurrent Event:

Private Sub Form_Current()
    Dim ChangeColour As String

        If Tickbox_Actioned = True And Tickbox_ChasedUp = True And Tickbox_Forget = True Then
            ChangeColour = vbRed
        ElseIf Tickbox_Actioned = True And Tickbox_ChasedUp = True Then
            ChangeColour = vbYellow
        ElseIf Tickbox_Actioned = True Then
            ChangeColour = vbGreen
        Else
            ChangeColour = vbWhite
        End If
        
        [EnquiryBox].ForeColor = ChangeColour
        [QuotationBox].ForeColor = ChangeColour
        [FurtherActionBox].ForeColor = ChangeColour
        [CustomerContactBox].ForeColor = ChangeColour
        [VehicleYearBox].ForeColor = ChangeColour
        [VehicleModelBox].ForeColor = ChangeColour
        [VehicleMakeBox].ForeColor = ChangeColour
        [NameOfStaffBox].ForeColor = ChangeColour
        [DateOfEnquiryBox].ForeColor = ChangeColour
        [EnquiryNumberBox].ForeColor = ChangeColour
End Sub

My preference is only change the Header Colour but your method works as well

Simon

 

[Ahmed@soundevo]

Try this must be OnCurrent Event:

Private Sub Form_Current()
    Dim ChangeColour As String

        If Tickbox_Actioned = True And Tickbox_ChasedUp = True And Tickbox_Forget = True Then
            ChangeColour = vbRed
        ElseIf Tickbox_Actioned = True And Tickbox_ChasedUp = True Then
            ChangeColour = vbYellow
        ElseIf Tickbox_Actioned = True Then
            ChangeColour = vbGreen
        Else
            ChangeColour = vbWhite
        End If
        
        [EnquiryBox].ForeColor = ChangeColour
        [QuotationBox].ForeColor = ChangeColour
        [FurtherActionBox].ForeColor = ChangeColour
        [CustomerContactBox].ForeColor = ChangeColour
        [VehicleYearBox].ForeColor = ChangeColour
        [VehicleModelBox].ForeColor = ChangeColour
        [VehicleMakeBox].ForeColor = ChangeColour
        [NameOfStaffBox].ForeColor = ChangeColour
        [DateOfEnquiryBox].ForeColor = ChangeColour
        [EnquiryNumberBox].ForeColor = ChangeColour
End Sub

My preference is only change the Header Colour but your method works as well

Simon

Ive put that code in and it seems to work but it doesn't update as and when the tick boxes are clicked. It works when you i click on the refresh all button (at the top in the toolbar) or go backwards and forwards through the records.

Is there a way to make it so when the tickbox is clicked it changes the colours ?

 

[Simon_MT]

I prefer to use Function, creature of habit! Create this Function

Function Enquiry_ChangeColor()
Dim ChangeColour As String
    DoCmd.RunCommand acCmdSaveRecord
    With CodeContextObject
        If .[Tickbox_Actioned] = True And .[Tickbox_ChasedUp] = True And .[Tickbox_Forget] = True Then
            ChangeColour = vbRed
        ElseIf .[Tickbox_Actioned] = True And .[Tickbox_ChasedUp] = True Then
            ChangeColour = vbYellow
        ElseIf .[Tickbox_Actioned] = True Then
            ChangeColour = vbGreen
        Else
            ChangeColour = vbWhite
        End If
        
        .[EnquiryBox].ForeColor = ChangeColour
        .[QuotationBox].ForeColor = ChangeColour
        .[FurtherActionBox].ForeColor = ChangeColour
        .[CustomerContactBox].ForeColor = ChangeColour
        .[VehicleYearBox].ForeColor = ChangeColour
        .[VehicleModelBox].ForeColor = ChangeColour
        .[VehicleMakeBox].ForeColor = ChangeColour
        .[NameOfStaffBox].ForeColor = ChangeColour
        .[DateOfEnquiryBox].ForeColor = ChangeColour
    End With
End Function

Form On Current Event =Enquiry_ChangeColor()
Tickbox_Actioned After Update =Enquiry_ChangeColor()
Tickbox_ChasedUp After Update =Enquiry_ChangeColor()
Tickbox_Forget After Update =Enquiry_ChangeColor()

Simon

 

[Ahmed@soundevo]

I prefer to use Function, creature of habit! Create this Function

Function Enquiry_ChangeColor()
Dim ChangeColour As String
    DoCmd.RunCommand acCmdSaveRecord
    With CodeContextObject
        If .[Tickbox_Actioned] = True And .[Tickbox_ChasedUp] = True And .[Tickbox_Forget] = True Then
            ChangeColour = vbRed
        ElseIf .[Tickbox_Actioned] = True And .[Tickbox_ChasedUp] = True Then
            ChangeColour = vbYellow
        ElseIf .[Tickbox_Actioned] = True Then
            ChangeColour = vbGreen
        Else
            ChangeColour = vbWhite
        End If
        
        .[EnquiryBox].ForeColor = ChangeColour
        .[QuotationBox].ForeColor = ChangeColour
        .[FurtherActionBox].ForeColor = ChangeColour
        .[CustomerContactBox].ForeColor = ChangeColour
        .[VehicleYearBox].ForeColor = ChangeColour
        .[VehicleModelBox].ForeColor = ChangeColour
        .[VehicleMakeBox].ForeColor = ChangeColour
        .[NameOfStaffBox].ForeColor = ChangeColour
        .[DateOfEnquiryBox].ForeColor = ChangeColour
    End With
End Function

Form On Current Event =Enquiry_ChangeColor()
Tickbox_Actioned After Update =Enquiry_ChangeColor()
Tickbox_ChasedUp After Update =Enquiry_ChangeColor()
Tickbox_Forget After Update =Enquiry_ChangeColor()

Simon

thanks Simon!

however it didnt seem to work at all I think I'll just stick with the original code that you put up!

 

[Simon_MT]

I have uploaded a version with the Function.

Simon