error message 91 Object variable or With block variable not set

Access 2007

I am trying to implement the 'FindAsYouType' example below and each time I
open the form I get this error message:

"91 Object variable or With block variable not set"

From the information I have applied the changes required, 'myCombo' and
'mySearch' field into the form code but cannot see where the problem lies,
can anyone advise what is wrong

Thanks

FAYTC code


Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use:To use the class, you need code similar to the 'following in a form's
module.
'Also requires a reference to DAO
'Two properties must be set: FilterComboBox
'and FilterFieldName. These are the combo box object
'and the name of the field that you are filtering.
'
'*******Start Form Code*******************
'
'Public faytCombo As FindAsYouTypeCombo
'
'Private Sub Form_Open(Cancel As Integer)
' Set faytCombo = New FindAsYouTypeCombo
' Set faytCombo.FilterComboBox = Me.myCombo
' faytCombo.FilterFieldName = "mySearch"
'End Sub
'
'******* END Form Code ******************
'

Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Public Property Get FilterComboBox() As Access.ComboBox
Set FilterComboBox = mCombo
End Property
Public Property Set FilterComboBox(theFilterComboBox As Access.ComboBox)
On Error GoTo errLabel
If Not theFilterComboBox.RowSourceType = "Table/Query" Then
MsgBox "This class will only work with a combobox that uses a Table or
Query as the Rowsource"
Exit Property
End If

Set mCombo = theFilterComboBox
Set mForm = theFilterComboBox.Parent
mForm.OnCurrent = "[Event Procedure]"
mCombo.OnGotFocus = "[Event Procedure]"
mCombo.OnChange = "[Event Procedure]"
mCombo.AfterUpdate = "[Event Procedure]"
With mCombo
.SetFocus
.AutoExpand = False
End With
Set mRsOriginalList = mCombo.Recordset.Clone
Exit Property
errLabel:
MsgBox Err.Number & " " & Err.Description

End Property

Private Sub mCombo_Change()
Call FilterList
End Sub
Private Sub mCombo_GotFocus()
mCombo.Dropdown
End Sub
Private Sub mCombo_AfterUpdate()
Call unFilterList
End Sub
Private Sub mForm_Current()
Call unFilterList
End Sub
Private Sub FilterList()
On Error GoTo errLable
Dim rsTemp As DAO.Recordset
Dim strText As String
Dim strFilter As String
strText = mCombo.Text
If mFilterFieldName = "" Then
MsgBox "Must Supply A FieldName Property to filter list."
Exit Sub
End If
strFilter = mFilterFieldName & " like '" & strText & "*'"
Set rsTemp = mRsOriginalList.OpenRecordset
rsTemp.Filter = strFilter
Set rsTemp = rsTemp.OpenRecordset
If rsTemp.RecordCount 0 Then
Set mCombo.Recordset = rsTemp
End If
mCombo.Dropdown
Exit Sub
errLable:
If Err.Number = 3061 Then
MsgBox "Will not Filter. Verify Field Name is Correct."
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub
Private Sub unFilterList()
On Error GoTo errLable
Set mCombo.Recordset = mRsOriginalList
Exit Sub
errLable:
If Err.Number = 3061 Then
MsgBox "Will not Filter. Verify Field Name is Correct."
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub
Public Property Get FilterFieldName() As String
FilterFieldName = mFilterFieldName
End Property
Public Property Let FilterFieldName(ByVal theFieldName As String)
mFilterFieldName = theFieldName
End Property
Private Sub Class_Initialize()

End Sub
Private Sub Class_Terminate()
Set mForm = Nothing
Set mCombo = Nothing
Set mRsOriginalList = Nothing
End Sub

Do you know the exact line that raises the error? Might it be

Set mRsOriginalList = mCombo.Recordset.Clone

I don't think combo boxes have a Recordset associated with them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"EddWood" wrote in message
...
Access 2007

I am trying to implement the 'FindAsYouType' example below and each time
I open the form I get this error message:

