error message 91 Object variable or With block variable not set

E

EddWood

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
 
D

Douglas J. Steele

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

(no e-mails, please!)


EddWood said:
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
 
D

Dirk Goldgar

Douglas J. Steele said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top