Shade coding provides worth to a kind because it simply directs the person’s eye to essential knowledge. This can be a highly effective approach that enables a person to rapidly consider a pages of knowledge for errors or warnings. The problem with Entry is the bounds it locations on the operate. Entry has a restrict of 4 colours you should use to focus on every subject . One other caveat when utilizing steady kinds conditional formatting, is that what you apply to 1 report applies to all information within the element part of the shape. Utilizing the situation code strategies described on this tutorial, there is no such thing as a restrict to the logic you’ll be able to apply to set the sphere situations to one of many 4 colour choices, however you’re nonetheless restricted to 4 colours on every subject.
Create a desk and identify it ColorCoding. Enter the next fields (with out the numbers)
1] CntrID as Autonumber, make it the first key subject
2] Cntname as textual content
3] WorkDesc as textual content — the info ought to both be “min” or ” maj” to work with the instance code
4] EMR as quantity, single –the knowledge must be any integer between 1 and 10 to work with this instance
5] aggdate as date/time
6] aggamnt as forex — the info must be {dollars} in tens of millions so 2 = two million in insurance coverage
7] wcompdate as date/time
8] wcompamnt as forex — the info must be {dollars} in tens of millions
9] concode as quantity, lengthy integer this subject might be calculated so go away it clean.
Enter some knowledge within the desk for testing.
Create a kind steady type kind utilizing the ColorCoding desk as the info supply. Add a kind header/footer.
Put a kind title within the kind header part of the shape (non-obligatory).
Use the opposite tab on the properties kind and provides every subject the identical identify because it’s management supply. So, identify the [EMR] subject “EMR” for instance. Then you definitely Can use the sphere names like variable names if you create VBA code.
Add all of the fields to the desk and align the in a single row with the sphere labels above every subject however within the kind header part of the shape.
This could go away one row of fields within the element part of the shape.
Put a button within the footer with the caption refresh.
In design mode in your kind, select Format / Conditional Formating.
Use the Mid command to test a personality within the situation code and set your situation color hex map. Take a look at the [EMR] subject. It’s the fourth subject from the left. The Mid command directs Entry to test the sphere concode within the fourth character place for a span of 1 character for the worth of three. Set the situations like this
Go away the default formatting set to white background and black font colour.
Expression is Mid([concode],4,1)=”3″ Decide a colour — I take advantage of Purple for 3 or a failing situation
Expression is Mid([concode],4,1)=”2″ Decide a color– I take advantage of yellow for two or a warning situation
Expression is Mid([concode],4,1)=”1″ Decide a colour — I take advantage of inexperienced for 1 or a passing situation
Repeat this for every subject you want to set situations on. Observe that every character of the situation code ranging from left to proper will symbolize a respective subject from left to proper. So, a 3 within the 2 place on the situation code subject will set the [CntName] subject to purple. See the sphere numbers above. Subsequent fields comply with the identical sequence.
Yet another instance for the [aggdate] subject, the fifth subject, the condtional format would appear to be:
Expression is Mid([concode],5,1)=”3″ Decide a colour.
The situation code subject, [concode], values are created with any logic that you simply wish to assign to it. On this instance I’m utilizing eight fields so the situation code is eight characters extensive and every character may very well be both a 0,1,2, or 3. So it might appear to be this 10112321. Now utilizing the mid command above, the fourth character is a 1 so the situation for the fourth subject,[ EMR], can be a 1 which situations to inexperienced. After getting the shape designed and examined, you might wish to return and set the [concode] subject to invisible. Your customers needn’t see that knowledge.
After you’ve got created the shape apply the next code. You possibly can lower and paste this proper into the shape with none typing for those who used the identical subject names described.
Lower right here ——–
Possibility Evaluate Database
Dim rec As DAO.Recordset
Dim db As Database
Public Perform SetConCode()
On Error GoTo erout
Dim xcode(10) As Integer, x As Integer, CCode As String, y As Integer ‘ xcode values 1 = inexperienced, 2 = yellow, 3 = purple, 0 =white
‘ Uncomment and use the following line when you have a whole lot of information and do not wish to wait on the display screen to replace
‘DoCmd.Echo False
‘Set the sphere counter to Zero
x = 0 ‘increment the sphere counter as you go
x = x + 1
‘Step 1
If IsNull(CntrID) Then
xcode(x) = 0
Else
xcode(x) = 1
Finish If
x = x + 1 ‘Step 2
If IsNull(Cntname) Then
xcode(x) = 0
Else
xcode(x) = 1
Finish If
x = x + 1 ‘Step 3
If IsNull(WorkDesc) Then
xcode(x) = 0
Else
xcode(x) = 1
Finish 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
Finish If
If InStr(4, concode, “3”) > 1 Then
xcode(2) = 3
Finish If
‘Construct conCode subject values from xcode values accumulating the worth of X from left to proper
y = 0
For y = 1 To eight
If y = 1 Then
CCode = xcode(y)
Else
CCode = CCode & xcode(y)
Finish If
Subsequent
concode = CCode
erout:
Debug.Print Err.Description
DoCmd.Echo True
Finish Perform
Non-public Sub addamnt_AfterUpdate() ‘Run the SetConCode operate after every subject is up to date
SetConCode
Finish Sub
Non-public Sub aggdate_AfterUpdate()
SetConCode
Finish Sub
Non-public Sub Cntname_AfterUpdate()
SetConCode
Finish Sub
Non-public Sub EMR_AfterUpdate()
SetConCode
Finish Sub
Non-public Sub Form_Open(Cancel As Integer)
‘Loop via all of the information setting the concode values when the shape is opened to test date values.
Dim x As Integer
x = 1
Set rec = Me.Recordset.Clone
rec.MoveLast
If rec.RecordCount = 0 Then
MsgBox “NO Information Discovered”
Finish If
rec.MoveFirst
DoCmd.GoToRecord acDataForm, “CondCodes”, acFirst
For x = 1 To rec.RecordCount – 1
If x = rec.RecordCount Then
GoTo getoutloop
Else
SetConCode
DoCmd.GoToRecord acDataForm, “CondCodes”, acNext
Finish If
Subsequent
getoutloop:
rec.Shut
Finish Sub
Non-public Sub set_Click()
‘Loop via all of the information setting the concode values
Dim x As Integer
x = 1
Set rec = Me.Recordset.Clone
rec.MoveLast
If rec.RecordCount = 0 Then
MsgBox “NO Information Discovered”
Finish If
rec.MoveFirst
DoCmd.GoToRecord acDataForm, “CondCodes”, acFirst
For x = 1 To rec.RecordCount – 1
If x = rec.RecordCount Then
GoTo getoutloop
Else
SetConCode
DoCmd.GoToRecord acDataForm, “CondCodes”, acNext
Finish If
Subsequent
‘DoCmd.Echo True ‘ flip echo again on for those who turned it off
getoutloop:
rec.Shut
Finish Sub
Non-public Sub wcompamnt_AfterUpdate()
SetConCode
Finish Sub
Non-public Sub wcompdate_AfterUpdate()
SetConCode
Finish Sub
Lower Right here ————–
You’ll need to set the VBA references to incorporate Microsoft DAO 3.6. That is completed on the toolbar when you’ve got the VBA editor open. Search for Instruments/References. When the reference window opens scroll right down to the Microsoft DAO 3.6 reference and test it. This instance was created with Entry 2002 however ought to work with Entry 2000 to Entry 2003. There could also be higher methods to perform the identical factor, however this works nicely for us. This could do it. Please be aware that this can be a highly effective and helpful tip that may assist you set your database functions aside and make your customers need extra. Make the most of it. Experiment with totally different logic statements in your SetConCode operate and see what you’ll be able to accomplish.