在子窗体网格中键入以筛选选择的组合框

huangapple
go评论75阅读模式
英文:

Typing in a Combobox to filter for selection in a subform grid

问题

我在MS Access 2016中有一个带有数据表视图子表的表单。我有一列是下拉框，我正在根据输入筛选值。它对于第一次选择有效，但在我进行选择后，我想要要么转到下一条记录并进行另一个选择，要么更改我在当前行中的选择时，所有的值都会消失并为空。这是我的代码：

  1. Option Compare Database
  2. Private Const RecordSQL As String = "SELECT ConID, ItemNo, Description FROM Consignment"
  4. Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String)
  5.     Dim strSQL As String
  6.     If Len(combo.Text) > 0 Then
  7.         strSQL = defaultSQL & " WHERE " & lookupField & " LIKE '*" & combo.Text & "*'"
  8.     Else
  9.         strSQL = defaultSQL    '这是下拉框的默认行源
  10.     End If
  11.     combo.RowSource = strSQL
  12.     combo.Dropdown
  13. End Sub
  15. Private Sub Select_Item_AfterUpdate()
  16.     '重置下拉列表
  17.     Select_Item.RowSource = RecordSQL
  18.     Select_Item.Requery
  19.     Select_Item.Dropdown
  20.     Select_Item.SetFocus
  21. End Sub
  23. Private Sub Select_Item_Change()
  24.     FilterComboAsYouType Me.Select_Item, RecordSQL, "Description"
  25. End Sub
英文:

I have a form in ms access 2016 with a subform in datagrid view. I have a column that is a combobox and I am filtering the values as you type. It works for the first selection, but when after I make my selection and I want to either go to the next record and make another selection or change the selection I made in my current row, all the values disappear and are blank. This is my code:

  1. Option Compare Database
  2. Private Const RecordSQL As String = "SELECT ConID, ItemNo, Description FROM Consignment"
  4. Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String)
  5.     Dim strSQL As String
  6.     If Len(combo.Text) > 0 Then
  7.         strSQL = defaultSQL & " WHERE " & lookupField & " LIKE '*" & combo.Text & "*'"
  8.     Else
  9.         strSQL = defaultSQL    'This is the default row source of combo box
  10.     End If
  11.     combo.RowSource = strSQL
  12.     combo.Dropdown
  13. End Sub
  16. Private Sub Select_Item_AfterUpdate()
  17.     'reset dropdown list
  18.     
  19.     Select_Item.RowSource = RecordSQL
  20.     Select_Item.Requery
  21.     Select_Item.Dropdown
  22.     Select_Item.SetFocus
  23.     
  25.     End Sub
  27. Private Sub Select_Item_Change()
  28.     
  29.     FilterComboAsYouType Me.Select_Item, RecordSQL, "Description"
  30.     
  32. End Sub

答案1

得分: 0

经过研究和代码实验，我找到了这个解决方案，我根据我的应用程序进行了修改。下面的代码适用于我所需的情况：

  1. ' 
  3. Option Compare Database
  5. Private Const RecordSQL As String = "SELECT ConID, ItemNo, Description FROM Consignment"
  7. Public Sub FilterComboAsYouType(Filter As Boolean)
  8.     Dim strSQL As String
  9.     
  10.     strSQL = RecordSQL    '这是组合框的默认行来源
  11.     Me.Select_Item.RowSource = strSQL
  12.     'Me.Select_Item.Requery
  13.     
  14.     DoEvents
  15.     If Filter Then
  16.         DoEvents
  17.         strSQL = RecordSQL & " WHERE Description LIKE '*" & Me.Select_Item.Text & "*'"
  18.         Me.Select_Item.RowSource = strSQL
  19.         'Me.Select_Item.Requery
  20.         Me.Select_Item.Dropdown
  21.         'Forms![PurchaseOrders]![PO_Con Subform]!Select_Item.Refresh
  22.         'Forms![PurchaseOrders]![PO_Con Subform].Refresh
  23.         'Me.Repaint
  24.     End If
  25. End Sub
  28. Private Sub Form_Load()
  29.     FilterComboAsYouType False
  30. End Sub
  32. Private Sub Select_Item_AfterUpdate()
  33.     
  34.     FilterComboAsYouType False
  35.     
  36.     '设置焦点在主窗体控件上
  37.     'Me.Parent.Text17.SetFocus
  38.     'Me.PoQty.SetFocus
  39.     
  41. End Sub
  43. Private Sub Select_Item_Change()
  44.     
  45.     FilterComboAsYouType True
  47. End Sub
  49. Private Sub Select_Item_GotFocus()
  50.     FilterComboAsYouType False
  51. End Sub
  53. Private Sub Select_Item_LostFocus()
  54.     FilterComboAsYouType False
  55. End Sub
  57. Private Sub txtSerial_KeyUp(KeyCode As Integer, Shift As Integer)
  58.     Me.Select_Item.SetFocus
  59.     FilterComboAsYouType True
  60.     'DoEvents
  61.     Me.Select_Item.Dropdown
  62. End Sub
  64. '

这是您提供的代码的翻译部分。

英文:

After researching and playing with code, I found this solution which I modified to my application. This code below worked for what I needed:

  1. ''' 
  3. Option Compare Database
  5. Private Const RecordSQL As String = "SELECT ConID, ItemNo, Description FROM Consignment"
  7. Public Sub FilterComboAsYouType(Filter As Boolean)
  8.     Dim strSQL As String
  9.     
  10.     strSQL = RecordSQL    'This is the default row source of combo box
  11.     Me.Select_Item.RowSource = strSQL
  12.     'Me.Select_Item.Requery
  13.     
  14.     DoEvents
  15.     If Filter Then
  16.         DoEvents
  17.         strSQL = RecordSQL & " WHERE Description LIKE '*" & Me.Select_Item.Text & "*'"
  18.         Me.Select_Item.RowSource = strSQL
  19.         'Me.Select_Item.Requery
  20.         Me.Select_Item.Dropdown
  21.         'Forms![PurchaseOrders]![PO_Con Subform]!Select_Item.Refresh
  22.         'Forms![PurchaseOrders]![PO_Con Subform].Refresh
  23.         'Me.Repaint
  24.     End If
  25. End Sub
  28. Private Sub Form_Load()
  29.     FilterComboAsYouType False
  30. End Sub
  32. Private Sub Select_Item_AfterUpdate()
  33.     
  34.     FilterComboAsYouType False
  35.     
  36.     'Set Focus on Mainform Control
  37.     'Me.Parent.Text17.SetFocus
  38.     'Me.PoQty.SetFocus
  39.     
  41. End Sub
  43. Private Sub Select_Item_Change()
  44.     
  45.     FilterComboAsYouType True
  47. End Sub
  49. Private Sub Select_Item_GotFocus()
  50.     FilterComboAsYouType False
  51. End Sub
  53. Private Sub Select_Item_LostFocus()
  54.     FilterComboAsYouType False
  55. End Sub
  57. Private Sub txtSerial_KeyUp(KeyCode As Integer, Shift As Integer)
  58.     Me.Select_Item.SetFocus
  59.     FilterComboAsYouType True
  60.     'DoEvents
  61.     Me.Select_Item.Dropdown
  62. End Sub
  64. '''

huangapple
  • 本文由 发表于 2023年2月19日 22:15:26
  • 转载请务必保留本文链接：https://go.coder-hub.com/75500740.html
  • combobox
  • filtering
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定