如何在 Access 数据输入表单中查询仅当前记录/行的组合框? [英] How to query combo box of only current record/row in Access data entry form?

问题描述

我在 Access 中创建了一个数据输入表单，该表单使用组合框输入农民姓名.使用组合框是为了方便并确保只输入列表中的农民.为方便起见，在您输入时重新查询组合框.组合框适用于第一个条目，但在查询下一行时以前的农民姓名将消失.我认为，Access 正在重新查询所有下拉列表，而不是当前的下拉列表/组合框.

I have created a data entry form in Access that uses combobox for entering farmer name. The combobox is used for ease and to make sure only farmers from the list are entered. For ease combo box is re-queried as you type in. The combobox works well for the first entry but previous farmers' names are vanished when queried for the next row. I think, Access is requerying all dropdowns rather than the current drop-down/combo-box.

用于查询下拉列表的 VBA 如下:

The VBA for the querying drop down is given below:

 Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, 
   lookupField As String)
   Dim strSQL As String
     If Len(combo.Text) > 0 Then
    strSQL = defaultSQL & " AND " & lookupField & " LIKE '*" & combo.Text & 
   "*'"
    Else
       strSQL = defaultSQL   'This is the default row source of combo box
   End If
    combo.RowSource = strSQL
    combo.Dropdown

  End Sub

Private Sub Combo137_Change()
    FilterComboAsYouType Me.Combo137, "SELECT  farmer.name,farmer.ID FROM farms INNER JOIN farmer ON 
 farms.ID = farmer.farm_id where farms.ID LIKE" & "'" & Form_Name & "*'", "farmer.name"
 End Sub

Private Sub Combo137_GotFocus()
If Form_Name <> "" Then
FilterComboAsYouType Me.Combo137, "SELECT  farmer.name,farmer.ID FROM farms INNER JOIN farmer ON 
farms.ID = farmer.farm_id where farms.ID LIKE" & "'" & Form_Name & "*'", "farmer.name"
Else
FilterComboAsYouType Me.Combo137, "SELECT  farmer.name,farmer.ID FROM farms INNER JOIN farmer ON 
  farms.ID = farmer.farm_id where farms.ID LIKE" & "'" & "NONE" & "*'", "farmer.name"
End If
End Sub

推荐答案

是的，所有记录都将显示相同的过滤列表，因为只有一个组合框并且属性设置反映在所有实例中.根据另一个字段/控件中的值过滤组合框 RowSource 称为级联".或依赖".此外，您的 RowSource 具有别名 - 保存的值不是显示的值.过滤列表后，显示别名将无法用于已过滤掉的已保存值的记录.这是级联组合框的一个众所周知的问题.处理选项:

Yes, all records will show the same filtered list because there is only one combobox and property settings are reflected in all instances. Filtering a combobox RowSource based on value in another field/control is known as "cascading" or "dependent". Also, your RowSource has alias - value saved is not value displayed. When the list is filtered the display alias will not be available for records that have saved value which has been filtered out. This is a well-known issue of cascading combobox. Options for dealing with:

1. 对于任何表单样式，仅过滤新记录或更改主值时的列表，然后重置为现有记录的完整列表

1. for any form style, only filter the list for new record or when primary value is changed, then reset to full list for existing records

对于连续或数据表视图中的表单，在表单记录源中包含查找表，将文本框绑定到查找表中的描述字段，将文本框放置在组合框的顶部，将文本框设置为 Locked Yes 和 TabStop No

for forms in Continuous or Datasheet view, include lookup table in form RecordSource, bind a textbox to descriptive field from lookup table, position textbox on top of combobox, set textbox as Locked Yes and TabStop No

这篇关于如何在 Access 数据输入表单中查询仅当前记录/行的组合框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！