473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

how to change forecolour in combo box based on selection

SteHawk85
Hi

I have a combo box with 3 options - Red, Amber and Green. I have wrote a bit of code (below) that I was hoping would change the fore colour of the box dependent on which option was selected, i.e. red = red etc. My code can be seen below, I am not getting any errors when I run it, but the fore colour is not changing when an option is selected and have tried it in both before update and after update.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRagRating_AfterUpdate()
  2. On Error GoTo ErrorHandler
  3.  
  4.    If Me.cboRagRating.Value = "Red" Then
  5.       Me.cboRagRating.ForeColor = vbRed
  6.  
  7.    ElseIf Me.cboRagRating.Value = "Amber" Then
  8.           Me.cboRagRating.ForeColor = RGB(255, 245, 238)
  9.  
  10.    ElseIf Me.cboRagRating.Value = "Green" Then
  11.           Me.cboRagRating.ForeColor = vbGreen
  12.    End If
  13.  
  14. CleanUpAndExit:
  15.     Exit Sub
  16.  
  17. ErrorHandler:
  18.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  19.     Resume CleanUpAndExit
  20. End Sub
Any help would be greatly appreciated

Thanks

Ste
Apr 17 '12 #1

✓ answered by Mariostg

Not quite, because if you do that, then when you do a selection on your combo box then the color will not update. So that being said, you need it at both places :)
Or you write a function (basically what you wrote) that you place in a module as opposed to be in the form module. Then each of the On current and After Update events would call the same function. It is a little more advanced, but cleaner.

Other option, would be that the On current event calls the After Update. So you would have something like Call cboRagRating_AfterUpdate() if you see what I mean.

15 2343
Mariostg
332 100+
Well, your code is working here and I am on Access 2003.
On the rare occasions I needed something like that, I prefer to alter the color of the caption of the combo box instead. And I change the background instead of the foreground.
Apr 17 '12 #2
Hi

Yeah I figured out what I was doing wrong and its working now except for one thing. Best give you some background first I am building a pupil database base and the form shows 1 pupils details at a time, now when I skip to the next pupil it carries over the fore colour from the previous page i.e. if pupil one is selected as red the fore colour changes to red, however when I skip to the next pupil the fore colour is red even if the option is Green! Below is the new code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRagRating_AfterUpdate()
  2. On Error GoTo ErrorHandler
  3.  
  4.    If Me.cboRagRating.Value = "1" Then
  5.       Me.cboRagRating.ForeColor = vbRed
  6.  
  7.    ElseIf Me.cboRagRating.Value = "2" Then
  8.           Me.cboRagRating.ForeColor = RGB(255, 245, 238)
  9.  
  10.    ElseIf Me.cboRagRating.Value = "3" Then
  11.           Me.cboRagRating.ForeColor = vbGreen
  12.      End If
  13.  
  14. CleanUpAndExit:
  15.     Exit Sub
  16.  
  17. ErrorHandler:
  18.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  19.     Resume CleanUpAndExit
  20. End Sub
Any ideas? Again help is greatly appreciated

Ste
Apr 17 '12 #3
Mariostg
332 100+
That probably has something to do with the On current property of the form. Basically, when you navigate from one record to another , you have to check what the value of the combo box is and then set the color accordingly.
Apr 17 '12 #4
Mariostg

I'm still quite new to VBA, however the Combo box doesn't seem to have an 'On Current' option in the event tab, can you expand on what you mean?

Thanks by the way for the Back Colour idea have changed my code and that looks a lot better!
Apr 17 '12 #5
Mariostg
332 100+
Welcome to VBA.
The combo box indeed does not, but the form has many event properties and one of them is On Current. The On current Event is activated when you move from one record to another one.
Apr 17 '12 #6
cheers mate would it just be a case of moving the code over to the 'On Current' event of the form?
Apr 17 '12 #7
Mariostg
332 100+
Not quite, because if you do that, then when you do a selection on your combo box then the color will not update. So that being said, you need it at both places :)
Or you write a function (basically what you wrote) that you place in a module as opposed to be in the form module. Then each of the On current and After Update events would call the same function. It is a little more advanced, but cleaner.

