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

 

[Uncle Gizmo]

What happens if the user selects three items where do you put the 3rd?

 

[valeryk2000]

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

 

[Uncle Gizmo]

Ah! I got it. You have a form frmCase. You are looking at Case number 357.

The list box lstItems offers several items you can select from. You select 5 items, then press a button and the Case Number 357 is added to the field CaseID in table "tblCaseItems. Simultaneously, each of the selected ItemID's are added to the ItemID field.

What you want is when you re-visit the Case, the list box indicates your previous selections.

I like the idea, but it's complicated and risky. What happens when the user changes the selections? I suppose you could delete the list and refill it with the new selections, but I don't like it, it feels wrong.

Instead of the List box, why not have a subform in datasheet view with a combobox to select the items. Less risky, and self maintaining?

 

[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"

 

[Uncle Gizmo]

Have you tried the on current event?

 

[valeryk2000]

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

 

[Uncle Gizmo]

In the forms module at the top there are two combo boxes. In the one on the left you can select "Form". Now the one on the right will list the form events. Choose current event and it will add the current events stub in your module. Call your function for updating the list box in that stub.

 

[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