Access cascading combobox autofill When left with

2019-09-19 11:06发布

问题:

I have ~50 cascading comboboxes on a form that I want to autofill if only one option is left. I found some code that worked for cboTwo (second combobox), but the other comboboxes aren't filling in automatically. I still have to use the drop down menu to make a selection. Is there any way that I can make all of my comboboxes autofill if there is only one option left in the drop down? I'd prefer some sort of macro help because that's what I've been using until now, but I'll use VBA if necessary. Thank you for all of your help!

Private Sub cboOne_AfterUpdate()

Me.cboTwo.Requery
If Me.cboTwo.ListCount = 1 Then
With Me.cboTwo
cboTwo.SetFocus
cboTwo.Value = cboTwo.ItemData(0)
End With
End If
End Sub

Private Sub cboTwo_AfterUpdate()

Me.cboThree.Requery
If Me.cboThree.ListCount = 1 Then
With Me.cboThree
cboThree.SetFocus
cboThree.Value = cboThree.ItemData(0)
End With
End If

End Sub

回答1:

The problem may be with misunderstanding Access control events. Unlike in many other languages, control events are rarely triggered by changes made in VBA code. In other words, the event handler cboTwo_AfterUpdate() is not automatically called when cboTwo.Value = cboTwo.ItemData(0) is executed in code, so there will not be any automatic event cascade. Try the following pattern:

Private Sub cboOne_AfterUpdate()
  Me.cboTwo.Requery
  If Me.cboTwo.ListCount = 1 Then
    With Me.cboTwo
      cboTwo.SetFocus
      cboTwo.Value = cboTwo.ItemData(0)
      cboTwo_AfterUpdate
    End With
  End If
End Sub

Private Sub cboTwo_AfterUpdate()
  Me.cboThree.Requery
  If Me.cboThree.ListCount = 1 Then
    With Me.cboThree
      cboThree.SetFocus
      cboThree.Value = cboThree.ItemData(0)
      cboThree_AfterUpdate
    End With
  End If    
End Sub