Prevent duplicate combo box selections on a form.

Using Microsoft Access 2010. I have a form with several combo boxes.  The combo boxes have a list of names (from the same table).  I want it to be impossible to select duplicate names in any off the combo boxes.  I want it to give an error message if duplicate names are selected.  I’ll need a step by step solution to accomplishing this.  Here is the is the list of combo box names

CREW1_LEADER
CREW1_DRIVER
CREW1_FF
CREW2_LEADER
CREW2_DRIVER
CREW2_FF
CREW3_LEADER
CREW3_DRIVER
CREW3_FF
CREW3_FF2
BLS_EMT
BLS_DRIVER
CREW4_LEADER
CREW4_DRIVER
CREW4_FF
CREW4_FF2
ALS_MEDIC
ALS_DRIVER
antonyeo70Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AccessGuy1763Commented:
I really can't think of much of a way to do this without involving VBA, so I hope you're at least somewhat comfortable with that.  If I had to solve this problem, I would add a _Change event to each of the combo boxes and have them call a function which returned True or False based on whether or not the selection had been duplicated.  The trickiest part of the design is that the combo box calling the function shouldn't check itself or it will always return True.

Here's some air code for you:

Private function fcnCheckForDuplicateName(inSelectedName as string, inExcludedComboName as string) as Boolean

   fcnCheckForDuplicateName = False

   If CREW1_LEADER.Column(0)=inSelectedName AND inExcludedComboName<>"CREW1_LEADER" then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_DRIVER.Column(0)=inSelectedName AND inExcludedComboName<>"CREW1_DRIVER" then

      fcnCheckForDuplicateName = True

   End If

   'Continue to add an If statement for all combo boxes
   '...

End Function

Private Sub CREW1_LEADER_Change()

   If fcnCheckForDuplicateName(me.CREW1_LEADER.Column(0),"CREW1_LEADER")=True then

      Call msgbox("Names cannot be selected for more than one field.",vbOkOnly)

      me.CREW1_LEADER = Null

   End If
   
End Sub

Open in new window

0
antonyeo70Author Commented:
Here is what I did and I get when selecting a combo box "runtime error '424' Object required"

Private Function fcnCheckForDuplicateName(inSelectedName As String, inExcludedComboName As String) As Boolean

   fcnCheckForDuplicateName = False

   If CREW1_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW2_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW2_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW2_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW3_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_FF" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_FF2.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_FF2" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW4_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_FF" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_FF2.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_FF2" Then

      fcnCheckForDuplicateName = True

   End If



   If BLS_EMT.Column(0) = inSelectedName And inExcludedComboName <> "BLS_EMT" Then

      fcnCheckForDuplicateName = True

   End If


   If BLS_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "BLS_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If



   If ALS_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "ALS_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


End Function

Private Sub CREW1_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW1_LEADER.Column(0), "CREW1_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_LEADER = Null

   End If
End Sub

Private Sub CREW1_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW1_DRIVER.Column(0), "CREW1_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_DRIVER = Null

   End If
   End Sub

Private Sub CREW1_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW1_FF.Column(0), "CREW1_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_FF = Null

   End If
   End Sub

Private Sub CREW2_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW2_LEADER.Column(0), "CREW2_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_LEADER = Null

   End If


Private Sub CREW2_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW2_DRIVER.Column(0), "CREW2_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_DRIVER = Null

   End If

Private Sub CREW2_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW2_FF.Column(0), "CREW2_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_FF = Null

   End If


Private Sub CREW3_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW3_LEADER.Column(0), "CREW3_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_LEADER = Null

   End If


Private Sub CREW3_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW3_DRIVER.Column(0), "CREW3_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_DRIVER = Null

   End If

Private Sub CREW3_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW3_FF.Column(0), "CREW3_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_FF = Null

   End If

Private Sub CREW3_FF2_Change()

   If fcnCheckForDuplicateName(Me.CREW3_FF2.Column(0), "CREW3_FF2") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_FF2 = Null

   End If


Private Sub CREW4_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW4_LEADER.Column(0), "CREW4_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_LEADER = Null

   End If


Private Sub CREW4_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW4_DRIVER.Column(0), "CREW4_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_DRIVER = Null

   End If

Private Sub CREW4_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW4_FF.Column(0), "CREW4_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_FF = Null

   End If

Private Sub CREW4_FF2_Change()

   If fcnCheckForDuplicateName(Me.CREW4_FF2.Column(0), "CREW4_FF2") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_FF2 = Null

   End If


Private Sub BLS_EMT_Change()

   If fcnCheckForDuplicateName(Me.BLS_EMT.Column(0), "BLS_EMT") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.BLS_EMT = Null

   End If

