Solved

MS Access create time picker combobox on form

Posted on 2016-10-03
12
34 Views
Last Modified: 2016-10-04
Hello Experts,

So I have search forever on the net for a simply solution to create a time picker on an Access form.
 screenshotscreenshot2I just need to have a control that allows the user to enter a time in 15min. increments and formats it to a short time so that I can write it to the table using VBA. The time control is unbound.

I have looked at more involved routines like Crystal's f_PopupCalendar but I all I need is a simple combobox control I think that will work. I just don't know how to translate the values into something I can write to the table.

Any help would be most a appreciated.
0
Comment
Question by:shogun5
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41827633
One option could be to use the time entry method described here:

Entering 24-hour time with input mask and full validation in Microsoft Access

The time can be rounded using a function like this:
Public Function RoundTime( _
  ByVal datDate As Date) _
  As Date

'Const cintMult As Integer = 24 '1 hour round
Const cintMult As Integer = 96 '15 minute round
'Const cintMult As Integer = 144 '10 minute round
'Const cintMult As Integer = 288 '5 minute round
  
  RoundTime = CVDate(Int(datDate * cintMult + 0.5) / cintMult)

End Function

Open in new window


Now, save this value. Forget everything about "Short Time" - formats are for display only./gustav
0
 
LVL 22

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 100 total points
ID: 41827645
Just a point of start
You can add an UpDown control using Microsoft UpDown Control ActiveX (from Activex Objects list)

Then in UpClick and DownClick event (not available in the Event tabs but you can select them in the Procedures list in the Module Window) you can write your desired code.
For example, with an editbox called let's say time_Edit and an UpDown object you can do something like this:
Private Sub UpDown_DownClick()
 If IsDate(Me![time_edit].Value) = True Then
      timeOriginal = CDate(Me![time_edit].Value)
      timeNew = DateAdd("n", -1, timeOriginal)
      Me![time_edit].Value = timeNew
   End If
End Sub

Private Sub UpDown_UpClick()
  If IsDate(Me![time_edit].Value) = True Then
      timeOriginal = CDate(Me![time_edit].Value)
      timeNew = DateAdd("n", 1, timeOriginal)
      Me![time_edit].Value = timeNew
   End If
End Sub

Open in new window

You should have still to adjust formats and also check if you're going up or down by a day (or more or less than 15 mins, setting some up and down time limits), but this should be a good point of start for you, I hope.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41827757
Microsoft provides a free ActiveX : Date and Time Picker Control 6.0 (SP6)
Here it is page from MS
0
 

Author Comment

by:shogun5
ID: 41827763
Ferruccio Accalai,

any chance you could put this is a dummy form so I can see how to do this?
0
 

Author Comment

by:shogun5
ID: 41827769
John T.

My is concern with this is when i distibute the application I can't confirm that the user will install the MSCOMCT2.OCX  file in the user's Microsoft Windows System or System32 directory so user experience will not be good if they do not have the .ocx file.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41827774
Well you can make a script that makes sure you have MSCOMCT2.OCX and if not to copy it...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41827789
First open Activex Object menu and check for Microsoft UpDown Control (the higher version you have)
Choose UpDown ControlThen in your module windown select the downclick and upclick eventsChoose the eventsAnd finally add your code
Write your code
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 41827804
I have an all VBA solution posted on my website.  You can either use that, or take a look at what I've done and implement something similar in your application.

HTH
Dale
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 41827826
Using ActiveX controls in Access is generally a bad idea if you plan on distributing this app (and it sounds as if you do). Starting with 2007 there are no Microsoft ActiveX controls that are certified to work with Access, and prior to that there were a very select group (and the MSCOMCT2.OCX was a specific control, not the one that is linked to in this thread). ActiveX controls may work on YOUR machine, but there is no guarantee they will work on the end user. And they may work today, but they may fail tomorrow, or next week, or next month - MSFT is under no obligation to ensure their updates do not break your application if an updated version of the control is deployed, since they do not guarantee that control is compliant with Access.

Also, you should NEVER simply copy files to an end user machine. Doing so could cause all sorts of trouble, most especially if that machine is dependent on a specific version of a control. If you need to deploy items to your end users, do so the correct way, using an installer that can determine if the item is needed, install it correctly, and also install any other dependent items.

Also, the MSCOMCT2.OCX can only be legally distributed if you own an environment which allows that distribution - and that does NOT include Office. Visual Basic 5/6 allowed this, as does the newer Visual Studio builds, but if all you have is Access/Office then you do not have the legal right to deploy that file to your end users. I realize this is a moot point for some, since MSCOMCT2.OCX is more-or-less deprecated, but the fact remains that you may not have the right to deploy that file.

There are some 3rd party vendors who provide controls, but only a bare handful. ONe of those is FMS, which offers Total Access Components (http://www.fmsinc.com/MicrosoftAccess/Controls.html), which includes a Date Time Picker.

Tony Towes has a list of quite a few controls that are built entirely with native Access items:

http://www.granite.ab.ca/access/calendars.htm

You would be far, far better off using one of those rather than an ActiveX control that was intended (and created) for use in the Visual Basic environment.
1
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41827984
This should work for you ... I had to do something similar for a Time-Clock application that I created a while back for a Dispatch operation.  Since the Technicians and Delivery Drivers could work past midnight ... we tracked their Clock-In, Lunch-Out, Lunch-In and Clock-Out times in full Date & Time format.  While this worked, 90% of their time-clock entries were on the same work day making it redundant to have to enter the Date 4 additional times.  Therefore, I created an separate time only form activated by a double click of the current record that allowed the Dispatcher to enter the time portion only if the employee clocked in and clocked out on the same day.

Timeclock1

That's kind of what you are doing.  Just create a ComboBox that has your times from 1:00 to 23:45 that the user can select from.  

Use a Input Mask like ...90:00;0_

I use this code on the OnClick Event of the OK button to populate the bound controls on the main timeclock sub form to allow the user to finish editing the current record and eventually save it.

Forms!dataentry_timeclock![timeclock subform2].Form!CLOCK_IN = DateValue(Me.Text10) & " " & TimeValue(Me.Text0)
        Forms!dataentry_timeclock![timeclock subform2].Form!LUNCH_OUT = DateValue(Me.Text10) & " " & TimeValue(Me.Text2)
        Forms!dataentry_timeclock![timeclock subform2].Form!LUNCH_IN = DateValue(Me.Text10) & " " & TimeValue(Me.Text3)
        Forms!dataentry_timeclock![timeclock subform2].Form!CLOCK_OUT = DateValue(Me.Text10) & " " & TimeValue(Me.Text4)

Open in new window

0
 

Author Closing Comment

by:shogun5
ID: 41828040
Dale, et. al, thanks a bunch for your inputs.

Dale, I used your solution as it was exactly what I needed and worked the best in my environment. The arguments that allowed by to increment as needed, e.g. 5 minutes, 15 minutes, and 30 minutes respectively was perfect for what I needed. Thanks again!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41828045
Glad I could help.
1

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now