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...:D

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]