Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Time Pieces (date/time Pickers; Calendar Scheduler N' Stuff) 3

Posted by: CyberCow Nov 5 2009, 02:42 PM

Several good folks here at UtterAccess have contributed to this demo project, (see the properties and code comments). This is a compilation of a date picker that will pop-up next to the control that called it; a time picker that will also pop-up near the control that called it; two different time range selectors; a date difference calculator that displays a date/time difference in days|hours|minutes; a calendar schedule; an analog clock with swappable clock faces and group of new icon images in a form with a few graphical elelments for nifty form design.
NOW UPDATED WITH A CALENDAR REPORT ! Thanks to Dennis (doctor9) here at UtterAccess, this demo now has a report modeled after the large calendar form. Contributors also include, (nut not limited to datAdrenaline, Bob Raskew and Stephen Yale)
The "Date as Words" (also in the Code Archive) has been reworked to render Time as well. Several new features in the modGlobalVars module.
This is compiled and is presented with Access 97, 2K & 2K7 formats. (All tested and working - 2K will work in 2K3 and the 2K7 will work in 2K10.) Hopefully you will find this useful. ( 1.47MB ): 1475
(download number before update was 1,751)
Update Nov 28, 2012 - cleaned-up & added some code to include the latest DateTime Functions module - by Cybercow
Be sure to check out the posts below for other features, corrections and modifications.

And as always, ensure any date code matches the syntax for your specific world location.

Posted by: vaudousi Apr 3 2012, 03:32 PM

Mark, I probably use the program badly. I tried with Windows XP and W7 32 bits. No joy !
The program opens frmDemo. The top left button is labeled Date/Time Picker. Clicking on it reveals 3 fields : Date/Time 1, Date/Time 2 and Get Diff.
Double clicking Date/Time 1 or 2 field raises an error (translated from french) :
Microsoft Visual Basic
Runtime error "2465"
Time Pieces can not find the field "cmd38" to which reference is made in your expression.
End Debug
Clicking Debug shows this line in Yellow
Me("cmd" & intCount).Caption = intCount - intCounter + 1
I have not made ​​any changes to the application.
Mark, the problem was (is) that names of english days (Sun, Mon, etc.) must be changed to french names.
Something weird : Dim must be followed by a dot (Dim.) not the other names.
Any comments ?

Posted by: CyberCow Apr 3 2012, 06:29 PM

Mon français est horrible et je dois utiliser le traducteur de Google pour obtenir ce n'importe où près correcte.
in English: "My French is horrible and I have to use Google Translator to get this anywhere near correct.")
Having zero experience in Office products with languages other than English, I'm afraid you're on your own, unless another French-speaking VBA expert happens by this thread and volunteers.
Otherwise, you could make all the changes yourself fairly quickly . . . .
Change all the weekday names to the French equivalent, then in the VBA editor, you could use the "Find" tool to replace all instances of the English weekday names to French - AND - do "Replace" "Dim " with "Dim."
What I don't know is if the French version will require a space after the "Dim."
Make a back-up and take a crack at it. If it gets broken, delete the broken one, make another copy of the back-up and try again.
That's the best I have right now.
If you come up with working re-work in French, please let us know. I'm always up for learning more about Access.

Posted by: doctor9 Apr 4 2012, 03:11 PM

took a look at all of the VBA in Timepieces, and re-wrote every section that depends on a hardcoded weekday name or month name. (Most of it was involved with the three pop-up Date Picker form variations.)
I was able to replace the hardcoded weekdays with references to a clever function I found in this previous thread. I also replaced a couple of references to "December" and "January" to function calls that pull the month name from a given date. This should, I hope, mean that people can now use this database in languages beyond English, or least make adapting it to another language much easier.
Just to bring things up to date, I've also moved the demo data associated with the big calendar forms up to April and May of 2012.
Feell free to download this update and see if I've missed something.
EDIT: Fixed a minor mistake in my VBA update, and added the multi-language feature to the top of the BIG calendar form. ( 478.98K ): 511

Posted by: vaudousi Apr 4 2012, 03:25 PM

Congratulations and thank you Dennis. The little calendars now work perfectly.
would not look like a troublemaker ... But is it easy to modify the routines to allow the choice of the first day of the week? Here in Europe, the first day is Monday.
Finally, there are errors in frmCalendar_Daily and frmAppointments. I assume you're busy with other more important things. I will try to identify the problem.

Posted by: doctor9 Apr 4 2012, 04:05 PM

