DAO.Recordset weirdness crashes Access

[JoshT]

I've written code for a form in Access 2000, which contains a Calendar Control 9.0. Version A of the code pasted below crashes Access with an Application Error after about 2 - 5 clicks on the calendar control. Version B pasted below works fine. Why? The only difference is where I declare the variables.

Thanks,
Josh

Version A:

Option Compare Database
Option Explicit

Dim rs As DAO.Recordset 'the recordset
Dim Q As String 'query string
Dim CalValue As String 'storage for CalendarControl.value

Private Sub CalendarControl_Click()
    'set subform to only display for the date picked
    CalValue = CalendarControl.Value
    Q = "SELECT * FROM [TimeSheetTable] WHERE [Date1] = #" & _ CalValue & "#;"
    TimeSheetSubForm.Form.RecordSource = Q
    
    'enter date into subform if empty
    Set rs = TimeSheetSubForm.Form.Recordset
    If rs.EOF Then
        TimeSheetSubForm.Form.txtDate.SetFocus
        TimeSheetSubForm.Form.txtDate.Text = CalValue
    Else
    End If
    
End Sub

Private Sub Form_Load()
    'show today when form opens
    CalendarControl.Value = Now
    CalendarControl_Click
End Sub

Private Sub Form_Unload(Cancel As Integer)
    'destroy recordset
    Set rs = Nothing
End Sub

Version B:

Option Compare Database
Option Explicit

Private Sub CalendarControl_Click()
    Dim rs As DAO.Recordset 'the recordset
    Dim Q As String 'query string
    Dim CalValue As String 'storage for CalendarControl.value
    
    'set subform to only display for the date picked
    CalValue = CalendarControl.Value
    Q = "SELECT * FROM [TimeSheetTable] WHERE [Date1] = #" & _ CalValue & "#;"
    TimeSheetSubForm.Form.RecordSource = Q
    
    'enter date into subform if empty
    Set rs = TimeSheetSubForm.Form.Recordset
    If rs.EOF Then
        TimeSheetSubForm.Form.txtDate.SetFocus
        TimeSheetSubForm.Form.txtDate.Text = CalValue
    Else
    End If
    
    Set rs = Nothing
End Sub

Private Sub Form_Load()
    'show today when form opens
    CalendarControl.Value = Now
    CalendarControl_Click
End Sub

[paulw]

Has to be with poor memory management by VB - Dims inside the procedure ensure that variable lifetime does not extend beyond the end of the procedure lifetime - Version A leaves variables hanging around, crashing the machine...

Paul.

[Lafor]

Another difference u forget to point out and that can be crucial


is where you set

Set rs = Nothing

Check it out...!!

[honeybee]

Actually I have never programmed in Access, but from whatever VB knowledge I have, I can give you another tip.

In your first code, after you are done with the recordset, close it with something like

rsRecordSet.Close

Or the problem could be the statement

[code]
Set rsRecordSet = Nothing
[\code]