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
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