Full Version: conditional format or code
UtterAccess Forums > Microsoft® Access > Access Forms
board
I am trying without success to format the control [settime] to allow for all 3 of the following conditions, there could be more so maybe code would be better, I am trying to colour text Red and Bold, background any other colour really for test purposes if the settime is not withing the specification.
[product]="TMF" And [settime]<70 Or [settime]>90 Or [product]="TBF" And [settime]<70 And [settime]>90
[product]="TBC" And [settime]<180 Or [settime]>360 Or [product]="TPB" And [settime]<180 Or [settime]>360
[product]="TTC" And [settime]<120 Or [settime]>240 Or [product]="THW" And [settime]<120 Or [settime]>240
Any help would be appreciated.
Stuart
Alan_G
Hi
If you're going to be using more than 3 conditions then VBA is the answer so it's probably worth going down that route to start with wink.gif
board
how would this translate into code
tuart
Alan_G
Assuming you have controls named product and settime on your form and the one called product is the one you want to format, if you put a couple of UDF's (user defined functions) in your forms module something along the lines of

CODE
Private Sub sCheckFormat(strProduct As String, intTime As Integer)
If Nz(strProduct,"") = "" Or Nz(intTime,"") = "" Then Exit Sub
If (strProduct ="TMF" And (intTime < 70 Or intTime > 90)) Or _
& (strProduct ="TBF" And (intTime < 70 And intTime > 90) Then
    sSetFormat
ElseIf strProduct = ("TBC" And (intTime < 180 Or intTime > 360)) Or _
& (strProduct = "TPB" And (intTime < 180 Or intTime > 360)) Then
    sSetFormat
ElseIf (strProduct = "TTC" And (intTime < 120 Or intTime > 240)) Or _
& (strProduct = "THW" And (intTime < 120 Or  intTime > 240)) Then
    sSetFormat
Else
    Me.product..ForeColor = vbBlack
    Me.product.BackColor = vbWhite
    Me.product.FontWeight = 400
End If
End Sub
'-------------------------------------------------
Private Sub sSetFormat()
Me.product..ForeColor = vbRed
Me.product.BackColor = vbBlue
Me.product.FontWeight = 700
End Sub


Call them from the After Update events of both product and settime controls with

sCheckFormat Me.product, me.settime

Note ***untested air code***
board
Many thanks I will give this a go
tuart
board
Finished with this
Private Sub sCheckFormat(strproduct As String, intsettime As Integer)
If Nz(strproduct, "") = "" Or Nz(intsettime, "") = "" Then Exit Sub
If (strproduct = "TMF" And (intsettime < 70 Or intsettime > 90)) Or _
(strproduct = "TBF" And (intsettime < 70 And intsettime > 90)) Then
sSetFormat
ElseIf (strproduct = "TBC" And (intsettime < 180 Or intsettime > 360)) Or _
(strproduct = "TPB" And (intsettime < 180 Or intsettime > 360)) Then
sSetFormat
ElseIf (strproduct = "TTC" And (intsettime < 120 Or intsettime > 240)) Or _
(strproduct = "THW" And (intsettime < 120 Or intsettime > 240)) Then
sSetFormat
Else
Me.settime.ForeColor = vbBlack
Me.settime.BackColor = vbWhite
Me.settime.FontWeight = 400
End If
End Sub
Private Sub sSetFormat()
Me.settime.ForeColor = vbRed
Me.settime.BackColor = vbBlue
Me.settime.FontWeight = 700
End Sub
thanks
Alan_G
Glad you got it working wink.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.