-
3
- #1
BenSacheri
Programmer
thread702-1756368
I wanted to share an update of the fayt class since the discussion closed 6 months ago. I was stumped trying to find a way to reload the records of the combo box with a different query. I have two use cases for needed to requery the combo box. In one case I have cascading comboboxes and my fayt combo needs to adjust based on the selection of another combo. In another case I have a checkbox on the form that says "show all records". The form normally opens to show a subset of records but there are a few occasions when they need to see all records in the table. You could also imagine this checkbox saying "include archived records". All the ways I tried to requery the recordset caused errors. I was at an Access developers conference in Oregon last week and one evening I got some additional brains looking at the issue. At one point we thought we had it fixed and could requery the combo 3 times but on the 4th time it would crash. That had us scratching our heads. Eventually we found a sequence of code that works.
Here are some discoveries that were made:
[ul]
[li]It's best if you don't try to requery the list outside of the class. I'm not sure I can put the issue into words, but there could be a conflict between the recordset of the combobox and the mRsOriginalList recordset in the class that is a clone. The RequeryList method of the class should be used. This way the mRsOriginalList recordset can be reset and a few other tweaks can take place.[/li]
[li]Changing the RowSource of the combobox does not always cause a requery of the combobox. We had some discussion about this but in testing this class there were many times that the data was not refreshed. The previous version of this class by MajP called the .Dropdown method which would force the list to populate if it was empty, but also caused the screen to flash. I discovered that requesting .ListCount into a variable would do the same thing but not cause the screen to flash. In my testing, both .ListCount and Requery need to be called, otherwise 'Error #91 - Object variable or With block variable not set' would occur.[/li]
[li]This version supports queries that have references to controls on open forms. Earlier I wrote that "it fails because my RowSource contained a query that had a VBA function in it" but I should have said it was SQL containing things like [forms].[frmXYZ].[txtFilter]. Those references don't work when loading a DAO recordset, but they do work when loading a combobox.[/li]
[/ul]
I have used this class with a combobox containing over 12,000 records. The find-as-you-type is quick and does not repeatedly hit the database tables. This class is a big help if you are working with unwieldy list sizes.
If I find any more issues I'll try to update this thread. I was a forum lurker for a long time and I want to make sure I give something back.
Ben
I wanted to share an update of the fayt class since the discussion closed 6 months ago. I was stumped trying to find a way to reload the records of the combo box with a different query. I have two use cases for needed to requery the combo box. In one case I have cascading comboboxes and my fayt combo needs to adjust based on the selection of another combo. In another case I have a checkbox on the form that says "show all records". The form normally opens to show a subset of records but there are a few occasions when they need to see all records in the table. You could also imagine this checkbox saying "include archived records". All the ways I tried to requery the recordset caused errors. I was at an Access developers conference in Oregon last week and one evening I got some additional brains looking at the issue. At one point we thought we had it fixed and could requery the combo 3 times but on the 4th time it would crash. That had us scratching our heads. Eventually we found a sequence of code that works.
Here are some discoveries that were made:
[ul]
[li]It's best if you don't try to requery the list outside of the class. I'm not sure I can put the issue into words, but there could be a conflict between the recordset of the combobox and the mRsOriginalList recordset in the class that is a clone. The RequeryList method of the class should be used. This way the mRsOriginalList recordset can be reset and a few other tweaks can take place.[/li]
[li]Changing the RowSource of the combobox does not always cause a requery of the combobox. We had some discussion about this but in testing this class there were many times that the data was not refreshed. The previous version of this class by MajP called the .Dropdown method which would force the list to populate if it was empty, but also caused the screen to flash. I discovered that requesting .ListCount into a variable would do the same thing but not cause the screen to flash. In my testing, both .ListCount and Requery need to be called, otherwise 'Error #91 - Object variable or With block variable not set' would occur.[/li]
[li]This version supports queries that have references to controls on open forms. Earlier I wrote that "it fails because my RowSource contained a query that had a VBA function in it" but I should have said it was SQL containing things like [forms].[frmXYZ].[txtFilter]. Those references don't work when loading a DAO recordset, but they do work when loading a combobox.[/li]
[/ul]
I have used this class with a combobox containing over 12,000 records. The find-as-you-type is quick and does not repeatedly hit the database tables. This class is a big help if you are working with unwieldy list sizes.
If I find any more issues I'll try to update this thread. I was a forum lurker for a long time and I want to make sure I give something back.
Ben
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 a reference to DAO and code
'similar to the following in a form's module.
' Discussion of this class found here: [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1756368[/URL]
' Ben: This is where I tried to work out how to refresh the combo box when its
' RowSource changed. I eventually worked it out at PAUG 2016 with help from
' Dirk E. and Thomas M. In short, don't mess with the combo RowSource
' outside of this class, and make sure mRsOriginalList is reset at the same time.
'Parmeters:
' TheComboBox: Your Combobox object passed as an object
' FilterFieldName: The name of the field to Filter as
' string
' FilterFromStart: Determines if you filter a field that
' starts with the text or if the text appears anywhere
' in the record.
' HandleArrows: Determines if up/down arrow keys stop the
' scrolling of the dropdown from affecting the filter.
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' PRIVATE faytProducts As New clsFindAsYouTypeCombo
' Form_Open(Cancel As Integer)
' faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False, True
' End Sub
'
' If you need to change the RowSource or requery the ComboBox, use this method:
' faytProducts.RequeryList <optional new SQL statement>
'
'******* END: Form Code ******************
Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Private mFilterFromStart As Boolean
Private mHandleArrows As Boolean ' BS 10/13/2015
Private mAutoCompleteEnabled As Boolean ' BS 10/13/2015
'Public Property Get FilterComboBox() As Access.ComboBox
' Set FilterComboBox = mCombo
'End Property
'
'Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
' Set mCombo = TheComboBox
'End Property
'
'Public Property Get FilterFieldName() As String
' FilterFieldName = mFilterFieldName
'End Property
'
'Public Property Let FilterFieldName(ByVal theFieldName As String)
' mFilterFieldName = theFieldName
'End Property
'
'Public Sub DestroyObject()
' mRsOriginalList.Close
' Set mRsOriginalList = Nothing
'End Sub
Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String _
, Optional FilterFromStart = False _
, Optional HandleArrows As Boolean = True)
' Ben: Added 4th paramenter (optional) to support my preference on how this
' combo box should 'feel'. When this parameter is TRUE, using the up/down
' arrow keys and page up/down in the combobox will stop the fayt filter
' from adding the first highlighted list item to the filter.
On Error GoTo ErrorHandler
If Not TheComboBox.RowSourceType = "Table/Query" Then
MsgBox "This class will only work with a combobox that uses a Table or Query as the Rowsource"
Exit Sub
End If
Set mCombo = TheComboBox
Set mForm = TheComboBox.Parent
mFilterFieldName = FilterFieldName
mFilterFromStart = FilterFromStart
mForm.OnCurrent = "[Event Procedure]"
mCombo.OnGotFocus = "[Event Procedure]"
mCombo.OnChange = "[Event Procedure]"
mCombo.AfterUpdate = "[Event Procedure]"
mHandleArrows = HandleArrows
If mHandleArrows = True Then
mCombo.OnKeyDown = "[Event Procedure]" ' BS 10/13/2015
mCombo.OnClick = "[Event Procedure]" ' BS 10/13/2015
End If
Dim i As Long
With mCombo
' The following was added to handle when delayed RowSource loading has been set up. BS 1/7/2016
If .RowSource = "" Then
.RowSource = .Tag
End If
[highlight #FCE94F] i = .ListCount ' This forces the combo recordset to populate without the screen flashing or forcing Form_Load. ' BS 5/9/2016
[/highlight]
' .SetFocus ' This forces Form_Load if it hasn't run yet.
' i = .ListRows
' .ListRows = 1 ' Reduce the amount of flashing from the next line.
' .Dropdown ' This forces the combo recordset to populate.
' .ListRows = i
.AutoExpand = False
End With
' This is an alternative method but it does not work if the RowSource has a
' reference in it to a control on a form.
' Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource, dbOpenSnapshot)
' Set mCombo.Recordset = mRsOriginalList
Set mRsOriginalList = mCombo.Recordset.Clone
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure InitalizeFilterCombo of clsFindAsYouTypeCombo"
Debug.Print Err.Number, Err.Description
Exit Sub
' Resume Next
Resume
End Sub
Private Sub Class_Terminate()
Set mForm = Nothing
Set mCombo = Nothing
mRsOriginalList.Close
Set mRsOriginalList = Nothing
End Sub
Private Sub FilterList()
On Error GoTo ErrorHandler
Dim rsTemp As DAO.Recordset
Dim strText As String
Dim strFilter As String
If mAutoCompleteEnabled = False Then
' Don't filter when keystrokes like return, up/down, page up/down are entered. BS 10/15/2015
' Beep
Exit Sub
End If
strText = mCombo.Text
If mFilterFieldName = "" Then
MsgBox "Must Supply A FieldName Property to filter list."
Exit Sub
End If
If mFilterFromStart = True Then
strFilter = mFilterFieldName & " like '" & strText & "*'"
Else
strFilter = mFilterFieldName & " like '*" & strText & "*'"
End If
Set rsTemp = mRsOriginalList.OpenRecordset
rsTemp.Filter = strFilter
Set rsTemp = rsTemp.OpenRecordset
If rsTemp.RecordCount > 0 Then
Set mCombo.Recordset = rsTemp
Else
' No records found for this filter. Alert the user so they don't keep typing.
Beep
End If
If Len(strText) > 0 Then
mCombo.Dropdown
Else
' Don't make the dropdown appear if the user just cleared the field.
End If
Exit Sub
ErrorHandler:
If Err.Number = 3061 Then
MsgBox "Will not Filter. Verify Field Name is Correct."
Else
MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure FilterList of clsFindAsYouTypeCombo"
End If
End Sub
Private Sub unFilterList()
On Error GoTo ErrorHandler
Set mCombo.Recordset = mRsOriginalList
Exit Sub
ErrorHandler:
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 mCombo_AfterUpdate()
Call unFilterList
End Sub
Private Sub mCombo_Change()
Call FilterList
End Sub
Private Sub mCombo_Click()
' When a value is selected from the list and populates the box, don't let that
' cause the list to be requeried. BS 10/13/2015
mAutoCompleteEnabled = False
End Sub
Private Sub mCombo_GotFocus()
'' BS 10/13/2015: I commented out the next line because I don't like
' this behavior when tabbing through controls on the form, especially
' when a couple of combo boxes are vertically stacked.
' This causes the dropdown to load when the SET event initializes, so it must be here unless it's called in InitalizeFilterCombo().
' mCombo.Dropdown
End Sub
Private Sub mCombo_KeyDown(KeyCode As Integer, Shift As Integer)
' Handle keys that affect the auto-complete feel of the combobox. BS 10/13/2015
If mHandleArrows = True Then
' BS 10/15/2015: I'm still not sure if I want this behavior. At first it felt natural but now I'm not sure it's good.
' If KeyCode = vbKeyReturn And mCombo.ListCount >= 1 And mAutoCompleteEnabled = True Then 'And mCombo.ListIndex = -1 Then
' ' If the user pressed Enter and at least one value is in the list
' ' then pick that item.
' ' When this code fires sometimes the AfterUpdate event does not.
' ' How can you force the AfterUpdate to fire?
' Beep
' mCombo.value = mCombo.ItemData(0)
' 'Debug.Print "KeyDown: " & mCombo, mCombo.ListCount, mCombo.ListIndex
' mCombo.SetFocus
' End If
Select Case KeyCode
Case vbKeyDown, vbKeyUp, vbKeyReturn, vbKeyPageDown, vbKeyPageUp
' When these special keys are hit they begin to select records
' from the dropdown list. Without this, as soon as one record
' is selected (by highlighting it) then the entire filter is
' set to that item making it impossible to use the keyboard to
' scroll down and pick an item down in the list.
mAutoCompleteEnabled = False
Case Else
mAutoCompleteEnabled = True
End Select
End If
End Sub
Private Sub mForm_Current()
Call unFilterList
End Sub
Public Sub RequeryList(Optional pRowSource As String = "")
' This class method only needs to be called when the combobox has a new rowsource,
' like when other controls affect what it should show, or the case of a cascading combobox.
'### BEST PRACTICE ###
' Note that when using the Find-as-you-type combo, if you need to change the RowSource
' you should pass the new rowsource to the RequeryList method and do not try to change
' the source from outside of the class module. If you make changes outside of the class
' it may appear to work for 3-4 iterations but fail after that.
Dim i As Long
On Error GoTo ErrorHandler
DoCmd.Hourglass True
StatusBar "Refreshing " & mCombo.Name & "..."
DoEvents
' Debug.Print mCombo.Name; ', mCombo.RowSource
If Not mRsOriginalList Is Nothing Then
mRsOriginalList.Close
End If
Set mRsOriginalList = Nothing
If Len(pRowSource) > 0 Then
mCombo.RowSource = pRowSource
End If
' You have to do something here to force the recordset to requery. Some people
' would argue that changing the RowSource forces a requery but I didn't experience
' that in this situation.
[highlight #FCE94F] i = mCombo.ListCount ' This forces the combo recordset to populate without the screen flashing or forcing Form_Load. ' BS 5/9/2016
' Without the line above you will get random errors with the recordset:
' Error #91 - Object variable or With block variable not set
mCombo.Requery
[/highlight]
' Debug.Print mCombo.Recordset.RecordCount
Set mRsOriginalList = mCombo.Recordset.Clone
' This is an alternative method but it does not work if the RowSource has a
' reference in it to a control on a form.
' Set mRsOriginalList = CurrentDb.OpenRecordset(pRowSource, dbOpenSnapshot)
' Set mCombo.Recordset = mRsOriginalList
Exit_Sub:
DoCmd.Hourglass False
StatusBar ""
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure RequeryList of clsFindAsYouTypeCombo"
GoTo Exit_Sub
Resume
End Sub
Private Sub StatusBar(pstrStatus As String)
' [URL unfurl="true"]http://www.mrexcel.com/forum/microsoft-access/233681-access-visual-basic-applications-application-statusbar.html[/URL]
Dim lvarStatus As Variant
If pstrStatus = "" Then
lvarStatus = SysCmd(acSysCmdClearStatus)
Else
lvarStatus = SysCmd(acSysCmdSetStatus, pstrStatus)
End If
End Sub