Private Sub BLS_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.BLS_DRIVER.Column(0), "BLS_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.BLS_DRIVER = Null

   End If


Private Sub ALS_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.ALS_DRIVER.Column(0), "ALS_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.ALS_DRIVER = Null

   End If
   End Sub
0
antonyeo70Author Commented:
Nevermind.  I had a duplicate combo box.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

antonyeo70Author Commented:
Here is the error I'm getting now.
error1.jpg
0
AccessGuy1763Commented:
After looking up a bunch of potential reasons on the Internet, I thought back to what was right in front of my face:

1) Is the routine for the duplicate combobox still in the VBA?  If so, remove it.
2) Check the "Change" event of all the combo boxes to make sure something weird wasn't somehow entered on the property sheet.
0
antonyeo70Author Commented:
Duplicate removed and i checked all the code.  it looks good but still getting an error


-----------

Private Function fcnCheckForDuplicateName(inSelectedName As String, inExcludedComboName As String) As Boolean

   fcnCheckForDuplicateName = False

   If CREW1_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW2_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW2_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW2_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW3_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW4_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_FF1.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_FF1" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_FF2.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_FF2" Then

      fcnCheckForDuplicateName = True

   End If



   If BLS_EMT.Column(0) = inSelectedName And inExcludedComboName <> "BLS_EMT" Then

      fcnCheckForDuplicateName = True

   End If


   If BLS_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "BLS_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If



   If ALS_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "ALS_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


End Function

Private Sub CREW1_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW1_LEADER.Column(0), "CREW1_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_LEADER = Null

   End If


Private Sub CREW1_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW1_DRIVER.Column(0), "CREW1_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_DRIVER = Null

   End If
 

Private Sub CREW1_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW1_FF.Column(0), "CREW1_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_FF = Null

   End If
 

Private Sub CREW2_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW2_LEADER.Column(0), "CREW2_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_LEADER = Null

   End If


Private Sub CREW2_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW2_DRIVER.Column(0), "CREW2_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_DRIVER = Null

   End If

Private Sub CREW2_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW2_FF.Column(0), "CREW2_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_FF = Null

   End If


Private Sub CREW3_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW3_LEADER.Column(0), "CREW3_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_LEADER = Null

   End If


Private Sub CREW3_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW3_DRIVER.Column(0), "CREW3_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_DRIVER = Null

   End If

Private Sub CREW3_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW3_FF.Column(0), "CREW3_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_FF = Null

   End If


Private Sub CREW4_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW4_LEADER.Column(0), "CREW4_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_LEADER = Null

   End If


Private Sub CREW4_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW4_DRIVER.Column(0), "CREW4_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_DRIVER = Null

   End If

Private Sub CREW4_FF1_Change()

   If fcnCheckForDuplicateName(Me.CREW4_FF1.Column(0), "CREW4_FF1") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_FF1 = Null

   End If

Private Sub CREW4_FF2_Change()

   If fcnCheckForDuplicateName(Me.CREW4_FF2.Column(0), "CREW4_FF2") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_FF2 = Null

   End If


Private Sub BLS_EMT_Change()

   If fcnCheckForDuplicateName(Me.BLS_EMT.Column(0), "BLS_EMT") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.BLS_EMT = Null

   End If

Private Sub BLS_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.BLS_DRIVER.Column(0), "BLS_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.BLS_DRIVER = Null

   End If


Private Sub ALS_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.ALS_DRIVER.Column(0), "ALS_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.ALS_DRIVER = Null

   End If
   End Sub
--------
0
AccessGuy1763Commented:
I don't know if you copied this directly out of your editor, but if so you are missing a whole bunch of End Subs
0
antonyeo70Author Commented:
I did.  Are the End Subs supposed to be after each "End if"?
0
AccessGuy1763Commented:
Yes exactly... basically one for each Private Sub comboboxName_Change() declaration.
0
antonyeo70Author Commented:
I fixed the "End Sub" but I get the same error message

--------------

Private Function fcnCheckForDuplicateName(inSelectedName As String, inExcludedComboName As String) As Boolean

   fcnCheckForDuplicateName = False

   If CREW1_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW1_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW1_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW2_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW2_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW2_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW2_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW3_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW3_FF.Column(0) = inSelectedName And inExcludedComboName <> "CREW3_FF" Then

      fcnCheckForDuplicateName = True

   End If



   If CREW4_LEADER.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_LEADER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_FF1.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_FF1" Then

      fcnCheckForDuplicateName = True

   End If


   If CREW4_FF2.Column(0) = inSelectedName And inExcludedComboName <> "CREW4_FF2" Then

      fcnCheckForDuplicateName = True

   End If



   If BLS_EMT.Column(0) = inSelectedName And inExcludedComboName <> "BLS_EMT" Then

      fcnCheckForDuplicateName = True

   End If


   If BLS_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "BLS_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If



   If ALS_DRIVER.Column(0) = inSelectedName And inExcludedComboName <> "ALS_DRIVER" Then

      fcnCheckForDuplicateName = True

   End If


