ms-access - 在 Access 中为 Search-As-You-Type 截断尾随空格
问题描述
我正在创建一个动态搜索,当用户在文本框中输入时过滤数据列表。
Private Sub TxtSearch_Change()
Dim CursorPosition As Long
Dim strSearch As String
Dim sqlSearch As String
CursorPosition = TxtSearch.SelStart
Me.Dirty = False 'set the dirty property to false to save the current value
strSearch = ""
If Not IsNull(Me.TxtSearch.Value) Then
strSearch = Me.TxtSearch.Value
End If
searchLength = Len(strSearch)
If searchLength < CursorPosition Then
For i = 1 To (CursorPosition- searchLength)
strSearch = strSearch + " "
Next
End If
'Check if a keyword has been entered or not
If strSearch = "" Then
Me.TxtSearch.SetFocus
sqlShowAll = "SELECT * FROM qrySearch"
Forms![frmSearch]!fsubTest.Form.RecordSource = sqlShowAll
Else
sqlSelect = "SELECT * FROM qrySearch WHERE ("
sqlLastName = "(LastName Like ""*" & strSearch & "*"")"
sqlFirstName = " OR (FirstName Like ""*" & strSearch & "*"")"
sqlFullName = " OR (FullName Like ""*" & strSearch & "*"")"
sqlEnd = ")"
sqlAllNames = sqlLastName & sqlFirstName & sqlFullName
sqlSearch = sqlSelect & sqlAllNames & sqlEnd
Forms![frmSearch]!fsubTest.Form.RecordSource = sqlSearch
End If
TxtSearch.SelStart = CursorPosition
End Sub
Access 会截断文本字段中的尾随空格。有没有办法解决这个问题?我已经实现了一个 for 循环来恢复用于搜索的尾随空格,但我想保存尾随空格,以便用户继续输入时空格不会消失。例如,我可以输入“Jane”并搜索“Jane”,但是当返回到文本框时,我会看到“Jane”,所以我永远无法输入“Jane Doe”而只能输入“JaneDoe”。
解决方案
这是我用来完成您正在寻找的代码的代码。我将搜索框“Searchfor”作为我输入的位置,将“SearchResults”作为包含数据的组合框。还有一个文本框“SrchText”,供查询“QRY_SearchAll”使用。该查询是一系列“Like” &[Forms]![FRM_SearchMulti]![SrchText]&&#34;&#34;&#34;我想在组合框中显示的每个字段,见图。
Private Sub SearchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
SrchText = vSearchString
'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.SearchResults.Requery
'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box SearchFor,
'and restores trailing space lost when focus is shifted to the list box
Me.SearchFor = vSearchString
Me.SearchFor.SetFocus
Me.SearchFor.SelStart = Me.SearchFor.SelLength
Exit Sub
End If
'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If
End Sub
关于此系统的一个警告:它使用重新查询而不是刷新。这对于一个相当快的系统上的合理数量的记录来说是很好的。我发现当我尝试在一个古老的 Sharepoint 服务器上对数据使用相同的代码时,我会在输入每个字母后延迟 10 秒。因此,如果您要处理大量记录或服务器速度较慢,您可能需要将“requery”更改为“refresh”。
推荐阅读
- c# - 如何在 C# 中创建包含不同类型的字典类型?
- java - 如何在Java中等待函数内部的函数调用,这可能吗?
- javascript - 如何改进功能中的标志?
- google-cloud-firestore - 如何对数据库中的云 Firestore 文档进行排序?
- opencv - Yolo Darknet:如何标记图像
- android - 使用静态处理程序更新 UI 线程
- c++ - 在 C++ 中输出某些字符串
- go - 安装wirelatency时没有这样的文件或目录
- git - 从多模块 Maven 项目中删除和忽略目标文件夹
- python - Django TabularInline 通过关系