Validating selections from multiple combo boxes

[OrganMan]

Hi there,

Was wondering if someone could provide me with code or even sample code on how to validate for a user making multiple combo box selections.

I have added 6 additional fields to my Staff table named Staffing1, Staffing2 ...thru Staffing6 which can store one of 4 different shifts of nurses eg: (RN 15-23, LPN 15-23, RN 17-23, LPN 17-23) that I have stored in a lookup table.

I have 6 separate combo boxes on my form and would like to validate so that the same shift of nurse cannot be selected by the user and saved to the table twice on one record.

Thanks for the help, just found this site and it look terrific.

 

[DCrake]

Simple Software Solutions

If you want to do this at the point of selection you will need to create a function that does somthing on the lines of...

Public Function TestDups(Cbo as Integer,Contents As String) AS Boolean
Dim ChkStr as String

Select Case Cbo
    Case 1

         ChkStr= Me.Cbo2  & Me.Cbo3 & Me.Cbo4... & Me.cbo6
    Case 2
         ChkStr= Me.Cbo1  & Me.Cbo3 & Me.Cbo4... & Me.cbo6
    Case 3
         ChkStr= Me.Cbo1  & Me.Cbo2 & Me.Cbo4... & Me.cbo6
    etc
End Select


'This adds all the entries in each of the combo boxes into one long string EXCLUDING the combo box that is calling the function

'Next check to see if it has aready been selected

If Instr(ChkStr,Contents) > 0 then
   TestDups = True
Else
   TestDups = False
End If

 End Function

Then on the OnClick Event of each of the Combo boxes

If TestDups(1,me.ActiveControl) = True Then
    Msgbox "You have already made this selection in another combo box")
    Me.ActiveControl = ""
End If

Where 1 is the first combo box to be checked.

CodeMaster:

 

[OrganMan]

Thanks for such a quick response DCrake, I'll give it a try.

 

[OrganMan]

Hey DCrake,

I now actually have 10 different fields that the user can choose from and from your suggestion here is what I tried:


Public Function TestDups(cbo As Integer, Contents As String) As Boolean
Dim ChkStr As String
'This adds all the entries in each of the combo boxes into one long string EXCLUDING the combo box that is calling the function
Select Case cbo
Case 1
ChkStr = Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 2
ChkStr = Me.cboStaffing & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 3
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 4
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 5
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 6
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 7
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing8 & Me.cboStaffing9 & Me.cboStaffing10
Case 8
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing9 & Me.cboStaffing10
Case 9
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing10
Case 10
ChkStr = Me.cboStaffing & Me.cboStaffing2 & Me.cboStaffing3 & Me.cboStaffing4 & Me.cboStaffing5 & Me.cboStaffing6 & Me.cboStaffing7 & Me.cboStaffing8 & Me.cboStaffing9
End Select

'Next check to see if it has aready been selected
If InStr(ChkStr, Contents) > 0 Then
TestDups = True
Else
TestDups = False
End If
End Function


I also added the code that you suggested on the OnClick event of each combo box.

The error that I get is invalid use of null, any other ideas?

Thanks

 

[DCrake]

Simple Software Solutions

On the OnClick event of each combo you will be passing it an Id [Parameter 1] and the selection made in the combo [Parameter 2]

TestDups(1,"My selection")

Are you allowing the user to to select any combo box or do they have to select them in sequence. Whereby they can only select an item from combo box 2 if they have made a selection from combo box 1?

What may be causing the error is combo boxes with no selections are Null. if this is the case you will need to wrap a Nz(Me.ComboBoxName,"") around each control in your select case statement.

First you need to decide where the error is being generated.

Let me know and I can help you further.

David

 

[OrganMan]

Hi David,

I have scaled it down to 4 combo boxes, when I get it working for them I can upscale to the 10 como boxes that are needed. Yes, the user will have to select on the combo boxes in sequence, eg: cboStaffing, cboStaffing1, cboStaffing3 then cboStaffing4.

In the select case I don't think that I am checking for Nulls properly because I now get the error sub or function not defined and it is highlighting IsNotnul, when I run it.

Here is what I have so far;

Public Function TestDups(cbo As Integer, Contents As String) As Boolean
Dim ChkStr As String
'This adds all the entries in each of the combo boxes into one long string EXCLUDING the combo box that is calling the function
Select Case cbo
Case 1
ChkStr = IsNotnull(Me.cboStaffing2, "") & IsNotnull(Me.cboStaffing3, "") & IsNotnull(Me.cboStaffing4, "")
Case 2
ChkStr = IsNotnull(Me.cboStaffing, "") & IsNotnull(Me.cboStaffing3, "") & IsNotnull(Me.cboStaffing4, "")
Case 3
ChkStr = IsNotnull(Me.cboStaffing, "") & IsNotnull(Me.cboStaffing2, "") & IsNotnull(Me.cboStaffing4, "")
Case 4
ChkStr = IsNotnull(Me.cboStaffing, "") & IsNotnull(Me.cboStaffing2, "") & IsNotnull(Me.cboStaffing3, "")
End Select

