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.
Who is Participating?

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

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.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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,
ccapitalAuthor Commented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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,
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
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
    NextControl.BackColor = NextBackColor
End Sub

Open in new window


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:
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
Gustav BrockCIOCommented:
Ah, it should be:

Set NextControl = ThisControl

Open in new window

Also the other line.

ccapitalAuthor Commented:
Worked a charm. Many thanks to both you and crystal
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.