Programming codex

MS Access Tutorial – Advanced Color Coding for Your Continuous Forms

MS Access Tutorial - Advanced Color Coding for Your Continuous Forms

[ad_1]

Color coding adds value to a form as it easily directs the user’s eye to critical data. This is a powerful technique that allows a user to quickly evaluate a page of data for errors or warnings. The difficulty with Access is the limits it places on the function. Access has a limit of four colors you can use to highlight each field. Another caveat when using continuous forms conditional formatting is that what you apply to one record applies to all records in the detail section of the form. Using the condition code methods described in this tutorial, there is no limit to the logic you can apply to set the field conditions to one of the four-color options, but you are still limited to four colors on each field.

Create a table and name it ColorCoding. Enter the following fields (without the numbers)

1] CntrID as Autonumber, make it the primary key field

2] Cntname as text

3] WorkDesc as text — the data should either be “min” or ” Maj” to work with the example code

4] EMR as a number, single –the data should be any integer between 1 and 10 to work with this example

5] aggregate as date/time

6] aggamnt as currency — the data should be dollars in millions so 2 = two million in insurance

7] wcompdate as date/time

8] company as currency — the data should be dollars in millions

9] conclude as the number, long integer this field will be calculated so leave it blank.

Enter some data in the table for testing.

Create a form of continuous style form using the ColorCoding table as the data source. Add a form header/footer.

Put a form title in the form header section of the form (optional).

Use the other tab on the properties form and give each field the same name as it’s control source. So, name the [EMR] field “EMR” for example. Then you Can use the field names like variable names when you create VBA code.

Add all the fields to the table and align the in one row with the field labels above each field but in the form header section of the form.

This should leave one row of fields in the detail section of the form.

Put a button in the footer with the caption refresh.

In design mode on your form, choose Format / Conditional Formating.

Use the Mid command to check a character in the condition code and set your condition colors. Look at the [EMR] field. It is the fourth field from the left. The Mid command directs Access to check the field conclude in the fourth character place for a span of 1 character for the value of 3. Set the conditions like this

Leave the default formatting set to white background and black font color.

Expression is Mid([concode],4,1)=”3″ Pick a color — I use Red for 3 or a failing condition

Expression is Mid([concode],4,1)=”2″ Pick a color– I use yellow for 2 or a warning condition

Expression is Mid([concode],4,1)=”1″ Pick a color — I use green for 1 or a passing condition

Repeat this for each field you wish to set conditions on. Note that each character of the condition code starting from left to right will represent a respective field from left to right. So, a 3 in the 2 places on the condition code field will set the [CntName] field to red. See the field numbers above. Subsequent fields follow the same sequence.

One more example for the [aggdate] field, the fifth field, the condtional format would look like:

Expression is Mid([concode],5,1)=”3″ Pick a color.

The condition code field, [concode], values are created with any logic that you want to assign to it. In this example, I am using eight fields so the condition code is eight characters wide and each character could be either a 0,1,2 or 3. So it may look like this 10112321. Now using the mid command above, the fourth character is a 1 so the condition for the fourth field,[ EMR], would be a 1 which conditions to green. Once you have the form designed and tested, you may want to go back and set the [concode] field to invisible. Your users don’t need to see that data.

After you have created the form apply the following code. You can cut and paste this right into the form without any typing if you used the same field names described.

Cut here ——–

Option Compare Database

Dim rec As DAO.Recordset

Dim db As Database

Public Function SetConCode()

On Error GoTo erout

Dim xcode(10) As Integer, x As Integer, CCode As String, y As Integer ‘ xcode values 1 = green, 2 = yellow, 3 = red, 0 =white

‘ Uncomment and use the next line if you have hundreds of records and don’t want to wait on the screen to update

‘DoCmd.Echo False

‘Set the field counter to Zero

x = 0 ‘increment the field counter as you go

x = x + 1

‘Step 1

If IsNull(CntrID) Then

xcode(x) = 0

Else

xcode(x) = 1

End If

x = x + 1 ‘Step 2

If IsNull(Cntname) Then

xcode(x) = 0

Else

xcode(x) = 1

End If

x = x + 1 ‘Step 3

If IsNull(WorkDesc) Then

xcode(x) = 0

Else

xcode(x) = 1

End If

x = x + 1 ‘Step 4

If IsNull(EMR) Then

xcode(x) = 0

ElseIf

EMR 3 And EMR = Now() And aggdate = 1 And aggamnt = 3 And aggamnt = Now() And wcompdate 3 And wcompamnt 1 Then

xcode(2) = 2

End If

If InStr(4, concode, “3”) > 1 Then

xcode(2) = 3

End If

‘Build conCode field values from xcode values collecting the value of X from left to right

y = 0

For y = 1 To 8

If y = 1 Then

CCode = xcode(y)

Else

CCode = CCode & xcode(y)

End If

Next

concode = CCode

erout:

Debug.Print Err.Description

DoCmd.Echo True

End Function

Private Sub addamnt_AfterUpdate() ‘Run the SetConCode function after each field is updated

SetConCode

End Sub

Private Sub aggdate_AfterUpdate()

SetConCode

End Sub

Private Sub Cntname_AfterUpdate()

SetConCode

End Sub

Private Sub EMR_AfterUpdate()

SetConCode

End Sub

Private Sub Form_Open(Cancel As Integer)

‘Loop through all the records setting the concode values when the form is opened to check date values.

Dim x As Integer

x = 1

Set rec = Me.Recordset.Clone

rec.MoveLast

If rec.RecordCount = 0 Then

MsgBox “NO Records Found”

End If

rec.MoveFirst

DoCmd.GoToRecord a data form, “CondCodes”, acFirst

For x = 1 To rec.RecordCount – 1

If x = rec.RecordCount Then

GoTo get outlook

Else

SetConCode

DoCmd.GoToRecord a data form, “CondCodes”, acNext

End If

Next,

get an outlook:

rec.Close

End Sub

Private Sub set_Click()

‘Loop through all the records setting the conclude values

Dim x As Integer

x = 1

Set rec = Me.Recordset.Clone

rec.MoveLast

If rec.RecordCount = 0 Then

MsgBox “NO Records Found”

End If

rec.MoveFirst

DoCmd.GoToRecord a data form, “CondCodes”, acFirst

For x = 1 To rec.RecordCount – 1

If x = rec.RecordCount Then

GoTo get outlook

Else

SetConCode

DoCmd.GoToRecord a data form, “CondCodes”, acNext

End If

Next

‘DoCmd.Echo True ‘ turn echo back on if you turned it off

get outlook:

rec.Close

End Sub

Private Sub wcompamnt_AfterUpdate()

SetConCode

End Sub

Private Sub wcompdate_AfterUpdate()

SetConCode

End Sub

Cut Here ————–

You will need to set the VBA references to include Microsoft DAO 3.6. This is done on the toolbar when you have the VBA editor open. Look for Tools/References. When the reference window opens scroll down to the Microsoft DAO 3.6 reference and checks it. This example was created with Access 2002 but should work with Access 2000 to Access 2003. There may be better ways to accomplish the same thing, but this works well for us. This should do it. Please note that this is a powerful and valuable tip that can help you set your database applications apart and make your users want more. Take advantage of it. Experiment with different logic statements in your SetConCode function and see what you can accomplish.

[ad_2]

Source by Jon E. Watson

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on whatsapp

Leave a Reply

Your email address will not be published. Required fields are marked *

Search:

Topics

Recent Posts