'Next check to see if it has aready been selected
If InStr(ChkStr, Contents) > 0 Then
TestDups = True
Else
TestDups = False
End If
End Function

Private Sub cboStaffing_Click()
If TestDups(cboStaffing, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing2_Click()
If TestDups(cboStaffing, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing3_Click()
If TestDups(cboStaffing3, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing4_Click()
If TestDups(cboStaffing4, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub


Thanks for the help David

 

[DCrake]

Simple Software Solutions

Avoid using the IsNotNull and use the Nz() function instead this will give you better results.


also

Private Sub cboStaffing_Click()
If TestDups(cboStaffing, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub

You shold be passing a number here that can be read by the function to determine the correct case event

The number will be a way of telling the function which combobox contents to ignore.

David

 

[OrganMan]

Hi David,

My task is getting closer to being solved, but I still have two issues. I have a table that displays 10 different selections for nurses shifts to choose from when any of my combo boxes are clicked, my table looks as follows:

Nurse_Id

 

[OrganMan]

Hi David,

Sorry for the mistaken post above.

My task is getting closer to being solved, but I still have two issues. I have a table that displays 10 different selections for nurses shifts to choose from when any of my combo boxes are clicked, my table looks as follows:

Nurse_Id Description
1 1RN(15-19)
2 1RN(19-23)
3 1RN(15-23)
4 1RN(19-7)
5 1RN(23-7)
6 1LPN(15-19)
7 1LPN(19-23)
8 1LPN(15-23)
9 1LPN(19-7)
10 1LPN(23-7)

When I run it I can only select the corresponding description for Nurse_id 1 in the first combo box, I can't select anything with the second combobox without getting the messagebox and I can only select the 3rd Nurse_id with the 3rd combo box and so on down to the 10th.

The user may only have to make one selection and just from the first combo box and this can be any of the 10 possible Nurse_Id's.

Here is the code so far:

Public Function TestDups(cbo As Integer, Contents As String) As Boolean
Dim ChkStr As String
'This adds all the entries in each of the combo boxes into one long string EXCLUDING the combo box that is calling the function
Select Case cbo
Case 1
ChkStr = Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 2
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 3
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 4
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 5
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 6
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 7
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 8
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing9, "") & Nz(Me.cboStaffing10, "")
Case 9
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing10, "")
Case 10
ChkStr = Nz(Me.cboStaffing, "") & Nz(Me.cboStaffing2, "") & Nz(Me.cboStaffing3, "") & Nz(Me.cboStaffing4, "") & Nz(Me.cboStaffing5, "") & Nz(Me.cboStaffing6, "") & Nz(Me.cboStaffing7, "") & Nz(Me.cboStaffing8, "") & Nz(Me.cboStaffing9, "")

End Select

'Next check to see if it has aready been selected
If InStr(ChkStr, Contents) > 0 Then
TestDups = True
Else
TestDups = False
End If
End Function

Private Sub cboStaffing_Click()
If TestDups(cboStaffing, Me.ActiveControl) = True Then
MsgBox ("You have already made this selections in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing2_Click()
If TestDups(cboStaffing, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing3_Click()
If TestDups(cboStaffing3, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing4_Click()
If TestDups(cboStaffing4, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing5_Click()
If TestDups(cboStaffing5, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing6_Click()
If TestDups(cboStaffing6, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing7_Click()
If TestDups(cboStaffing7, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing8_Click()
If TestDups(cboStaffing8, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing9_Click()
If TestDups(cboStaffing9, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub
Private Sub cboStaffing10_Click()
If TestDups(cboStaffing10, Me.ActiveControl) = True Then
MsgBox ("You have already made this selection in another combo box")
Me.ActiveControl = ""
End If
End Sub

Thanks David

 

[DCrake]

Simple Software Solutions

Lets be quite explicit on this...

On the Onclick Event on any of the combo boxes you need to have the code to run the function to test for duplicates.

As each combo box has its own name we need to tell the function who has called it. So for example on the CboStaffing combo box it would read as follows.

Private Sub cboStaffing_Click()
If TestDups(1, Me.cboStaffing) = True Then
     MsgBox ("You have already made this selections in another combo box")
     Me.cbostaffing= ""
End If
End Sub

On the CboStaffing2 combo box it would read

Private Sub cboStaffing2_Click()
If TestDups(2, Me.cbostaffing2) = True Then
    MsgBox ("You have already made this selections in another combo box")
   Me.cboStaffing2 = ""
End If
End Sub

As you can see there are 2 items being passed to the function.
Parameter 1 = cbo number
Parameter 2 = The chosen item form the list in the curernt combo

In the TestDups function is uses the Select Case Cbo to identify which combo box is calling the function. What you are doing is to pass the same thing for each parameter.

To test this but a breakpoint on the first line of the function then hover over each of the parameters to see what you are passing to it. Press F8 to advance the code one line at a time to see what is it actually doing.

David.