[AccessD] Pop up Calendar date validation

Jurgen, years ago I began writing an update to my Access 97 MiniCalendar.  It's written in Access 2000, and is event driven.  In other words, when you open it up, you are actually dimming it 'withevents', so you actually get an event raised when a date is clicked on the calendar.

I'd be more than happy to send this new version to you offlist, but with the caveat that it would be 'as is' .  The calendar works just fine, it even has a 'sample form' so you can see how to implement and use it.  It's got a LOT of code behind it.  Has dropdown forms on it, etc.  Lots of fun and neat stuff.  Some things I never finished, which is why I haven't put it out in the wild.  But there have been a handful of times where I needed a calendar, and I've thrown that working version in, and it's worked just fine.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Friday, March 19, 2010 11:05 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Pop up Calendar date validation


I've got a reprieve on some other duties and have been tasked with some new reporting and that raised a question of whether I could simplify some of what I've built.
 
Scenario:  Every date textbox allows a dual entry method.
 
Double click the textbox and a calendar pops or a user may enter the control and key the date.
 
Validation can run in the controls BeforeUpdate event when a date is keyed but this event is not raised when the data is set from a PopUp form.  The BeforeUpdate calls the same validate event that is triggered via PopUp calendar so that controls validation code is in one place.
 
Every date textbox is just copy/paste from a template so that the control comes hooked up with a property sheet function event in the double-click:  =fnGetDate(Screen.ActiveControl).
 
A given specific control may be on a form that is sometimes a standalone form.  That exact form may be on a sub form and may even be multi-instanced sub form and it may also be a subsubform, single or multi-instances.
 
I am currently using a somewhat kludgy approach to validation.  If it's an employee birthdate, we must ensure that the date makes sense so the person should be between the age of 18 and 65 and warn if it is out of range, yet accept a manual over ride.
 
If it's a closing date of a newly entered project, it should accept a future date but warn if the date is past or more than 3 months in the future.
 
For a date range, it should be able to verify that the 'from' and 'to' dates are in sequence and if not, switch them.  In some cases, this enables and sets focus on a button that pops a report that uses the date range.
 
Since the control requires a double click, my preference is that the validation warnings appear before the calendar pop up closes to minimize excess mousing.  As long as the calendar is on screen, best make sure a reasonable date will be returned before closing it.
 
The problem is hooking the control's before update to run the validation and thereby cancel the unload of the calendar and ensure the appropriate validation runs.
 
The Calendar form Pop Up property is yes, Modal no.   I don't have to do some massive select case to perform the validation in the calendar.  For a given textbox, the validation is the same regardless of whether the form is embedded on a parent form or as a subsubForm.
 
The text box event property runs the procedure that opens the calendar in the first line and sets the reference to the control in the 2nd line by calling a public event on the calendar to set a variable pointing directly to the control being changed.  As the function runs it is possible to get a reference to the screen active control.  This is not the case once the calendar opens as it takes the screen.
 
The click event on any date on the calendar triggers the close and unload events that then need to find and run the particular validation event for that control.  Because of the pop up nature, the calendar needs to run the ctl.Parent or ctl.Parent.Parent or ctl.Parent.Parent.Parent validate event.
 
using on error resume next:
       strFrmSubSub = ctl.Parent.Parent.Parent.Name
       strFrmSub = ctl.Parent.Parent.Name
       strFrm = ctl.Parent.Name
there always a length to strFrm but the others may be zero length.
 
A select case determines if Len(strFrmSubSub), we need the following reference to call the form's public ValidateDate procedure:
Case Len(strFrmSubSub)
            Forms(strFrm).(strFrmSub).Form.(strFrmSubSub).Form.ValidateDate
Case Len(strFrmSub)
            Forms(strFrm).(strFruSub).Form.ValidateDate
Case Else
            Forms(strFrm).ValidateDate
 
Where it gets messy is when there are numerous controls that have multiple date textboxes with different validation events.  This is handled in the got focus event of the form that sets a module scoped variable that is resolved in a select case on the form as to which control's validation event runs in the form's ValidateDate procedure.  Of course, several date textboxes can share the same validation routine on a control provided they need to meet the same criteria.
 
Is there a cleaner way?  Can I raise the before update event of the control from a popup form to cancel the calendar unload?  It would be ideal to be able to pass a reference to the control in the event that opens the form and raise its before update when a date is clicked on the calendar.

Ciao Jürgen Welz
Edmonton, Alberta jwelz at hotmail.com 		 	   		  
_________________________________________________________________
IM on the go with Messenger on your phone
http://go.microsoft.com/?linkid=9712960
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.