ccapital
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Ah, it should be:
/gustav
Set NextControl = ThisControl/gustav
ASKER
Worked a charm. Many thanks to both you and crystal
you're welcome ~ happy to help
ASKER
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.