OK Nicos67 . . . . Are ya ready!
[purple]Don't forget to backup the DB[/purple]. Also, where you see [purple]
Purple?[/purple], the descriptive text has to be supplied by you. Just be careful and check for typo's.
1) Delete all the previous code given.
2) In the Declarations Section of a module in the module window, enter the following Public Variables & Constant:
Code:
[blue]Public curFrmName As String, curCtlName As String
Public Const frmMainName As String = "[purple][b]YourMainFormName?[/b][/purple]"[/blue]
3) In the same module above, add the following routine. This routine stores form & date names , as well as bring the Calendar out of hiding. [blue]The routine also allows for Passing Thru[/blue] if an selection is not made.
Code:
[blue]Public Sub OpenCal(frmName As String, ctlName As String)
Dim frm As Form
Set frm = Forms(frmMainName)
[green]'If statement keeps On Focus Event from looping![/green]
If Not frm!myCalendar.Visible Then
'Hold Names & Open Calendar
curFrmName = frmName
curCtlName = ctlName
Forms(frmMainName)!myCalendar.Visible = True
[green]'Allows user to continue without changing date if
'no selection is made! Maybe user wants to bypass![/green]
Forms(frmMainName)!myCalendar.ValueIsNull = True
Forms(frmMainName)!myCalendar.SetFocus
End If
End Sub[/blue]
4) In the [blue]GotFocus[/blue] event for all your Date Controls of interest, add the following code (this is the main activation line, allowing execution by [blue]Tab[/blue] or [blue]Mouse-Click[/blue]):
Code:
[blue] Call OpenCal("[purple][b]Your Form or subForm Name?[/b][/purple]", "[purple][b]YourDateControlName?[/b][/purple]")[/blue]
Next, in the [blue]Lost Focus[/blue] event of your Calendar Control, add the following line:
Code:
[blue] Me.TimerInterval = 50[/blue]
When the Calendar loses focus the [blue]TimerInterval[/blue] allows the event to complete. [blue]
This avoids the problem of hiding the Calendar while it has the focus![/blue] When the timer runs out, the [blue]On Timer[/blue] event takes over, transfering the Calendar Selected Date, setting the focus to the Date Control, and hiding the Calendar.
In the Main Forms On Timer event, add the following code:
Code:
[blue] Dim frm As Form, frmUse As Form, subFrm As Control
[green]'Setup Destination Objects for Focus & Date Transfer[/green]
If curFrmName = frmMainName Then
[green]'Main Form[/green]
Set frmUse = Forms(frmMainName)
frmUse.SetFocus
Else
[green]'SubForm[/green]
Set frm = Forms(frmMainName)
Set frmUse = frm(curFrmName).Form
Set subFrm = frm(curFrmName)
frm.SetFocus
subFrm.SetFocus
End If
[green]'Restore focus to origional control![/green]
frmUse(curCtlName).SetFocus
[green]'Detect Calendar ByPass[/green]
If Not IsEmpty(Me!myCalendar) Then
frmUse(curCtlName) = Me!myCalendar.Value
End If
[green]'Hide Calendar & Stop Timer[/green]
Me!myCalendar.Visible = False
Me.TimerInterval = 0[/blue]
Just one more setting. In the Calendar Control properties (other tab at the bottom), make sure [blue]ValueIsNull[/blue] property is set to [blue]No[/blue].
Cheers!
See Ya! . . . . . .
