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" <Edd_nospam_@/Wood\hotmail.co.uk> wrote in message 
news:3A977C42-1B78-40CA-A0D5-169E2A692CE3@microsoft.com...
> 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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:%23fDoTNHaKHA.5300@TK2MSFTNGP02.phx.gbl...
> 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)