Populate Combobox from Listbox Results

[WillM]

Hello,
I have a list of staff that have a conflict of interest with a particular entity. As a result, these staff are not allowed to interview these entities.

I have a query that matches all staff with their respective entities that they have a conflict of interest with (CoI) and that is functioning correctly.

When the form loads to add an interview, there is a listbox that pulls all the people who are not allowed to do an interview with that particular entity. That is also working correctly.

I have a subform, that is a continuous form, which will allow the user to add staff, one at a time, via a drop down box. These people are stored in their own table with a FK Id to the interview table. This also works correctly.

What I cannot figure out is how to filter the combobox on the subform to exclude the people in the listbox.

Here is what I have tried, loosely based on what I have found on Google and researching here. I am 100% sure it is not working correctly, but I've no idea what I am missing.

The query the listbox is based on has 3 colums, the ID, the Name, and the business contract number.

Private Sub Form_Load()
Dim strSource As String
Dim i As Integer

For i = 0 To Me.lstCoI.ListCount - 1
strSource = "SELECT [staff] FROM lutStaff WHERE Staff <> '" & Forms!frmScheduleInterview.lstCoI.Column(1, i) & "'"

Debug.Print strSource

Next i

Me.subfrmMembers.Form!Staff.RowSource = strSource

End Sub

In the immediate window, I get the following result:
SELECT [staff] FROM lutStaff WHERE Staff <> name1
SELECT [staff] FROM lutStaff WHERE Staff <> name2
SELECT [staff] FROM lutStaff WHERE Staff <> name3
SELECT [staff] FROM lutStaff WHERE Staff <> name4
SELECT [staff] FROM lutStaff WHERE Staff <> name5
SELECT [staff] FROM lutStaff WHERE Staff <> name6

The issue is that the box is not filtering all the names out of the list it is built on. It is only filtering out the last name.

Obviously I need to save the results for comparison, but I am at a loss on how to do that.

Thanks for any help,
Will

 

[CJ_London]

with your loop, strSource1 is constantly being overwritten until the last one

Not totally clear what you are trying to do (or at the least, the way you are doing it but try

strSource1 = "SELECT [staff] FROM lutStaff WHERE Staff IN ("
For i = 0 To Me.lstCoI.ListCount - 1
    strSource1 = strSource1 & "'" & lstCoI.Column(1, i) & "',"
next i
 strsource1=left(strsource1,len(strsource1)-1) & ")"
Debug.Print strSource

Note you don't need to form reference if the lstCoI control is in the same form

 

[vbaInet]

I have a subform, that is a continuous form,

Won't work on a continuous form or a form in datasheet view.

There's a way around it but it's a bit convoluted.

 

[WillM]

with your loop, strSource1 is constantly being overwritten until the last one

Not totally clear what you are trying to do (or at the least, the way you are doing it but try

strSource1 = "SELECT [staff] FROM lutStaff WHERE Staff IN ("
For i = 0 To Me.lstCoI.ListCount - 1
    strSource1 = strSource1 & "'" & lstCoI.Column(1, i) & "',"
next i
 strsource1=left(strsource1,len(strsource1)-1) & ")"
Debug.Print strSource

Note you don't need to form reference if the lstCoI control is in the same form

That gives me the contents of the listbox in the combobox. What I need is the opposite, I need everything BUT those items in the listbox to populate the combobox.

 

[MarkK]

try . . .

strSource1 = "SELECT [staff] FROM lutStaff WHERE Staff [B][COLOR="Red"]NOT[/COLOR][/B] IN ("

 

[WillM]

That did it! Thank you very much!!!

 

[vbaInet]

Won't work on a continuous form or a form in datasheet view.

There's a way around it but it's a bit convoluted.

If it's a one off change to the Row Source then you're fine, but if you want to do it on the fly per record then it won't work as you expect. What if your subform's Record Source contains existing records that require values that are both IN() and NOT IN()?

 

[WillM]

vbaInet

The process is that they select a contract number on a different from, it opens the form with the above information which is only tied to that particular contract number, they cannot change the record or do anything other than select team members in the combo box subform then they must close the form.

Edit: There will never be a time, in this listbox to combo box realtionship, that there will be both IN and NOT IN.

This shouldn't present a problem, hopefully?

 

[vbaInet]

Ok, makes sense. You should be fine.

 

[CJ_London]

@Markk - thanks for picking that up - I was in too much of a rush to finish before dinner

 

[MarkK]

@CJ, omg, you did all the actual work. Cheers,