removing from combo box

[rudeman76]

hello,

I have 6 combo-boxes on a form. They are all populated by the same query. They are for the user to select the 6 employees that are on shift. Is there a way to remove the employee from the rest of the combo boxes once their name has been selected? I just want to make sure the user does not enter the same employee twice.

Andrew

[rudeman76]

Hi,
I have been playing around with this. I have the Combo-boxes bein populated from a table using the AddItem. I put this in the gotfocus of each combo box. Once it populates the combo-box, I use the removeItem to remove the other combo-boxes. It is working ok. Problem now is when I created the table, I had the employees entered in as "Lastname, Firstname". The comma in the middle is putting the firstname on the next line.
So rather than getting:

Lastname, Firstname

I am getting

Lastname
Firstname

Is there a way I can get it not to do this or do I have to have two seperate columns in my employee table, one for first name, and one for last name?

Andrew

[ADezii]

Hi,
I have been playing around with this. I have the Combo-boxes bein populated from a table using the AddItem. I put this in the gotfocus of each combo box. Once it populates the combo-box, I use the removeItem to remove the other combo-boxes. It is working ok. Problem now is when I created the table, I had the employees entered in as "Lastname, Firstname". The comma in the middle is putting the firstname on the next line.
So rather than getting:

Lastname, Firstname

I am getting

Lastname
Firstname

Is there a way I can get it not to do this or do I have to have two seperate columns in my employee table, one for first name, and one for last name?

Andrew

You can create a Calculated Field in a Query, let's call it Name_No_Comma, that removes the comma from the String, as in:
[CODE=text]Name_No_Comma: Left$([Name],InStr([Name],",")-1) & " " & Right$([Name],Len([Name])-InStr([Name],",")-1)[/CODE]
Flintstone, Fred now becomes ==> Flintstone Fred

[rudeman76]

thanks, that worked awesome.
I am now getting an error when I use the removeitem. It tells me the name selected is not in the list.

    If IsNull(Combo25.Value) Then
        Rst.MoveFirst
        Do While Not Rst.EOF
            If Rst!position = "Security" Then
                str = Rst!employee
                x1 = Left$([str], InStr([str], ",") - 1) & " " & Right$([str], Len([str]) - InStr([str], ",") - 1)
                Combo25.AddItem x1
            End If
            Rst.MoveNext
        Loop
    End If
    
    If Not IsNull(Combo29.Value) Then
        Combo25.RemoveItem Combo29.Value
    End If
    If Not IsNull(Combo27.Value) Then
        Combo25.RemoveItem Combo27.Value
    End If
    If Not IsNull(Combo31.Value) Then
        Combo25.RemoveItem Combo31.Value
    End If
    If Not IsNull(Combo45.Value) Then
        Combo25.RemoveItem Combo45.Value
    End If
    If Not IsNull(Combo47.Value) Then
        Combo25.RemoveItem Combo47.Value
    End If

I have the same code (combo## have been changed for each) for all 6 boxes (i dont know if i did this right). It works for most of them but not all of them, and I cant figure out why. It works the first time it you use the combobox, but some of them when you go back to the same combobox, it gives the error that the name selected in the other combobox is not in the list.

Andrew

[ADezii]

thanks, that worked awesome.
I am now getting an error when I use the removeitem. It tells me the name selected is not in the list.

    If IsNull(Combo25.Value) Then
        Rst.MoveFirst
        Do While Not Rst.EOF
            If Rst!position = "Security" Then
                str = Rst!employee
                x1 = Left$([str], InStr([str], ",") - 1) & " " & Right$([str], Len([str]) - InStr([str], ",") - 1)
                Combo25.AddItem x1
            End If
            Rst.MoveNext
        Loop
    End If
    
    If Not IsNull(Combo29.Value) Then
        Combo25.RemoveItem Combo29.Value
    End If
    If Not IsNull(Combo27.Value) Then
        Combo25.RemoveItem Combo27.Value
    End If
    If Not IsNull(Combo31.Value) Then
        Combo25.RemoveItem Combo31.Value
    End If
    If Not IsNull(Combo45.Value) Then
        Combo25.RemoveItem Combo45.Value
    End If
    If Not IsNull(Combo47.Value) Then
        Combo25.RemoveItem Combo47.Value
    End If

I have the same code (combo## have been changed for each) for all 6 boxes (i dont know if i did this right). It works for most of them but not all of them, and I cant figure out why. It works the first time it you use the combobox, but some of them when you go back to the same combobox, it gives the error that the name selected in the other combobox is not in the list.

Andrew

I'm having a little trouble following your logic. Unless the same Name exists in all the Combo Boxes, you are not removing it from them, but the actual value of the Combo Box. If these are only single column Combos, then the value is the Name, but if not, you are removing the Item by its Index. I'm probably just missing the point, but explain in a little more detail if you can.