Code to change RecordSource property

Hello,
I have a form (TestApptDis) which the OnOpen event triggers the RecordSource property
to find the table that equals Date() (the tables for this form are titled with dates).
The form has a combo box (cboApptDate), on the MouseDown event for the
combo box a pop-up form (frmCalPopUp) containg a Microsoft Calendar Control 11.0 (calPopUp)
appears.
When a user selects a different date on the Calendar Control, I would like
to change the RecordSource for TestApptDis. If the table does not exist the
code will create one then Requery the control.
So far I have,

Private Sub Form_Open(Cancel As Integer)
'This code works correctly opening form on correct table.
With Forms!TestApptDis
Form.RecordSource = Date
End With    
End Sub

Private Sub cboApptDate_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
'Unhide the calendar and give it the focus.
    DoCmd.OpenForm ("frmCalPopUp")
    If Not IsNull(cboApptDate) Then
    cboApptDate.Value = Forms!frmCalPopUp!calPopUp.Value
    Else
    Forms!frmCalPopUp!calPopUp.Value = Date
    End If
End Sub

Private Sub calPopUp_Click()
'Calendar opens on todays date which is the same as
'the form's RecordSource.
'User selects another date and the value is passed to the combo box.
Forms!TestApptDis!cboApptDate = Me.calPopUp
Forms!TestApptDis!cboApptDate.SetFocus
'We close the calendar form.
DoCmd.Close
End Sub

I tried this code in the combo box AfterUpdate event:

Sub cboApptDate_AfterUpdate()
Dim strNewRecordSource As String
strNewRecordSource = Me.cboApptDate.Value & "'"
Me.Form.RecordSource = strNewRecordSource
End Sub

But it didn't work, no error codes,
Does anyone have any ideas about what I might try next?
Thanks in advance,
Paul

Start Free Trial