Highlight items in list box by VBA/sql query

[valeryk2000]

We have a multiselect list box. User can select several items and insert them into a table (two fields, item ID and case ID) using Currentdb.execute "insert into ...". Later, when the user select the case again, how to make items selected according to the item IDs from the table?
Thanks

 

[valeryk2000]

The table has two fields - many-to-one - Case # may have many item IDs.

 

[arnelgp]

what are your comboboxes and their rowsource?

 

[valeryk2000]

Uncle,
You got it right. We have case# on the form. List box has related button 'Save Selections'. Selections (Item ID) are inserted into table tblCaseItems ((c) - Gizmo ;-). If the user clicks the button again - the first step is Delete from tblCaseItems where case#= current case# and then - Insert ...
Actually I know how to highlight items in the list box:
1. Open Recordset ("select ItemID from tblCaseItems where case#=current case#")
2. Do until rs.EOF - list.Selected (rs(0))=true
The problem is: what event can be captured when the user moves to the next record ?
arnelgp:
On formLoad the list box is populated with items from a lookup table with 2 field (columns) ItemID and ItemText - listbox ColumnWidth= 0";2"

 

[valeryk2000]

No, I did not. Can you give me a snippet?

 

[arnelgp]

on your form's current event:

Public Sub Form_Current()
dim rs As Dao.RecordSet
Dim db as Dao.database
set db=currentdb
set rs=db.openrecordset("select ItemID from tblCaseItems where case#=current case#")
with rs
if not .bof and not .eof then .movefirst
while not .eof
list.Selected (rs(0))=true
.movenext
wend
end with
set rs=nothing
set db=nothing
End Sub

 

[valeryk2000]

OK. I'll try it later (now busy with turkey stuff

 

[valeryk2000]

It's solved - on current we read the value of case# and the rest is easy.
Thank you for your help? Uncle and arnelgp!
Your friend
valeryk2000

 

[roosn]

i have a form with 6 multiselect 'simple listboxes, which i use to query about 120,000 records

so when i make my selections on a listbox, the textbox below records the selected ID's as for example 6,9,12,

i then create the query in SQL and the records output works quite well within 2 - 3 of seconds

as you can appreciate selecting the 6 listboxes can take a bit of time, and what i would like to do is save my search values from each textbox below the listbox into a record on a local table with a name

i then later would go to a combo box, find and select my saved search record and once the saved search record is selected, have the respective text boxes back filled with the saved data

now it is here i am stuck

for example in my textbox i have "6,9,12". is there a way of highlighting the rows in the listbox with ID's in the first column IN (6, 9, 12)

thank you

roosn

 

[arnelgp]

if it is multiselect listbox:

Dim s As String
Dim i As Integer
' supply the correct name for textbox1 (with values 6,9,12,)
' and List0 with the name of your listbox
s = Me.textbox1
s = "," & s & ","
For i = 0 To Me.List0.ListCount - 1
Me.List0.Selected(i) = False
If InStr(s, "," & Me.List0.Column(0, i) & ",") <> 0 Then _
Me.List0.Selected(i) = True
Next