Greetings
Win 10 / Access 2016 / 64 bit
I have created a 'searching' form based on the example from
http://www.opengatesw.net/ms-access-...pe-Access.html
This creates a form which is a static copy of the information in my table, which is basically my creation based on their template.
At the end of the explanation of the 'Search As You Type' 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'm not sure, but I get the impression this change could make a form in which I could edit the table contents directly,
but I'm not understanding the tweaks it wants me to do. (A bonus would be that it lets my 'Plot' (PL) field show more than
the current ~250 characters - but I have to eat this elephant slowly)
As I created it, the search creates a list box that holds all the fields, from which I then copy over the selected record to text boxes in a form. But they are 'static'.
Here's the code I have ...
================================================== =========================================
Option Compare Database
Option Explicit
'************* Code Start **************
' This code was originally written by OpenGate Software
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
' OpenGate Software http://www.opengatesw.net
Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)
'================================================= =================================
' THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
' ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
' FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
' SQL (ROWSOURCE) SHOULD BE.
'
' ctlSearchBox THE TEXTBOX THE USER TYPES IN TO SEARCH
'
' ctlFilter THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
'
' strFullSQL THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
' RESULTS ARE RETURNED
'
' strFilteredSQL THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
' YOU WOULD WANT TO USE '...like ""*" & me.txtsearch.value & "*"""
' TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
'
' ctlCountLabel (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
' COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
'================================================= ====================================
'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
Const blnEmptyOnNoMatch = True 'Set to true if you want nothing to appear if nothing matches their search
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
70 If ctlFilter.ListCount > 0 Then
80 ctlSearchBox.SetFocus
90 ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
100 Else
110 If blnEmptyOnNoMatch = True Then
120 ctlFilter.RowSource = ""
130 Else
140 ctlFilter.RowSource = strFullSQL
150 End If
160 End If
170 Else
180 ctlFilter.RowSource = strFullSQL
190 End If
200 Else
210 ctlFilter.RowSource = 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
' ***** Code End ******
__________________________________________________ __________________________________________________ ___
Option Compare Database
Option Explicit
' THIS FORM PROVIDES AN EXAMPLE OF THE SEARCH AS YOU TYPE CAPABILITY.
' YOU NEED TO EMULATE THE txtSearch_Change, txtSearch_KeyPress,
' txtSearch_GotFocus, txtSearch_LostFocus events
' IN ORDER TO MAKE IT WORK; THE LINES WITH ***ADAPT THIS LINE TO YOUR OWN FORM***
' INDICATE SOMETHING YOU WILL
' NEED TO CHANGE TO MATCH HOW YOUR FORM IS DEPLOYED
Private blnSpace As Boolean 'INCLUDE THIS LINE ON YOUR FORM
Private Sub btnClearFilter_Click()
'CODE FOR THE RED "X" BUTTON TO CLEAR THE FILTER AND SHOW ALL
On Error Resume Next
10 Me.txtSearch.Value = ""
20 txtSearch_Change
End Sub
Private Sub txtSearch_Change()
'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
Dim strFullList As String
Dim strFilteredList As String
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 ID, TL, YR, WD, HT, SZ, DR, PS, BW, CL, SL, ES, ED, LG, ST, NT, LN, HR, MN, RK, RT, PL, AC, AN, AD, BI, CM, CR, DO, DM, FM, FA, FN, HS, HO, MY, MU, RM, SF, SP, TH, WR, WS, UR, DW, FL, IO, C1, C2, C3, C4, C5, C6, C7, C8 FROM tbNm ORDER BY tbNm.TL;"
'specify the way you want the rowsource to be filtered based on the user's entry
40 strFilteredList = "SELECT ID, TL, YR, WD, HT, SZ, DR, PS, BW, CL, SL, ES, ED, LG, ST, NT, LN, HR, MN, RK, RT, PL, AC, AN, AD, BI, CM, CR, DO, DM, FM, FA, FN, HS, HO, MY, MU, RM, SF, SP, TH, WR, WS, UR, DW, FL, IO, C1, C2, C3, C4, C5, C6, C7, C8 FROM tbNm WHERE [TL] LIKE ""*" & Me.txtSearch.Value & _
"*"" OR [YR] LIKE ""*" & Me.txtSearch.Value & "*"" ORDER BY [TL]"
'run the search
50 fLiveSearch Me.txtSearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount
60 End If
End Sub
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
'NECESSARY TO IDENTIFY IF THE USER IS HITTING THE SPACEBAR
'IN WHICH CASE WE WANT TO IGNORE THE INPUT
10 On Error GoTo err_handle
20 If KeyAscii = 32 Then
30 blnSpace = True
40 Else
50 blnSpace = False
60 End If
70 Exit Sub
err_handle:
80 Select Case Err.Number
Case Else
90 MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
vbCrLf & "Error " & Err.Number & "(" & Erl & ")"
100 End Select
End Sub
Private Sub txtSearch_GotFocus()
' USED TO REMOVE THE PROMPT IF THE CONTROL GETS FOCUS
10 On Error Resume Next
20 If Me.txtSearch.Value = "(type to search)" Then
30 Me.txtSearch.Value = ""
40 End If
End Sub
Private Sub txtSearch_LostFocus()
' USED TO ADD THE PROMPT BACK IN IF THE CONTROL LOSES FOCUS
10 On Error Resume Next
20 If Me.txtSearch.Value = "" Then
30 Me.txtSearch.Value = "(type to search)"
40 End If
End Sub
================================================== ===================================
As always, thanks for the help