James,
Make a new form called frmCalendar, and place a calendar control on it
(Insert > ActiveX Controls, select Calendar Control X.0); its name
should default to Calendar0. Then add a command button, and click cancel
as soon as the button wizard comes up; its name should default to
Command1. If the names are different, you'll have to change accordingly
in the code below. Same goes for the original form's name, which I will
assume to be frmMainForm, and the target date control on the form,
assumed named txtShowDate.
Select the command button, change its Caption property (tab Format) to
OK or something, then got to tab Events, put the cursor in the On Click
event and click on the little button with the ellipsis sign that appears
on the right; select Code Builder. You will be taken to a VBA editor
screen, and these two lines of code will be there:
Private Sub Command1_Click()
End Sub
Paste the following in between:
vSep = Instr(1, OpenArgs, ";")
vFrm = Left(OpenArgs, vSep-1)
vCtl = Right(OpenArgs, Len(OpenArgs)-vSep)
Forms(vFrm).Controls(vCtl) = Me.Calendar0
DoCmd.Close acForm, Me.Name
Go back to form design, save and close. Now open the main form in design
view, select the date control, go to its On Enter property on tab Events
and do as before; you should get the lines:
Private Sub YourControlName_Enter()
End Sub
Paste the following code in between:
vCaller = Me.Name & ";" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar" ,,,,,,vCaller
If IsDate(Me.ActiveControl) Then
Forms![frmCalendar]!Calendar0 = Me.ActiveControl
Else
Forms![frmCalendar]!Calendar0 = Date
End If
This way, upon entering the date control, the calendar form pops up; if
there was already a date in the date control, the calendar will open to
that date; otherwise, it will default in the current date. When the user
selects a date with the mouse and clicks OK, the calendar form will pass
the selected date to the date control, and close.
Note: this is designed so the same calendar form will work with any
control on any form, as long as it is on the main form (not in a
subform), and it has the above code in its On Enter event; the code
passes the form and control names to the calendar form, so the latter
knows where to send the value back.
If you want to use a separate command button instead of the control's
Enter event, then the code behind the button would have to have the
control name hardcoded:
vCaller = Me.Name & ";" & Me.NameOfTheDateControl
DoCmd.OpenForm "frmCalendar" ,,,,,,vCaller
If IsDate(Me.ActiveControl) Then
Forms![frmCalendar]!Calendar0 = Me.Me.NameOfTheDateControl
Else
Forms![frmCalendar]!Calendar0 = Date
End If
HTH,
Nikos
