If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting on non-numerical value
Hi there
I apologise for my lack of understanding about Access 2003, I'm still learning and hoping you can help make me better! I have designed a form, and need to conditionally format one of the controls based on its own contents. I have managed to make it work when I am working with numerical vaules, but can't seem to get my head around making it work with text inputs. Effectively the form displays projects. Each project is taken from a record on one table. Against each project is one of four project administrators (the administrators are stored in a separate table on a one-to-many relationship). So, I am trying to get the form to change the colour of the administrator control depending on which administrator has been selected for the project. As a second request (which is not a priority by any means) is it possible to change the background colour of the form itself dependent on the contents of one of its controls? |
#2
|
|||
|
|||
Conditional Formatting on non-numerical value
In Conditional Formatting you'll need to use
Expression Is then [Administrator]="Washington" where Washington is the admin. Since you have 4 administrators, you'll have to settle for using the Default Formatting for one of them. As for your second question, what kind of form are you talking about? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Conditional Formatting on non-numerical value
Hi Ben,
Try the following in the Northwind.mdb sample database: To implement Conditional Formatting on text values: 1.) Open the Categories form in design view. Display the form's Properties dialog (View | Properties, or the F4 button). Select the CategoryName text box. You should see "Text Box: CategoryName" in the blue title bar of the Properties dialog. Click on Format | Conditional Formatting... 2.) Add the following two conditions: Condition 1 Field Value Is equal to "Beverages" Select a bold red font with a white background Condition 2 Field Value Is equal to "Condiments" Select a bold green font with a blue background To change the background color of the form, based on a value in the CategoryName field: 1.) With the word "Form" showing in the blue title bar of the Properties dialog, select the Format tab. Remove the (bitmap) from the Picture property. 2.) Copy and paste the following VBA code into the Form_Current procedure for this form. You can add additional Case statements, as desired: Option Compare Database Option Explicit Private Sub Form_Current() On Error GoTo ProcError Select Case Me.CategoryName Case "Beverages" Me.Detail.BackColor = 255 'Red Case "Condiments" Me.Detail.BackColor = 33023 'Orange Case Else Me.Detail.BackColor = 12632256 'Gray End Select ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in Form_Current event procedure..." Resume ExitProc End Sub Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Ben M Rowe" wrote: Hi there I apologise for my lack of understanding about Access 2003, I'm still learning and hoping you can help make me better! I have designed a form, and need to conditionally format one of the controls based on its own contents. I have managed to make it work when I am working with numerical vaules, but can't seem to get my head around making it work with text inputs. Effectively the form displays projects. Each project is taken from a record on one table. Against each project is one of four project administrators (the administrators are stored in a separate table on a one-to-many relationship). So, I am trying to get the form to change the colour of the administrator control depending on which administrator has been selected for the project. As a second request (which is not a priority by any means) is it possible to change the background colour of the form itself dependent on the contents of one of its controls? |
#4
|
|||
|
|||
Conditional Formatting on non-numerical value
Thank you to both responses, very helpful and I've achieved exactly what I
needed now! Ben "Tom Wickerath" wrote: Hi Ben, Try the following in the Northwind.mdb sample database: To implement Conditional Formatting on text values: 1.) Open the Categories form in design view. Display the form's Properties dialog (View | Properties, or the F4 button). Select the CategoryName text box. You should see "Text Box: CategoryName" in the blue title bar of the Properties dialog. Click on Format | Conditional Formatting... 2.) Add the following two conditions: Condition 1 Field Value Is equal to "Beverages" Select a bold red font with a white background Condition 2 Field Value Is equal to "Condiments" Select a bold green font with a blue background To change the background color of the form, based on a value in the CategoryName field: 1.) With the word "Form" showing in the blue title bar of the Properties dialog, select the Format tab. Remove the (bitmap) from the Picture property. 2.) Copy and paste the following VBA code into the Form_Current procedure for this form. You can add additional Case statements, as desired: Option Compare Database Option Explicit Private Sub Form_Current() On Error GoTo ProcError Select Case Me.CategoryName Case "Beverages" Me.Detail.BackColor = 255 'Red Case "Condiments" Me.Detail.BackColor = 33023 'Orange Case Else Me.Detail.BackColor = 12632256 'Gray End Select ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in Form_Current event procedure..." Resume ExitProc End Sub Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Ben M Rowe" wrote: Hi there I apologise for my lack of understanding about Access 2003, I'm still learning and hoping you can help make me better! I have designed a form, and need to conditionally format one of the controls based on its own contents. I have managed to make it work when I am working with numerical vaules, but can't seem to get my head around making it work with text inputs. Effectively the form displays projects. Each project is taken from a record on one table. Against each project is one of four project administrators (the administrators are stored in a separate table on a one-to-many relationship). So, I am trying to get the form to change the colour of the administrator control depending on which administrator has been selected for the project. As a second request (which is not a priority by any means) is it possible to change the background colour of the form itself dependent on the contents of one of its controls? |
#5
|
|||
|
|||
Conditional Formatting on non-numerical value
Hi Ben,
You're welcome. You know, if you are going to use VBA code to change the backcolor of a particular form section (such as the Detail section), you might as well do the conditional formatting in the same procedure, rather than rely on the built-in conditional formatting. Doing so will keep all formatting operations in one place (easier to maintain), and you'll be able to expand beyond the limit in Access 2003 of three conditions. Something like this: Private Sub Form_Current() On Error GoTo ProcError Select Case Me.CategoryName Case "Beverages" Me.Detail.BackColor = 255 'Red With Me.CategoryName .ForeColor = 255 'Red .BackColor = 16777215 'White .FontBold = True End With Case "Condiments" Me.Detail.BackColor = 33023 'Orange With Me.CategoryName .ForeColor = 32768 'Green .BackColor = 16711680 'Blue .FontBold = True End With Case Else Me.Detail.BackColor = 12632256 'Gray With Me.CategoryName .ForeColor = 0 'Black .BackColor = 12632256 'Gray .FontBold = False End With End Select ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in Form_Current event procedure..." Resume ExitProc End Sub Notes: Remove the existing Conditional Formatting applied via the Format | Conditional Formattng... dialog. You can also reference pre-defined intrinsic constants for color (or define your own with constants). The following constants can be used anywhere in your code in place of the actual values: Constant Description vbBlack Black vbRed Red vbGreen Green vbYellow Yellow vbBlue Blue vbMagenta Magenta vbCyan Cyan vbWhite White Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Ben M Rowe" wrote: Thank you to both responses, very helpful and I've achieved exactly what I needed now! Ben |
Thread Tools | |
Display Modes | |
|
|