Array/combo box problem, Please Help (1 Viewer)

[davidbui]

Please can some kind person tell me how to make a feature on my form where a user is cannot enter the same value in different combo boxes.

E.g. I have 8 combo boxes called Degree 1,2,3 etc...... all linked to the same table, but i don't want a user to be able to select a value in more than one combo box.

As i couldn't solve this problem, i have tried a different method, of using a validate button to check for any repeating values. Even this doesn't work as the conditions compares null values. The code for this is below;

For txtCount1 = 0 To 7
For txtCount2 = 0 To 7
If txtCount1 <> txtCount2 Then 'If Values of two array elements are the same then next loop
If txtValues(txtCount1) = txtValues(txtCount2) Then 'If Values are the same then build then build msgbox value
textString = textString & "Error - Value in Combo_" & txtCount1 + 1 & " is the same as the Value in Combo_" & txtCount2 + 1 & ""
textString = textString & vbCrLf
End If
End If
Next txtCount2
Next txtCount1

If textString <> "" Then ' If there are errors display message
MsgBox textString
Else 'Display No Errors
MsgBox "No Errors"
End If
End Sub

If anyone out there can tell me a solution to either problem i will be very grateful.

 

[jjturner]

Why don't you validate for each combobox value on their respective BeforeUpdate events, then if the user has repeated a value, warn the user with the MsgBox, then set 'Cancel=True' to undo the user's value.

You will only have to iterate through 1 loop this way (on a combobox by combobox basis). To avoid having to re-type the same loop iteration for all 8 combo boxes, you might want to create a function that can be called from each BeforeUpdate event which returns a Variant ('False' if no match, or the matched combobox 'name' if there is a match). Then evaluate on each BeforeUpdate event with 'If functionname(presentcomboboxname.value) <> False Then create and display your Msgbox and set Cancel = True'

I'm paraphrasing, but that should work!

Oh, and if you want to allow 'Null' values, just test for this in your function with the 'IsNull' function.

HTH,
John

 

[davidbui]

Thanks for your response John.

I have now managed to get my for loops to work and compare all 8 combo boxes and ignore null values (The loop works from clicking a 'Save' button.

Although I now have a new problem that I hope you can help me with. When the loop picks up any errors, it is still possible to save the record. I was wondering if there is a way to stop this from occuring?

Another problem with the loop is that it picks up errors twice. For example if it has found matching values for Degree 1 & Degree 2 it lists the error message two times. This isn't really a problem, it is more of a cosmetic thing that i would like resolved.

If you or anyone can come up with a solution to the problems listed above, please post a reply as I am getting very annoyed with access.

 

[jjturner]

Davidbui,

Anticipating your 'Save' problem, I was indirectly suggesting that you be a bit more pro-active and catch the duplication at the point of insertion.

Even though you have your code semi-functional at this point, I still recommend this approach. Doubly so, since your nested double loop is what is causing your MsgBox to appear twice, whereas my solution would only have you loop once through the combobox controls.

I would be happy to walk you through it should you decide to re-design.

Regards,
John

 

[davidbui]

I would be very grateful if you would walk me through your suggested method. I am new to using VBA and i am finding it very annoying, so any help is much obliged.

Many thanks,
David

 

[jjturner]

Possible Solution

Here's the what I came up with for your function:

Function CheckForDuplication(strComboName As String, _
            varComboValue As Variant) As Variant
'   This function takes 2 arguments - the control's name and value.
'   It will return either 'False' or the string referencing which
'   combo box already has the value which is being passed to this function.

Dim i           As Integer
Dim ReturnStr   As String

If Not IsNull(varComboValue) Then
    For i = 1 To 8
        If "Degree " & i <> strComboName Then
            If Me.Controls("Degree " & i).Value = varComboValue Then
                ReturnStr = "Combo " & i
                Exit For
            End If
        End If
    Next i
End If

'Now assign a returnvalue for the function
If ReturnStr = "" Then
    CheckForDuplication = False
Else
    CheckForDuplication = ReturnStr
End If

End Function

Put this function in your form's module and then for each combobox put the following code on your BeforeUpdate event:

Private Sub Degree_1_BeforeUpdate(Cancel As Integer)

Dim varReturnValue    As Variant

varReturnValue = CheckForDuplication("Degree 1", Me.Degree_1.Value)

If varReturnValue <> False Then
    MsgBox "Error - Value in Combo 1 is the same as Value in " _
        & varReturnValue
    Cancel = True
End If

End Sub

Note that you'll have to put the correct index # on each of your BeforeUpdate events for the control name.

Let me know how this works out for you!

HTH,
John

 

[davidbui]

Hello John,

Thanks for the solution to my problem. Just to let you know it worked perfectly and you have saved me a lot of grief!

Can't thank you enough for your help

Regards
David Bui

 

[jjturner]

Glad to hear it - hope all goes well from here!