February 26, 2015 at 5:15 pm
I have five date fields in Access 2010 for exams or other events that are done, and a view is calculating a future date when they need to be done again. The code is identical for all five forms with the exception of the text box name. So in this case, IEPConference was when the last meeting was done, IEPConferenceDue is calculated as three years from the previous. I want a warning if it's due within 60 days with a yellow background, and the warning turns red when it's overdue.
This is the code that I'm dealing with:
IEPDue = DateDiff("d", Now(), Nz(IEPConferenceDue, Now()))
FuncDue = DateDiff("d", Now(), Nz(FunctionalVisionEvalDue, Now()))
If IEPDue < 60 And Len(IEPConferenceDue) > 0 Then
Me!txtIEPOverdue.Visible = True
Me!txtIEPOverdue.Enabled = True
Me!txtIEPOverdue.Locked = False
Me!txtIEPOverdue.SetFocus
Select Case IEPDue
Case Is < 0
tmsg = "OVERDUE " & Abs(IEPDue) & " DAYS"
Me!txtIEPOverdue.ForeColor = vbWhite
Me!txtIEPOverdue.BackColor = vbRed
Case 1 To 60
tmsg = "Due in " & Abs(IEPDue) & " days"
Me!txtIEPOverdue.ForeColor = vbBlack
Me!txtIEPOverdue.BackColor = vbYellow
End Select
Me!txtIEPOverdue.Text = tmsg
Me!txtIEPOverdue.Enabled = False
Me!txtIEPOverdue.Locked = True
End If
So I've got five datediff calculations and five code blocks that are pretty much identical. I'd like to do a function that I pass the datediff calc, the field name to check if there's a date in it, and the name of the text box that I'll display a warning and change the color. If everything is not yet due, the box is invisible.
I remember a previous programming language that I dealt with that I could do this, but I have a feeling that I just can't get there from here in Access/VBA. If I could do it, it'd save a lot of lines of code and would simplify maintenance, but if not, that's the way it is. My VBA Fu is not strong, but I'm well above a white belt.
Thanks!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 2, 2015 at 3:14 pm
It should be possible to define a function in VBA that is generic in nature, and returns the color sets for each control, and could be called by logic on each form. But I would suggest you consider using conditional formatting on the text boxes. I think you will find that feature of Access 2010 a very powerful tool for displaying data.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply