Refering to listbox value

[pm4698]

Hello there!
I have a list box and a combo box.
I have for row source the same field of array at both the list box and the combobox.

All i want is, when i select a value from the combo box, the same value from the list box to disappear.

I have written a piece of code at list box:
SELECT table1.surname from table1 where table1.surname Not In (nz([Forms]![my_form]![combobox_name]), etc
and i have writter at after update of combo box, the code:
me.listbox_name.requery.

This works just fine, but i have lots of combo boxes and i get an sql message that i have passed way the limit of 1024 characters of code, so i am looking to another way to settle the other combo boxes.

I was thinking a sample code at after update of a combo box like:
if me.listbox_name.rowsource = me.combobox_name.value then
me.listbox_name.rowsource = ""
me.listbox_name.requery

but it doesn't work.

Does anyone know how could i declare this statement correctly so i can get this work?

Thanks in advance

 

[pm4698]

I am trying an alternative code at after update combo box, like this:

Dim i As Integer
For i = listbox_name.ListCount - 1 To 0 Step -1
If listbox_name.Value(i) = combobox_name.Value Then
listbox_name.Value(i) = ""
End If
Next i

but i have a mismatch message.

 

[gemma-the-husky]

what queries/row sources are your list box/combo box based on.

(dont use value lists - use a query based on a table(s) for this)

the best way is to have a "selected flag" - have one box include items WITH the flag checked, and the other include items WITHOUT the flag checked

then when you "move" an item, all you have ot do is something like

1. "update the flag to true/false as appropriate"
2. listbox.requery
2. combobox.requery

job done.

if the listboxes/comboboxes are multi-select its a bit trickier, to get the code, but you can go through the selected items, and set the flag for all of them together.

 

[pm4698]

At the listbox i use a query at the row source.Something like this:
SELECT table1.surname from table1 where table1.surname not in <> etc (like first post).
And at the after update combo box i use the me.listbox_name.requery code.
My disadvantage is that i have lots of combo boxes. The combo box has as row source the same as list box (SELECT table1.surname from table1 where table1.surname not in <>...<> (all the combo boxes of the form except the listbox).
As a result, i use lots of not in <>...<>...<> so the values selected from comboboxes will not be included at the list box. At some point, i get a message that i have passed the 1024 character limit.

Could you explain a little analytically your idea about check boxes?

 

[gemma-the-husky]

for the list box
SELECT table1.surname from table1 where selectedflag = false
(maybe with other settings like distinct and orderby if necessary)

for the combo box
SELECT table1.surname from table1 where selectedflag = true

I tend to use stored queries rather than inline statements, so i can design them visually

then basically this

when you select an item in the list box, have a command button that just does
(I use a left pointing arrow graphic, and a right pointing arrow graphic)

docmd.runsql "update table1 set selectedflag = true where surname = " & chr(34) & whatever & chr(34)
mylistbox.requery
mycombobox.requery

 

[pm4698]

Another idea of mine is to make two queries that select all surnames except the ones that have been already chosen.
The one query will exclude half combo boxes and the other the rest.
Then i will make a union of these 2 queries.
With this, i will not have problem exceeding the 1024 limit character.
Can i do such thing or not?

 

[pm4698]

Otherwise, regarding my first statement, for the combo boxes which i cannot include at my code 'cause of exceeding the 1024 character limit, i could use an after update code, so i will scan my list box and if record of the listbox matches the record i have selected from the combo box then this record will be removed from the list box.

I tried:
Dim lnI As Integer

For listbox_name= 1 To listbox_name.ListCount
If listbox_name.List(lnI) = combobox_name.Value Then
listbox_name.removeItem(lnI)
End If

Next
but it doesn't work.

Any suggestions?