Change border colour

[ClowneUser]

Hi,

We have a database that records all the calibration equipment we have, and the dates that they need recalibrating, I've been trying to get it to change the border colour from green to red if the date has passed. I set the border colour on the form to be green, but then in vb set it to red if the date has passed current date.

Private Sub Combo2_AfterUpdate()
strElementName = Me!Combo2.Column(1)
strElementDescription = Me!Combo2.Column(2)
strElementSize = Me!Combo2.Column(3)
lngFrequency = Me!Combo2.Column(4)      'Frequency of Check
Text61 = Me!Combo2.Column(7)            'Next calibration
'lngRed = RGB(255, 0, 0)
'lngGreen = RGB(0, 255, 0)
If Text61 < Date Then
Box72.BorderColor = RGB(255, 0, 0)
'Else
'Box72.BorderColor = lngGreen
End If
Text92 = Me!Combo2.Column(8)            'Checked By
Text8 = Me!Combo2.Column(10)            'Certificate Number

strElementName.Visible = True
strElementDescription.Visible = True
strElementSize.Visible = True
lngFrequency.Visible = True
Text8.Visible = True
Text92.Visible = True
Box72.Visible = True
Box35.Visible = True
Label31.Visible = True
Label34.Visible = True
Label37.Visible = True
Label38.Visible = True
Label69.Visible = True
Label70.Visible = True
Label71.Visible = True
Text61.Visible = True

I've tried a few different things hence the remarked out code, but nothing I've tried seems to work.

The database is Access 2000

Any ideas what I need to do to achieve this?

Thanks,

Mick.

 

[dhookom]

Check out faq705-7148 and tell us what you are finding.

Duane
Hook'D on Access
MS Access MVP

 

[ClowneUser]

Thanks Duane,

Not sure if I'm doing something wrong or not, but when I try to run the debugger, it prompts me for a Macro, but there are no macro's in the database.

If I add a breakpoint and I run the form nothing happens.

Am I missing something?

Mick.

 

[dhookom]

The code you wrote will only run when the combo2 (couldn't you find a better name) is updated.

I think I would try to use conditional formatting rather than code. I believe A2000 had this functionality.

Duane
Hook'D on Access
MS Access MVP

 

[ClowneUser]

Hi Duane,

Yes I agree the naming is not the best, but it was a database written by someone else that has since left the company...

I tried conditional formatting but it wont work on boxes or at least its grayed out when highlighting the box ( as its the border of the box I want to change the colour of )

I've looked over this code numerous times and I can't see anything wrong with it, it should work, it just appears that the date in Text61 is being ignored, but I can't figure out why. If I change the code to be:

Private Sub Combo2_AfterUpdate()
strElementName = Me!Combo2.Column(1)
strElementDescription = Me!Combo2.Column(2)
strElementSize = Me!Combo2.Column(3)
lngFrequency = Me!Combo2.Column(4)      'Frequency of Check
Text61 = Me!Combo2.Column(7)            'Next calibration
Text92 = Me!Combo2.Column(8)            'Checked By
Text8 = Me!Combo2.Column(10)            'Certificate Number


If Text61 <> Date Then


Box72.BorderColor = vbRed
Else
Box72.BorderColor = vbGreen
End If


strElementName.Visible = True
strElementDescription.Visible = True
strElementSize.Visible = True
lngFrequency.Visible = True
Text8.Visible = True
Text92.Visible = True
Box72.Visible = True
Box35.Visible = True
Label31.Visible = True
Label34.Visible = True
Label37.Visible = True
Label38.Visible = True
Label69.Visible = True
Label70.Visible = True
Label71.Visible = True
Text61.Visible = True




End Sub

So if it doesn't equal Date then the border turns RED.

Is there any way to find out what it thinks text61 is? on the form it does show the date correctly. Could it be incorrect fomatting of the date somehow?

Any ideas would be appreciated as I'm pulling my hair out with this and I haven't got much left...

Thanks,

Mick.

 

[ClowneUser]

Just added Debug.Print for both Date and Text61 and both show the correct dates, so i'm officially puzzled now as it really should work as far as I can see.

Again any idea's why it's not?

Thanks,

Mick.

 

[randy700]

Is the data in Text61 a date field or is it a string field that LOOKS LIKE a date?

Randy

 

[dhookom]

Have you attempted to compile your code?

What are these "str..." things? Are they control names or memory variables? If they are memory variables you can't set them to visible. Generally accepted naming convention suggests they are string variables.

Do you understand that combo box columns are numbered beginning with 0?

Again, learn how to debug your code by setting breakpoints.

Private Sub Combo2_AfterUpdate()
  strElementName = Me!Combo2.Column(1)
  strElementDescription = Me!Combo2.Column(2)
  strElementSize = Me!Combo2.Column(3)
  lngFrequency = Me!Combo2.Column(4)         [green]'Frequency of Check[/green]
  Me.Text61 = Me!Combo2.Column(7)            [green]'Next calibration[/green]
  Me.Text92 = Me!Combo2.Column(8)            [green]'Checked By[/green]
  Me.Text8 = Me!Combo2.Column(10)            [green]'Certificate Number[/green]

  MsgBox "Text61: " & Me.Text61
  If Me.Text61 <> Date Then
    MsgBox "Not equal"
    Me.Box72.BorderColor = vbRed
   Else
    MsgBox "Equal"
    Me.Box72.BorderColor = vbGreen
  End If

  strElementName.Visible = True
  strElementDescription.Visible = True
  strElementSize.Visible = True
  lngFrequency.Visible = True
  Me.Text8.Visible = True
  Me.Text92.Visible = True
  Me.Box72.Visible = True
  Me.Box35.Visible = True
  Me.Label31.Visible = True
  Me.Label34.Visible = True
  Me.Label37.Visible = True
  Me.Label38.Visible = True
  Me.Label69.Visible = True
  Me.Label70.Visible = True
  Me.Label71.Visible = True
  Me.Text61.Visible = True

End Sub

Duane
Hook'D on Access
MS Access MVP