-1

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
CBeachBum
  • 1
  • 4
  • 1
    "isn't working" isn't a question. What is the *specific* problem? Did you try debugging? Breakpoints? Inspecting locals? – Mathieu Guindon Sep 12 '17 at 19:59
  • Please excuse me, I am an absolute beginner with VBA and did not word that correctly. The second combobox will autofill with the last available option. The rest of the comboboxes that I entered the same code for are not autofilling. I still have to use the drop down menu to make my selection. I have not tried and of the troubleshooting steps you listed above because I have no clue what they mean. I will try to figure those out though and get back to you. – CBeachBum Sep 12 '17 at 20:31
  • 1
    Please [edit] your question to include this (and any other relevant) information. – Mathieu Guindon Sep 12 '17 at 20:32
  • @CBeachBum You are not required to be an expert coder, but if someone asks for more information, the best thing to do is actually edit the question and add more detail in a timely manner. Stack Overflow is a great resource if you are willing to take time to improve your own posts. The pace is also usually faster pace... most question answered within the same day if not the same hour. If you take too long to "get back", the question is likely to become stagnant. – C Perkins Sep 13 '17 at 04:19
  • @CPerkins Thank you for the advice. I am using this at work and had other tasks to be completed. Therefore, I couldn't respond instantly. I appreciate everyone's assistance though. – CBeachBum Sep 13 '17 at 14:33
  • @CBeachBum I understand and there must be no rush. It is just that I see many first-time posters who do not properly respond to feedback, so I fear they never get a good answer. We're still ready to help. Did you try adding explicit calls to the `????_AfterUpdate()` subprocedures? – C Perkins Sep 13 '17 at 17:23

1 Answers1

0

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
C Perkins
  • 3,733
  • 4
  • 23
  • 37
  • The suggested code did not work for me. I really just don't understand VBA coding at all. I've just been using macros for everything until now. I'm so sorry to have wasted your time. I'm just getting frustrated with it and need to think of an alternative method. – CBeachBum Sep 14 '17 at 19:07
  • The ComboBox.RowSource queries are probably not sufficient, but you have not shared any details about those. I could only assume that calling Requery on each ComboBox would work. Please edit your question (not in the comments) with the RowSource query for a couple of the comboboxes. Your concerns are likely not just with VBA coding, but I guess probably with the Access event model. It is not always intuitive or like other system, that's why I preceded my code answer with a brief comment about that. – C Perkins Sep 14 '17 at 19:31