"91 Object variable or With block variable not set"

From the information I have applied the changes required, 'myCombo' and
'mySearch' field into the form code but cannot see where the problem lies,
can anyone advise what is wrong

Thanks

FAYTC code


Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use:To use the class, you need code similar to the 'following in a form's
module.
'Also requires a reference to DAO
'Two properties must be set: FilterComboBox
'and FilterFieldName. These are the combo box object
'and the name of the field that you are filtering.
'
'*******Start Form Code*******************
'
'Public faytCombo As FindAsYouTypeCombo
'
'Private Sub Form_Open(Cancel As Integer)
' Set faytCombo = New FindAsYouTypeCombo
' Set faytCombo.FilterComboBox = Me.myCombo
' faytCombo.FilterFieldName = "mySearch"
'End Sub
'
'******* END Form Code ******************
'

Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Public Property Get FilterComboBox() As Access.ComboBox
Set FilterComboBox = mCombo
End Property
Public Property Set FilterComboBox(theFilterComboBox As Access.ComboBox)
On Error GoTo errLabel
If Not theFilterComboBox.RowSourceType = "Table/Query" Then
MsgBox "This class will only work with a combobox that uses a Table or
Query as the Rowsource"
Exit Property
End If

Set mCombo = theFilterComboBox
Set mForm = theFilterComboBox.Parent
mForm.OnCurrent = "[Event Procedure]"
mCombo.OnGotFocus = "[Event Procedure]"
mCombo.OnChange = "[Event Procedure]"
mCombo.AfterUpdate = "[Event Procedure]"
With mCombo
.SetFocus
.AutoExpand = False
End With
Set mRsOriginalList = mCombo.Recordset.Clone
Exit Property
errLabel:
MsgBox Err.Number & " " & Err.Description

End Property

Private Sub mCombo_Change()
Call FilterList
End Sub
Private Sub mCombo_GotFocus()
mCombo.Dropdown
End Sub
Private Sub mCombo_AfterUpdate()
Call unFilterList
End Sub
Private Sub mForm_Current()
Call unFilterList
End Sub
Private Sub FilterList()
On Error GoTo errLable
Dim rsTemp As DAO.Recordset
Dim strText As String
Dim strFilter As String
strText = mCombo.Text
If mFilterFieldName = "" Then
MsgBox "Must Supply A FieldName Property to filter list."
Exit Sub
End If
strFilter = mFilterFieldName & " like '" & strText & "*'"
Set rsTemp = mRsOriginalList.OpenRecordset
rsTemp.Filter = strFilter
Set rsTemp = rsTemp.OpenRecordset
If rsTemp.RecordCount 0 Then
Set mCombo.Recordset = rsTemp
End If
mCombo.Dropdown
Exit Sub
errLable:
If Err.Number = 3061 Then
MsgBox "Will not Filter. Verify Field Name is Correct."
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub
Private Sub unFilterList()
On Error GoTo errLable
Set mCombo.Recordset = mRsOriginalList
Exit Sub
errLable:
If Err.Number = 3061 Then
MsgBox "Will not Filter. Verify Field Name is Correct."
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub
Public Property Get FilterFieldName() As String
FilterFieldName = mFilterFieldName
End Property
Public Property Let FilterFieldName(ByVal theFieldName As String)
mFilterFieldName = theFieldName
End Property
Private Sub Class_Initialize()

End Sub
Private Sub Class_Terminate()
Set mForm = Nothing
Set mCombo = Nothing
Set mRsOriginalList = Nothing
End Sub

"Douglas J. Steele" wrote in message
...
Do you know the exact line that raises the error? Might it be

Set mRsOriginalList = mCombo.Recordset.Clone

I don't think combo boxes have a Recordset associated with them.


They do, at least in Access 2003 and later. However, in Access 2003 I find
that the combo's Recordset property returns Nothing unless my code has
already set it to a recordset I created. I don't know how Access 2007
behaves in this regard.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)