CODEWITHSUNDEEP
vbams-accesscombobox
methuselah
methuselah

Reputation: 13216

MS Access cannot find the field combobox mentioned in youe expression

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

Answers (1)

PaulFrancis
PaulFrancis

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

Related Questions