Highlighting a datetime field on an Access 2016 form when it is close to current time.

I have 6-7 fields displayed on an Access 2016 form. Each field contains a datetime value (only a time) and I would like to highlight the field that is nearest to the present time (BUT ahead of the current time). i.e. the next time to occur.

For example:

The fields are 02:00 06:00 10:00 14:00 18:00 22:00

The time is now 9:35AM and so the field which will be highlighted will be the 10:00 field. It is the next time to occur.

When the current time moves to 10:01AM then the 10:00 field would no longer be highlighted and the 14:00 one would be.

'Highlighting' will be by meaning of changing the text box's Back Color property.

ccapitalAsked: 2017-12-24

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 24

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented: 2017-12-24

if the times are in order as your example, and they always have a value, you can add this calculated field to the Record Source:

NextTime: IIf([MyTime1]>Time(),[MyTime1],IIf([MyTime2]>Time(),[MyTime2],IIf([MyTime3]>Time(),[MyTime3],IIf([MyTime4]>Time(),[MyTime4],IIf([MyTime5]>Time(),[MyTime5],IIf([MyTime6]>Time(),[MyTime6],Null))))))

If there may not be values for all the times, or they will not be in order, then you will need to use a custom function to return the next time.

Then you can use conditional formatting for each of the controls with times. If the time is equal to NextTime, then conditional formatting is applied.

I must say, however, that this seems to be poorly structured data. Instead of 6 fields for Time in one record, there should be 6 records.

have an awesome day,
crystal

ccapitalAuthor Commented: 2017-12-24

Many thanks for the input. Rather than highlight an existing field this gave me a new field which shows me the next time but that is absolutely fine and works A OK.

I totally understand that the structure is not ideal but this situation requires that for each record there are 6 distinct times in a day so I have them as Time1, Time2 etc. Also, there are only 10 records so the performance hit is minimal.

Again, thanks for the input.

LVL 24

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented: 2017-12-24

you're welcome

you can make NextTime hidden on the form and use a formula to compare for the conditional formatting of each time control if you want to highlight in-place. Performance will be better, though, if you like the NextTime displayed separately.

have an awesome day,
crystal

LVL 56

Gustav BrockCIOCommented: 2017-12-25

You can call a function like this in the form's OnActivate or OnCurrent event:

Private Sub Form_Current()

    Const NormalBackColor   As Long = vbWhite
    Const NextBackColor     As Long = vbYellow

    Dim ThisControl As Control
    Dim NextControl As Control
    
    Dim ControlName As String
    Dim Minutes     As Long
    Dim CurrentTime As Date
    
    CurrentTime = Time
        
    For Each ThisControl In Me.Controls
        Select Case ThisControl.Name
            Case "Textbox1", "Textbox2"   ' Adjust/expand to hold the list of names of your textboxes to check.
                ThisControl.BackColor = NormalBackColor
                Minutes = DateDiff("n", CurrentTime, ThisControl.Value)
                If Minutes > 0 Then
                    If NextControl Is Nothing Then
                        NextControl = ThisControl
                    ElseIf Minutes < DateDiff("n", CurrentTime, NextControl.Value) Then
                        NextControl = ThisControl
                    End If
                End If
        End Select
    Next
    
    NextControl.BackColor = NextBackColor
    
End Sub

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

ccapitalAuthor Commented: 2017-12-26

Gustav, thanks. I brought this code and and edited and it runs fine until it gets to the text box to be highlighted and the nit raises an error.

Run-time error '91'
Object variable or With block variable not set

The line that has the issue is Next Control = ThisControl

At that point using the immediate window I can see that Next Control is Nothing

LVL 56

Gustav BrockCIOCommented: 2017-12-26

Ah, it should be: