Search as you type
Windows 7 64bit 
Access 2010 64bit
I'm trying to modify the sample database found here use a textbox to filter a subform rather than a ListBox.
http://www.opengatesw.net/ms-access-...pe-Access.html
At the bottom of the page it states:
You can also apply this same technique to a form instead of just a listbox with some minor tweaks. All you would need to change is the function:
fLiveSearch to change references from RowSource to RecordSource, and ListCount with RecordSetClone.RecordCount
I've tried those modifications as shown below, however it keeps generating the error "91, Object variable or With block variable not set" on this line:
ctlSubForm = Forms![frmSearchExample]!sbfItems.Form
You'll see that I commented out the original code as a reference.
Code:Private Sub txtSearch_Change() 'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX 'http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html Dim strFullList As String Dim strFilteredList As String Dim ctlSubForm As Control ctlSubForm = Forms![frmSearchExample]!sbfItems.Form ' This msgbox returns the correct result MsgBox Me.sbfItems.Form.RecordSource 10 If blnSpace = False Then 20 Me.Refresh 'refresh to make sure the text box changes are actually available to use 'specify the default/full rowsource for the control 30 strFullList = "SELECT RecordID, First, Last FROM tblNames ORDER BY First;" 'specify the way you want the rowsource to be filtered based on the user's entry 40 strFilteredList = "SELECT RecordID, First, Last FROM tblNames WHERE [First] LIKE ""*" & Me.txtSearch.Value & _ "*"" OR [Last] LIKE ""*" & Me.txtSearch.Value & "*"" ORDER BY [First]" 'run the search '50 fLiveSearch Me.txtSearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount 50 fLiveSearch Me.txtSearch, ctlSubForm, strFullList, strFilteredList, Me.txtCount 60 End If End SubThank you,Code:Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _ strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control) '10 On Error GoTo err_handle 'restore the cursor to where they left off 20 ctlSearchBox.SetFocus 30 ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1 40 If ctlSearchBox.Value <> "" Then 'Only fire if they've input more than two characters (otherwise it's wasteful) 50 If Len(ctlSearchBox.Value) > iSensitivity Then '60 ctlFilter.RowSource = strFilteredSQL 60 ctlFilter.RecordSource = strFilteredSQL '70 If ctlFilter.ListCount > 0 Then 70 If ctlFilter.RecordsetClone.RecordCount > 0 Then 80 ctlSearchBox.SetFocus 90 ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1 100 Else 110 If blnEmptyOnNoMatch = True Then '120 ctlFilter.RowSource = "" 120 ctlFilter.RecordSource = "" 130 Else '140 ctlFilter.RowSource = strFullSQL 140 ctlFilter.RecordSource = strFullSQL 150 End If 160 End If 170 Else '180 ctlFilter.RowSource = strFullSQL 180 ctlFilter.RecordSource = strFullSQL 190 End If 200 Else '210 ctlFilter.RowSource = strFullSQL 210 ctlFilter.RecordSource = strFullSQL 220 End If 'if there is a count label, then update it 230 If IsMissing(ctlCountLabel) = False Then 240 ctlCountLabel.Caption = "Displaying " & Format(ctlFilter.ListCount - 1, "#,##0") & " records" 250 End If 260 Exit Function err_handle: 270 Select Case Err.Number Case 91 'no ctlCountLabel 'exit 280 Case 94 'null string 'exit 290 Case Else 300 MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _ vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl 310 End Select End Function
Ken
Reply With Quote