Hmm... Tedious, but not necessarily difficult. I think you'd just have to move the form controls manually. Just make sure to make a backup of the form first, just in case. < Make the form taller, to accomodate a new row of days. Shift all of the days down a "row". Then, move the first sunday up to the last spot of the now-blank first row. Slide boxes 2-7 to the left. Repeat. I'll see if I can come up with something more elegant in the meantime.
If you can isolate a problem, you might want to start a NEW thread in one of the other forums to describe it, rather than replying directly to this thread. You can post a link to this thread as a reference, if necessary. Just copy the URL from your browser's address box, and use the green arrow button when composing your new message to paste the URL into your message. Or just say, "this is in reference to the 'Time Pieces (date/time Pickers; Calendar Scheduler N' Stuff) 3' thread in the Code Archive forum". < Either way, a new message thread will draw more attention from more volunteers who are looking for "new" requests for assistance.

Posted by: doctor9 Apr 4 2012, 04:58 PM

Here's my promised "more elegant" solution for changing the big calendar to be "Monday First":
1. Shuffle the labels that display the names of the weekdays around so lblWeekday2 is first, and lblWeekday1 is last, right after lblWeekday6.
2. Find this bit of code in the SetDates subroutine:

'   Get first day of the month.
    intFirstDay = Weekday(DateSerial(intPubMyYear, intPubMonth, 1))

3. Add the following code just below it:

'   First Day is MONDAY
    intFirstDay = intFirstDay - 1
    If intFirstDay = 0 Then intFirstDay = 7

That should do it, I think.
Similarly, to alter one of the little DatePicker forms, make these changes:
1. Shuffle the labels that display the names of the weekdays around so lblWeekday2 is first, and lblWeekday1 is last, right after lblWeekday6.
2. Find this bit of code in the OnCurrent subroutine:
    'Set initial counter depending on day of week the corresponding month starts on
    For intCounter = 1 To 7
        If strDay = WeekdayName(intCounter, True) Then
            Exit For
        End If
    Next intCounter
    If intCounter = 8 Then
        MsgBox "Error.  Could not match " & strDay & " to a weekday name."
        Exit Sub
    End If

3. Add this bit right after that:
'   First day is MONDAY:
    intCounter = intCounter - 1
    If intCounter = 0 Then intCounter = 7

These instructions should work for all three DatePicker forms, because they were all based on the same template.
Hope this helps,

Posted by: vaudousi Apr 8 2012, 12:33 PM

Your analysis was correct and now the calendars work with Monday as first day of the week.

Posted by: DanielPineault Apr 19 2012, 03:45 PM

I just installed the date/time picker in a db which I use for 2 controls, I call it the same way for both

    DoCmd.OpenForm "frmMiniDateTime", , , , , acDialog

For some unknown reason, the second errs out and always brings me to the following line
    DoCmd.MoveSize XPos * 15, YPos * 15                         '*****

Both variable have values
XPos = 2149
YPos = 618
Anyone have any ideas?

Posted by: doctor9 Apr 19 2012, 04:47 PM

From what I can tell, that section of code is meant to move the date picker near the place you double-clicked, if possible.
My monitor is set up for 1024x768 pixels. When I invoke the datepicker subform from the demo form and trace the code, my values are:
WidthInPixels: 272
HeightInPixels: 151
GetScreenResX: 1024
GetX: 482
XPos: 377
GetScreenResY: 768
GetY: 435
YPos: 370
Your XPos value seems REALLY high. Are you using two adjacent widescreen monitors with really high resolution settings?
Hope this helps,

Posted by: DanielPineault Apr 19 2012, 06:23 PM

Yes, 1600x900 and 1920x1080.
Isn't that interesting! If I unplug my secondary monitor and only my laptop display all is good. However, if I use the dual display, it errs?!
Thank you for poiniting me in the proper direction. Now to figure out a solution.
Also, is there a way to make the time piece display the current value of the control that called it when there is a value, otherwise, use the current date/time value?
Ofound a way, still have to test it further, but for anyone else looking to do this, I rewrote (added to should I say) the Form_Open event

Private Sub Form_Open(Cancel As Integer)
' Remove this hi-lited section of code if you do not have        *****
' or do not intend to use the "api_TwipsPerPixels" and           *****
' "basFormToPointer" modules.*                                   *****
' This section of code will cause the form to pop-open near      *****
' the control that called it.                                    *****
    Dim WidthInPixels As Integer, HeightInPixels As Integer     '*****
    Dim XPos As Integer, YPos As Integer                        '*****
    WidthInPixels = ConvertTwipsToPixels(Me.Width, 0)           '*****
    HeightInPixels = ConvertTwipsToPixels(Me.Detail.Height, 1)  '*****
    If GetX() + WidthInPixels > GetScreenResX() Then            '*****
        XPos = GetX() - WidthInPixels                           '*****
      Else                                                      '*****
        XPos = GetX()                                           '*****
    End If                                                      '*****
    If GetY() + HeightInPixels > GetScreenResY() Then           '*****
        YPos = GetY() - HeightInPixels                          '*****
      Else                                                      '*****
        YPos = GetY()                                           '*****
    End If                                                      '*****
    DoCmd.MoveSize XPos * 15, YPos * 15                         '*****
'**Purpose: Populate cboYear rowsource
Dim intYearTemp As Integer
Dim intCounter As Integer
Dim intMaxYear As Integer
Dim strYear As String
Dim intHr As Integer
Dim intMm As Integer
Dim sCtrlVal As Variant
    'First year is 1950 & init variables
    intYearTemp = 1950
    intMaxYear = 100
    strYear = ""
    'Populate strYear
    For intCounter = 0 To 100
        strYear = strYear & intYearTemp + intCounter & ";"
    'Set cboYear's rowsource property
    Me![cboYear].RowSource = strYear
    'Init global variables
    sCtrlVal = Screen.ActiveControl
    If IsNull(sCtrlVal) Then
        intYear = Format(Date, "yyyy")
        strMonth = Format(Date, "mmmm")
        intHr = Format(Now, "Hh")
        intMm = Format(Now, "Nn")
        intYear = Format(sCtrlVal, "yyyy")
        strMonth = Format(sCtrlVal, "mmmm")
        intHr = Format(sCtrlVal, "Hh")
        intMm = Format(sCtrlVal, "Nn")
    End If
    'Set default values for fields
    Me![cboYear] = intYear
    Me![cboMonth] = strMonth
    Me.txthr = intHr
    Me.txtmm = intMm
    Me.txtMInc = 15            
End Sub

Posted by: doctor9 Apr 20 2012, 08:16 AM

I'm afraid I can't help out with this problem, as I have only one monitor. If you find a solution, please post it!

Posted by: CyberCow Apr 20 2012, 09:31 AM

I have double monitors on all 3 of my desktops and as long as Access is open in my main monitor, I have no issues. but when run Access in my secondary monitor, I do have issues with the x/y placement routine(s). So, I imagine using using dual monitors on laptop setup would also engage such issues. I just never bothered to delve into it and attempt a fix or work-around.
HAs for acquiring a value from the calling control, I never thought to do that either. It was originally designed for data input. I'll take a look at another approach to that Daniel and post back here.

Posted by: doctor9 Apr 24 2012, 02:18 PM

I must admit, this is a very good idea. I worked on the "frmMiniDateTime" and "frmMiniDateTimeDet" date picker forms, reworking how they work internally, while adding the following new features:
* The VBA on the frmDemo form has changed, to handle either a Null value or a Date value in the textbox at the time of double-clicking,
* You can now manually type values into the hours/minutes/seconds textboxes if you like (you will get an error if you type an invalid value, however).
* After you click on a day of the month, that day will be shaded red and it will stay shaded red, to remind you which day you selected.
If I missed any problems arise, please let me know.
Dennis ( 501.51K ): 222

Posted by: doctor9 Nov 14 2012, 11:01 AM

I've thrown together an updated version of Time Pieces, now including a weekly form. If you look at the code behind the new weekly form, you'll see it handles the date data in a very different way from the monthly form, but the tables haven't been altered one bit. Basically, a month can start on any day of the week. But a weekly calendar always starts on a Sunday, even if that week contains dates from two consecutive months. So the new technique is more of an illustration of how there is more than one way to accomplish a goal.
The only other change is that I've added an "Open the weekly form to this week" button to the daily form.
EDIT: Added more detail to my explanation of how the weekly form handles data differently. ( 514.94K ): 515

Posted by: mike60smart Nov 15 2012, 06:05 AM

Hi Everyone
The attached Db shows how the Form for Appointments has been modified to allow for a Selection of a Company Name and the Selection of a of a Technician.
On the After Update of the Selection of a Company then the Map Grid Code for that Company is added to the Notes Control
After saving the Appointment both the Technician Name and the Map Code are added to the specified Appointment Date
Many thanks to Cybercow for this modification ( 158.72K ): 723

Posted by: doctor9 Dec 13 2013, 03:23 PM

This is just a minor tweak that I thought worth mentioning without going to the trouble of re-uploading the demo.
The "increase minutes by the selected increment value" button on the frmMiniDateTime form has a tiny glitch in it. The IF statement reads:

If Me.txtmm.Value >= (60 - Me.txtMInc.Value) Then ' 59

However, the .Value of a textbox is a string, not a number. So, if you have a value of "10" in the textbox, and you try to increment that value by 1, the button will actually change the value to "00", which obviously isn't what you want. So, we just need to convert the textbox value to a number for the comparison:
    If Val(Me.txtmm.Value) >= (60 - Me.txtMInc.Value) Then ' 59

I suppose you could use CInt() function instead of Val() - not sure which would be preferable.
On the same note, the BeforeUpdate events for the hours and minutes textboxes need the same sort of fix. The IF test that makes sure the value in the textbox is an integer needs to convert the string value to a number value:
            If Me.txthr <> Int(Me.txthr) Then

Recommended fix:
            If Val(Me.txthr) <> Int(Me.txthr) Then


Posted by: skymou Oct 22 2017, 03:22 PM

Under VBA7, I would suggest that the GetDC argument be ByVal hWnd As LongPtr, not Long

Also GetDesktopWindow would return LongPtr, not Long, and probably also GetSystemMetrics.