vba - Busque mientras escribe con MS Access Combobox

ms-access access-vba

Question

Me gustaría crear un cuadro combinado de búsqueda simple como se escribe en Microsoft Access como se muestra en la imagen a continuación.

NB: la imagen de arriba es de una implementación complicada de lo que estoy tratando de lograr desde aquí

Mi cuadro combinado se llama ctlSearch . Utilizando Visual Basic, me gustaría conectarme al evento onChange , detectar la entrada del usuario y, en consecuencia, perfeccionar la lista de posibles resultados. ¿Es posible utilizar este enfoque para implementar el cuadro combinado de búsqueda por tipo?

Answer 1

Puede configurar el combo o cuadro de lista de la siguiente manera:

SELECT ID,Hotel,Location FROM Sometable t
WHERE t.Hotel
LIKE "*" & Forms!YourForm!txtSearch.Text & "*"
ORDER BY t.Hotel

Luego, en el evento Cambiar, vuelva a consultar el combo o el cuadro de lista.

Answer 2

Intenta usar esto Esto es mucho más simple que tu fuente mencionada.

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