Problem when using WithEvents keyword in Access 97


Question

I'm using WithEvents feature to create custom class processing GotFocus events. All works fine, but when I set WithEvents object variable to nothing in Class_Terminate sub I get error 2647. Why?

Answer

Lambert,

Your version of the code is working OK except clsDateBox.Class_Terminate - the problem here AFAIK is that MS Access has destroyed control in your custom class but its reference isn't yet destoryed - have a look through the functions I'm posting in this message - they are just slightly changed yours.

Going WithEvents road with MS Access 97 isn't an easy trip - be careful.

HTH,
Shamil

P.S. The code:

'*+ ** CBF ***
Option Compare Database
Option Explicit

Public pcolTxtBoxes As New Collection

Private Sub Form_Load()
  Dim ctl As Control
  For Each ctl In Me.Form.Controls
   Select Case ctl.ControlType
    Case acTextBox:
      If ctl.Tag = "DateBox" Then
        pcolTxtBoxes.Add New clsDateBox
        Set pcolTxtBoxes(pcolTxtBoxes.Count).Control = ctl
      End If
    Case Else
   End Select
  Next ctl
End Sub

Private Sub Form_Close()
  Set pcolTxtBoxes = Nothing
End Sub
'*- ** CBF ***

'*+ ** clsDateBox ***
Option Compare Database
Option Explicit

Private WithEvents mDateBox As TextBox

Private Sub Class_Terminate()
   If Not IsDestroyed(mDateBox) Then
     MsgBox "DateBox " & mDateBox.Name & " will be destroyed now"
     Set mDateBox = Nothing
   Else
    ' you cannot set here control to nothing - you get 2467 Error -
    ' control is destroyed by MS Access but its reference isn't nothing -
    ' Set mDateBox = Nothing
   End If
End Sub

Private Function IsDestroyed(ByRef rctl As Control) As Boolean
  IsDestroyed = True
  On Error GoTo IsDestroyed_exit
  IsDestroyed = Not (rctl.Name = rctl.Name)
IsDestroyed_exit:
  Err.Clear
End Function

Public Property Set Control(ByVal ctlNewControl As TextBox)
    Set mDateBox = ctlNewControl
    BindMe mDateBox
End Property

Private Sub BindMe(ByRef rTxB As TextBox)
    Set mDateBox = rTxB
    With mDateBox
      .OnGotFocus = "[Event Procedure]"
    End With
End Sub

Private Sub mDateBox_GotFocus()
    mDateBox = dGetDate(mDateBox, mDateBox.Name)
    ' dGetDate is a function that displays a calendar control and returns a date.
End Sub
'*- *** clsDateBox ***

Copyright 1999-2008 by Shamil Salakhetdinov. All rights reserved.