Open form to correct record

[Paul3rd]

Hello,
I'm building a form ("Appt"). On it is an OLE bound field containing a
small Excel spreadsheet ("OLEBound8"), and a MSCAL.Calendar.7 control
("calCtl1").
There is also a text box ("Text5") containing a date in which today's date
is the default, or the user can select a date by using a find date button
("Command7") on the form and subsequently the text box is updated.
The underlying table ("ApptDis") has two fields,
the ("ApptSheet") field which contains the bound Excel spreadsheet
template, and
a field called ("WorkDate") which is of the Date/Time type and is also the
primary key field.

My goals are threefold:
a) The form should open with the record corresponding to todays date in
("Text5").
If a record does not exist with today's date in the "WorkDate" field
then VBA creates one.
b) When the user chooses a new date the ("Appt") table is scanned for a
corresponding record,("WorkDate" field) if found the record is displayed;
if not, a new one is created.
c) Everytime a new record is created, a copy of the Excel spreadsheet template
(which resides in C:\Documents and Settings\Administrator\Application

Data\Microsoft\Templates) must be inserted into the "ApptSheet" field.

The following is the code I'm using to control "calCtl1" & "Text5"
Option Explicit

Private Sub Calctl1_Click()
Me.Text5 = Me.calCtl1.Value
Me.Text5.SetFocus
Me.calCtl1.Visible = False
End Sub

'This keeps the calendar hidden until user chooses another date


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Me.calCtl1.Visible = True

If Not IsNull(Me.Text5) Then
Me.calCtl1.Value = Me.Text5
Else
Me.calCtl1.Value = Date
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
If someone could point me in the right direction with the correct
method(s) I'd be eternally grateful.
Thanks, Paul3rd