Duplicates and Combo boxes 1

[rookery]

I have a Form with 15 combo boxes on it. They all have the same table as a RowSource.

Is there any way to stop a user from selecting the same field in more than one combo box other than perhaps writing some code in the AfterUpdate event of each box that checks all the other boxes values, once a choice has been selected?

 

[danvlas]

Or set the Hide Duplicates property of the text boxes on the report to Yes

Dan

 

[rookery]

Dan
thanks for your reply but I'm dealing with Combo boxes not Text boxes. Also was unable to find a Hide Duplicate propety on either control or the Form.

Any other ideas???

 

[oharab]

Put this function in a module somewhere.

Public Function CheckCombos(ctl As Access.Control) As Boolean
'checks all the controls on frm for the integer itest.

Dim ctlCheck As Access.Control
CheckCombos = False
For Each ctlCheck In ctl.Form.Controls
'loop thru all the controls on frm
If TypeOf ctlCheck Is ComboBox And ctlCheck.name <> ctl.name Then
'if it's a combo and not the one we are checking from then check the value
If ctlCheck.Value = ctl.Value Then
'if the values match then set the function to true and stop checking
CheckCombos = True
Exit Function
End If
End If
Next ctlCheck
End Function


For each combobox on your form put this code on the BeforeUpdate event,


If CheckCombos(Me.Combo0) = True Then
Cancel = True
MsgBox &quot;You can't do that.&quot;
Me.Undo
End If


don't forget to change me.combo0 to whatever combo you are checking from.
Now every time you change the value of a combo box, it loops through all of the combo boxes and checks to see if the value of the current box is unique. If not then it lets the user know and stops the update.

HTH

Ben ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[oharab]

Found a mistake already:
change
For Each ctlCheck In ctl.Form.Controls

to

For Each ctlCheck In ctl.Parent.Controls



B
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[danvlas]

This is absolutely funny! I replied to a totally different post than yours! Forum software problems???



Dan

 

[rookery]

Why is it always so easy when you know how! Ben you are a STAR!! Cheers mate.