vba - looping over specific form controls

Question

I have an access form with like 5 dropdowns boxes, I need to check if the values of the dropdown boxes are unique before I approve the form. I can iterate over all the form elements but I want to be able to iterate only over dropdowns:

---------------------------------------------------------------------- |Id1 [dropdown values] Id2 [Dropdown values] Id3 [Dropdown Values] | | | | CollectionTask [Another dropdown] | | []This is a checkbox | | [Approve Button] [clear myForm btn] | ---------------------------------------------------------------------- 'iterating over the form elements On Error Resume Next Dim ctl As Control For Each ctl In Me.Controls 'do something only for dropdown controls of form Next

Instead of iterating over everything, I want to iterate over only the dropdown types. I feel like there should be a way to do it. I am working around this using the technique below but that's a lot of code.

'this code will do the trick but that means I will have to write a condition for every id 'which for me is lots of code (not being lazy but I think iterating over the form elements will be more efficient If (id1.Value = id2.Value) Or (id1.Value = id3.Value) Or (id1.Value = id4.Value) then Msgbox "make sure you are not duplicating ids" End if

HansUp · Accepted Answer · 2015-06-23 16:13:45Z

With the following code you can check all controls with the ControlType of acComboBox. Basically it just adds the ComboBox's value into an Dictonary checking each time to see if the value already exists in the Dictonary. You just need to determine what you want to happen if the value is already set in another ComboBox.

This is freehand code but I am fairly certain everything is correct:

 Private Sub CheckCombos() Dim ctl As Control Dim dict As Variant Set dict = CreateObject("Scripting.Dictionary") For Each ctl In Me.Controls If ctl.ControlType = acComboBox Then If Nz(ctl.Value,"") <> "" Then If dict.Exists(ctl.Value) Then Msgbox "Combobox: " & ctl.Name & " has the same value as: " & dict(ctl.Value) Else dict.Add ctl.Value, ctl.Name End If Else Msgbox "Empty Combobox" 'Handle exit End If End If Next End Sub

Collectives™ on Stack Overflow

vba - looping over specific form controls

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related