Reputation: 13216
I am trying to write a quick piece of VBA code that will check to see if 2 combobox values match in a particular range, and if they do it should return an error message.
What I found was that my code was failing when it compared cboOption1.Column(0)
to cboOption1.Column(0)
. So I added in the extra line And Not Me.Controls("cboOption" & i) = Not Me.Controls("cboOption" & k)
to cancel out any comparisons a combobox would do against itself. But now I am getting the error message that MS Access cannot find the field combobox mentioned in my expression at this line. Any idea what is going wrong?
Dim myArray As Variant
Dim x As Integer
myArray = Array(cboOption1.Column(0), cboOption2.Column(0), cboOption3.Column(0), cboOption4.Column(0))
For x = LBound(myArray) To UBound(myArray)
For i = 1 To 4
For j = 1 To 4
If Me.Controls("cboOption" & i).Column(0) = myArray(x) _
And Me.Controls("cboOption" & j).Column(0) = myArray(x) _
And Not Me.Controls("cboOption" & i) = Not Me.Controls("cboOption" & k) Then
MsgBox "You cannot select " & myArray(x) & " twice."
Exit Sub
End If
Next
Next
Next x
Upvotes: 0
Views: 109
Reputation: 5809
How about a basic chek if i = j
before going into the value comparision?
Dim myArray As Variant
Dim x As Integer
myArray = Array(cboOption1.Column(0), cboOption2.Column(0), cboOption3.Column(0), cboOption4.Column(0))
For x = LBound(myArray) To UBound(myArray)
For i = 1 To 4
For j = 1 To 4
If i <> j Then
If Me.Controls("cboOption" & i).Column(0) = myArray(x) _
And Me.Controls("cboOption" & j).Column(0) = myArray(x) Then
MsgBox "You cannot select " & myArray(x) & " twice."
Exit Sub
End If
End If
Next
Next
Next x
Upvotes: 1