Other option, would be that the On current event calls the After Update. So you would have something like Call cboRagRating_AfterUpdate() if you see what I mean.
Apr 17 '12 #8
Nice one mate Super Star that 'Call cboRagRating_AfterUpdate()' has worked an absolute treat! : D
Apr 17 '12 #9
Mariostg
332 100+
You are welcome.
Feel free to mark your question answered.
Apr 17 '12 #10
NeoPa
32,556 Expert Mod 16PB
SteHawk85:
Nice one mate Super Star that 'Call cboRagRating_AfterUpdate()' has worked an absolute treat! : D
That does indeed work Ste. However, it was no accident that Mario first suggested putting the code in a standard procedure and calling this from both event procedures. Maintenance-wise, calling one event procedure from another is considered messy. On the plus side, while putting the standard procedure in a separate, standard module is viable, it isn't necessary. If a procedure is used exclusively for a process related to a specific form, then it makes perfect sense to create it as a Private procedure within the form's module. The result is much tidier than calling one event procedure from another.
Apr 18 '12 #11
so basically i write a module with the code and that works without me having to use the code?
Expand|Select|Wrap|Line Numbers
  1. Call cboRagRating_AfterUpdate()
It's working fine at the min but if this would make it easier to use in future projects I wouldn't mind understanding what you mean fully.
May 14 '12 #12
NeoPa
32,556 Expert Mod 16PB
Let me try to explain then. Assume you have two different events (EG. Form_Current() and X_AfterUpdate()) that require code ({CODE}) to be run whenever they are triggered. There are two way this can be handled within the same module - that of the form itself. #1 is not recommended as it imposes a non-flexible structure and undesirable dependencies on the code. Thus, #2 is more maintainable and easier to understand and deal with.
  1. Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2.     {CODE}
    3. End Sub
    4.  
    5. Private Sub X_AfterUpdate()
    6.     Call Form_Current()
    7. End Sub
  2. Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2.     Call CommonRoutine()
    3. End Sub
    4.  
    5. Private Sub X_AfterUpdate()
    6.     Call CommonRoutine()
    7. End Sub
    8.  
    9. Private Sub CommonRoutine()
    10.     {CODE}
    11. End Sub
May 14 '12 #13
I see! so all i have to do is move the Code from Form_Current into a module called 'Common Routine' and then use the call function in both the 'After Update' and 'Current Form'.

cheers Neo once again you have helped me out!
May 17 '12 #14
NeoPa
32,556 Expert Mod 16PB
SteHawk85:
so all i have to do is move the Code from Form_Current into a module called 'Common Routine' and then ...
Not unless you mean procedure rather than module. In that case then yes. Absolutely :-)
May 17 '12 #15
haha yeah still getting used to the lingo!
May 18 '12 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: BigJay | last post by:
I am 1. trying to have a combobox used as a selector to display records in a subform. and not sure on how to get selected info into subform.the combo is populated but can not get subform updated...
6
by: Kazza | last post by:
Hi I'm using Access 2003 and I need to figure out how to display the record details based on a combo box selection. For example, when I select a company name in a combo box, how do I get the related...
3
by: sstasiak | last post by:
Hello all I have read similar threads on this site, but can't seem to get any of the solutions working for me. I have a main form(OncRegMain) that adds records to a table(tblOncReg). This table...
3
by: wideasleep | last post by:
Hello Everyone, I am looking for a way to trigger specific subforms to come up in a form through a combo box selection. For example purposes my selection in the combo box is CCP-03-081-FRM-14. The...
1
by: ikuyasu | last post by:
Hi, I am trying to create a report that takes a value from the field on a table as a group category. But The value on the report takes an id number (auto increment, and the first column)...
6
by: troy_lee | last post by:
I have a continuous form that has one combo box (cbo1) with the selectable values of "Removal" and "Installation". I would like to change another combo box (cbo2) value list based on the selection...
1
by: zufie | last post by:
Hi, When I make a selection from the drop down list of values on a Combo Box. The Combo Box is on a Form. The Combo Box selection appears on ALL of the records. That is, when I select one...
27
by: DanicaDear | last post by:
Dealing with electric utility rubber gloves..... tblCOMMODITY contains fields CLASS, SIZE, & COMMODITY_NUM (PK). A class combined with a size make a commodity number. I don't want the user...
3
by: ranga044 | last post by:
Hi Guys, I'm implementing a system these days, i want to implement a combo box selection process but i don't know how to implement it, so asking you guys favor? my scenario is this, let's say...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.