End Function

Private Sub CREW1_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW1_LEADER.Column(0), "CREW1_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_LEADER = Null

    End If
   End Sub


Private Sub CREW1_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW1_DRIVER.Column(0), "CREW1_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_DRIVER = Null

     End If
   End Sub
Private Sub CREW1_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW1_FF.Column(0), "CREW1_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_FF = Null

  End If
   End Sub
Private Sub CREW2_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW2_LEADER.Column(0), "CREW2_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_LEADER = Null

    End If
   End Sub
Private Sub CREW2_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW2_DRIVER.Column(0), "CREW2_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_DRIVER = Null

    End If
   End Sub
Private Sub CREW2_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW2_FF.Column(0), "CREW2_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW2_FF = Null

    End If
   End Sub
Private Sub CREW3_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW3_LEADER.Column(0), "CREW3_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_LEADER = Null

   End If
   End Sub
Private Sub CREW3_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW3_DRIVER.Column(0), "CREW3_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_DRIVER = Null

    End If
   End Sub
Private Sub CREW3_FF_Change()

   If fcnCheckForDuplicateName(Me.CREW3_FF.Column(0), "CREW3_FF") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW3_FF = Null

     End If
   End Sub
Private Sub CREW4_LEADER_Change()

   If fcnCheckForDuplicateName(Me.CREW4_LEADER.Column(0), "CREW4_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_LEADER = Null

    End If
   End Sub
Private Sub CREW4_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.CREW4_DRIVER.Column(0), "CREW4_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_DRIVER = Null

    End If
   End Sub
Private Sub CREW4_FF1_Change()

   If fcnCheckForDuplicateName(Me.CREW4_FF1.Column(0), "CREW4_FF1") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_FF1 = Null

   End If
   End Sub
Private Sub CREW4_FF2_Change()

   If fcnCheckForDuplicateName(Me.CREW4_FF2.Column(0), "CREW4_FF2") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW4_FF2 = Null

    End If
   End Sub
Private Sub BLS_EMT_Change()

   If fcnCheckForDuplicateName(Me.BLS_EMT.Column(0), "BLS_EMT") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.BLS_EMT = Null

    End If
   End Sub
Private Sub BLS_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.BLS_DRIVER.Column(0), "BLS_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.BLS_DRIVER = Null

     End If
   End Sub
Private Sub ALS_DRIVER_Change()

   If fcnCheckForDuplicateName(Me.ALS_DRIVER.Column(0), "ALS_DRIVER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.ALS_DRIVER = Null

   End If
   End Sub
0
AccessGuy1763Commented:
Ok I have a bad feeling that you attempted to type in every single routine rather than going to each combobox individually and adding the routines.  I think this has resulted in one or more of the Event procedures being screwed up.

Let's step back a second.  Open up Notepad.  Copy everything out of your Module for the form and Paste it into Notepad for safe keeping.  Now delete everything from the module and save it.  Next, paste ONLY the code for fcnCheckForDuplicateName into the module.  Pick ONE of the comboboxes, select it, pull up the Property sheet, and go to the Event tab.  Next to the "On Change" event, click the three ellipses and select "Code Builder".  This should automatically generate the "Private Sub cmboboxname_Change()" and "End Sub" lines for you.  Paste the If statement inside.  Now pull up Form View and test the combobox you just entered the procedure for.  Let me know if it generates an error.
0
antonyeo70Author Commented:
ok working on it...thanks for the clarification.
0
antonyeo70Author Commented:
ok I tried it with just one combo box but go the same error. Here is what went into the combo box property the way you explained.  I put all the other code fcnCheckForDuplicateName into the module.


--------------

Private Sub CREW1_LEADER_Change()
If fcnCheckForDuplicateName(Me.CREW1_LEADER.Column(0), "CREW1_LEADER") = True Then

      Call MsgBox("Names cannot be selected for more than one field.", vbOKOnly)

      Me.CREW1_LEADER = Null

    End If
End Sub
0
AccessGuy1763Commented:
Can you attach the most current version you have please?

I want to run it on my PC to see if I can figure out what's going on.
0
antonyeo70Author Commented:
here you go.
Roster.accdb
0
AccessGuy1763Commented:
Ok I've got it.  The form has some corruption going on.

I did get it working by importing all of the objects into a new database file (create a new database then go to External Data > Import > Access and follow the screens from there).  That should work for you also and then you can go about adding the code, but once corruption has been introduced I can't make any guarantees that simply importing the objects into a new database has "fixed" the problem forever.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.