Compare values between combobox and listbox 1

[Ntelos]

Hi there!
I have a combo box and a listbox.
All i want is when i select a value(text) from the combobox to check if there is a same value(same text) at the listbox and if there is, then this value will be removed from the listbox.
I tried at after update combobox this:
Dim lnI As Integer

For lnI = 0 To listbox_name.ListCount - 1
If listbox_name.ListIndex(lnI) = combobox_name.Value Then
listbox_name.RemoveItem (lnI)
End If

Next lnI

but its not working

Any ideas?

Thank you a lot!

 

[MajP]

Is the listbox based on a value list? If not the remove item will not work.

You can run the following code in the form's on open event and it will convert the query rowsource into a value list.

Private Sub convertToValueList(theListBox As Access.ListBox)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fldField As DAO.Field
  Dim strLstValue As String
  Dim intColCount As Integer
  Dim intColCounter As Integer
  Dim intRowCounter As Integer
  If theListBox.RowSourceType = "Table/Query" Then
    intColCount = theListBox.ColumnCount
    strSql = theListBox.RowSource
    theListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    theListBox.RowSourceType = "Value List"
    Do While Not rs.EOF
       For intColCounter = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(intColCounter), " ")) & """;"
       Next intColCounter
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       theListBox.AddItem (strLstValue)
       strLstValue = ""
    Loop
 End If
End Sub

 

[Ntelos]

The combo box has rowsource from a query which selects all values from field surname of table1. The same happens with the listbox too. I want, when i select a surname from the combobox then this surname to be removed from the list, but i want to do this by the way i described at my first post.

i will try your code and let you know.

Thank you really much for your help

 

[MajP]

Yes as I said, you would have to use my code or remove the item from the list by changing the query.

This method (removeitem) is only valid for list box or combo box controls on forms. Also, the RowSourceType property of the control must be set to "Value List".

 

[Ntelos]

So, all i have to do is use this code at the open event at my listbox and at the code to replace the "theListBox" with my listbox name.
After that, my control will be converted to value list and then i could use my code?
If not, how this works?

Sorry if i am asking stupid questions but i am an amateur at visual basic, so i need some extra help

 

[MajP]

Sorry.
1. Place the above code in a standard module.
2. Leave your listbox as is, with a rowsource query.
3. In the forms on open event place this code
call convertToValueList(me.yourListBoxName)

What happens is you pass a reference to the listbox. It creates a recordset based on your list box row source. It reads through all the items, and builds a string. So if you had a query that returned
Cat
Dog
Mouse
Bird

it builds a string
Cat;Dog;Mouse;Bird
and makes it the rowsource.

 

[MajP]

Can I ask what is the final goal that you are trying to accomplish? There may be some better interfaces to do this.

 

[Ntelos]

I now can understand your code and it seems pretty cool!
i will try this and let you know.
My concept is this:
I have a table called Employee with fields like ID, name, surname etc.
Lets say that i have at about 40 posts at my company, to which i have to assign a job to each employee(i.e. Josephson goes to post1, Anderson to post2 etc). When all posts are filled with employees, then the remaining employees can go out and have fun.
So, my skeptic is to have a listbox where at the first stage all employees appear.
At my form, i have labels with all posts and besides each label i have combo boxes which return from a query the surnames of the employees - so, for each post you can select one employee.
I want 2 things:
1) when i select an employee, this surname to disappear from the rest combo boxes so someone cannot be mistaken and chooses one employee for 2 posts.
2)At the end, when all posts are filled in with employees, at the list there will be the rest employees that will go and have fun.
So, i started to write queries at each combo box and at the listbox, like:
SELECT table1.surname
FROM table1
WHERE (((table1.surname) Not In (nz([Forms]![my_form]![combobox1]),nz([Forms]![my_form]![combobox2])etc
and at after update of each combobox's and listbox's i added code to requery all the comboboxes and the listbox.

this worked just fine, but i have lots of combo boxes.
as a result, i get a message at the rowsource query that i exceed the 1024 character limit.
So, i thought that the comboboxes that i cannot include at the rowsource to control them by the way i asked you for.

 

[Ntelos]

I put the function code at my form and then when i try to open the form i get the message:


Runtime error "3061".

Too few parameters. It there should be 38.
and when i click on debug button i have the

Set rs = CurrentDb.OpenRecordset(strSql)

highlighted

 

[MajP]

before the above line put the following

debug.print "SQL: " & strSql
Then post the sql string from the immediate window.

It does not like the rowsource sql string. By chance did you take out the original rowsource?

 

[MajP]

This is probably a better way that I use. It provides a lot of flexibility.
1)make a table "tblSelected"
tblSelected
selectedID (PK, autonumber)
numFK (FK, long if the table uses a numeric PK)
strFK (FK, text if the table uses a text PK)

This table is where you store a list of the selected items. So if you have employees with numeric employee IDs then you would store the selected employee ID in the numFK field. If the employee ID is text then store the selected emp ID in the strFK.

This can be modified, but I wrote the code like this to reuse often.

2) So if you select an item you add its key to the tblSelected, if you unselect the person you remove them from tbl selected.

Private Sub Form_Load()
  call ClearSelected
End Sub

Public Sub clearSelected()
  Dim strSql As String
  strSql = "Delete * from tblSelected"
  DoCmd.SetWarnings (False)
  DoCmd.RunSQL strSql
  DoCmd.SetWarnings (True)
End Sub

Public Sub insertData(varData As Variant, primaryKeyType As String)
  Dim strSql As String
  If primaryKeyType = "Text" Then
      strSql = "insert into tblSelected (strFK) values('" & varData & "')"
  Else
      strSql = "insert into tblSelected (numFK) values(" & varData & ")"
  End If
  DoCmd.SetWarnings (False)
  DoCmd.RunSQL strSql
  DoCmd.SetWarnings (True)
End Sub

Public Sub deleteData(varData As Variant, primaryKeyType As String)
  Dim strSql As String
  If primaryKeyType = "Text" Then
      strSql = "delete * from tblSelected where strFK = '" & varData & "'"
  Else
      strSql = "delete * from tblSelected where numFK = " & varData
  End If
  DoCmd.SetWarnings (False)
  DoCmd.RunSQL strSql
  DoCmd.SetWarnings (True)
End Sub

So then my queries become something like
"Unselected people"
SELECT
tblScouts.autoScoutID,
tblScouts.strFirstName,
tblScouts.strLastName
FROM
tblScouts
WHERE
(((tblScouts.autoScoutID) Not In (select numFK from tblSelected)));

"Selected People"
SELECT
tblScouts.autoScoutID,
tblScouts.strFirstName,
tblScouts.strLastName
FROM
tblScouts
WHERE
(((tblScouts.autoScoutID) IN (select numFK from tblSelected)));

The above code and table is generic and can be very easily reused.

http://www.4shared.com/file/225302807/56e4de72/ToFromList.html

The above demo uses this concept to build a To-->From listbox like you would have in a wizard allowing you to move items between lists.