How to query combo box of only current record/row in Access data entry form?

Question

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.

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

I think, access is requerying all dropdowns rather than the current drop-down/combo-box. It does. That's by design, as there - though displayed for each row - is one combobox only. — Gustav
Why do you even need a combobox to select farmer for a selected farm? A farm can have multiple farmers? — June7
Normally, RowSource would be like: SELECT ID, [name] FROM farmer WHERE farm_ID = [cbxFarm]; then other properties: ColumnCount 2, ColumnWidths 0";1", BoundColumn 1, AutoExpand Yes, LimitToList Yes. — June7
Filtering on ID with wildcard doesn't really make sense. farm_id LIKE '1*' would return 1, 10, 11, 100, etc. This does not restrict list to a single farm's farmers. Do farms have names? — June7

June7 June7 · Accepted Answer · 2020-07-19T17:11:34

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:

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

How to query combo box of only current record/row in Access data entry form?

1 Answers