Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

Apply conditional formatting using code

Conditional formatting is a great feature introduced in Access 2000, it allows you to apply different formats to records on a forms based on values and formulas you setup. In this example I’m going to build on a article by Microsoft you can find here.

Colors are better for some…

My client wanted to use colors to quickly see the status of each item, (I would have normally used a drop down box for easy sorting on status, both colors and drop downs have their benefits and drawbacks). I added buttons to each record so that the customer can click on each to change the status as shown below:

Each button assigns a numeric value to the GraphicStatusID field and saves the record so that the user can immediately see the change on the form. Here’s the button code:

Private Sub cmdApproved_Click()
Me.GraphicStatusID = conApprvdGreen
Me.DateGraphicApproved = Date
DoCmd.RunCommand acCmdSaveRecord
End Sub

Here’s how it looks:

Whole Row Formatting

(Notice there are five buttons above, one color per button. I overcame that obstacle by not using the whole row, read more below.)

Use the Controls Collection to apply formatting

There are many reasons to use code instead of Access’ interface to add formatting conditions, mainly it’s a lot easier to apply the formatting when you have to do many controls on your form. Having it all in one place will make it easy to update the formulas and formating too.

The first step is to use the Tag property to identify which controls on your form will be processed by your code, in this case I used “Conditional”. I then used the code posted here and called the ApplyCondFormatting subroutine on the form’s Load event.

Tag Property

The form has a Controls collection that you can iterate using the For Each method and a control variable, (ctl). Looping through all of the controls allows you to check the Tag property and if there is a match, apply the formating code. The code works if you have only three conditions.

 

But what if you need to apply more than three formats to a row?
There is no way around the three formatting limit so I decided to take a hybrid approach: Use the first two columns to indicate a color status instead of the entire row. The first column, Vendor, has  three and the PO column has two more for a total of five status colors. When the user clicks on any of the status buttons the color will now move between the first and second column as shown below:

You can see the code for staggered formating here.

This article illustrates the power of the control collection and applying conditional formatting in your code.

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores