搜索访问列表框的数据作为你型在MS Access形式 [英] Searching Access List Boxes data as-you-type in MS access forms
问题描述
我想实现的搜索列表框的数据来填充为你型与文本框费尔德。
I am trying to implement search list boxes data to populate as-you-type with the text box feild.
我看了几个文件和净有用的材料发现下面的链接有用的执行我的要求，所以我用几乎相同的code，但结束了一个问题。
I read couple documents and useful material in net found the following link useful to implement my requirement so I used almost same code but ended up with an issue.
的http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html
我在我的形式，其中有100多个项目中有一个Primary_skill列表框费尔德，我试图实现按搜索我在表单中输入的txt箱费尔德数据自动显示。
I have a "Primary_skill" List box feild in my form where it has 100+ items in it and I am trying implement data to auto display as per the search I enter txt box feild in the form.
我跑这里的问题，我无法选择这里寻找两个不同的项目。 （我得到的线Me.refresh一些错误的形式code
The problem I run here, I was unable to choose two different items in search here. (I get some error with the line Me.refresh in form code
举个例子来阐述：我想选择用户primary_skills都是DB2和SQL服务器的地方，因为我是能够搜索intially和选择DB2的复选框，后来我改变搜索TXT我遇到错误指向调试我.REFRESH线上的改变事件。
Example to elaborate: I want to choose user primary_skills are both "DB2" and "SQL server" where as I was able search intially and selected the check box of db2 and later I change search txt i get error pointing debug at me.refresh line in "on change" event.
**Form search as-you-type List box code**
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 TEMP.Primary_Skill FROM TEMP;"
'specify the way you want the rowsource to be filtered based on the user's entry
40 strFilteredList = "SELECT TEMP.Primary_Skill FROM TEMP WHERE [Primary_Skill] LIKE ""*" & Me.txtsearch.Value & _
"*"""
'run the search
50 fLiveSearch Me.txtsearch, Me.Primary_Skill, 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
**Modsearach (Module Code):**
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
'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 ******
任何想法，我在这里缺少什么。谢谢！
Any idea what I am missing here. Thanks!
推荐答案
首先，不使用允许多个值。它只会给你头疼。如果你想用一个一对多的关系，你应该使用一个连接表来代替。
Firstly, don't use "Allow Multiple Values". It will only give you headaches. If you want to use a one-to-many relationship you should use a join table instead.
编辑：您可以允许在列表框多个值，但你必须通过它迭代找到值，并把它们纳入分贝个别如果你想这样做的权利。所以，我建议避开它，除非你知道自己在做什么，或者愿意更多地了解编码和SQL之类的东西。
You can allow multiple values on the listbox, but you'll have to iterate through it to find the values and put them into the db individually if you want to do it right. So I'd suggest avoiding it unless you know what you are doing or are willing to learn more about coding and SQL and stuff.
话虽这么说，这个问题看起来并不涉及到code已经导入，但关系到列表框本身的：
That being said, the issue doesn't look related to the code you've imported but is related to the listbox itself:
什么是列表框控件源？例如，如果列表框的控制源为一列，它无法在默认情况下同时接受多个值。你不得不在窗体的源表更改。列表框需要匹配数据库：如果数据库只允许一个值，列表框必须遵守，如果数据库允许多个然后列表框也可以让他们
What is the control source of the list box? For example, if the control source of the listbox is a single column, it cannot by default also accept multiple values. You'd have to change it in the source table of your form. The list box needs to match the database: if the database only allows one value, the list box must follow, if the database allows multiple then list box can also have them.
在Me.Refresh运行，它会导致列表框失去焦点，这意味着访问将尝试更新列表框控件来源。我的猜测是控制源不能接受多个值，并导致所观察到的问题。
When Me.Refresh runs, it causes the listbox to lose focus which means access will attempt to update the Control Source of the listbox. My guess is that the control source can't accept multiple values and is leading to the observed bug.
这篇关于搜索访问列表框的数据作为你型在MS Access形式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
