need multi selection list box help

[Danny]

I set this up so the users can select more than one record so they can
update records a bunch at a time.
only one field is modified.
How can I edit this table easily instead of scrolling through the database
and comparing the selected items in the list box and comapring them to the
fields in the database.

Can you pass a list to an update query?

or can I edit one field in the database if I know the record number or that
ID number without having to loop through each one.

Thanks in advance

[Salad]

Danny wrote:

I set this up so the users can select more than one record so they can
update records a bunch at a time.
only one field is modified.
How can I edit this table easily instead of scrolling through the database
and comparing the selected items in the list box and comapring them to the
fields in the database.
I don't know. From your problem definition above you don't mention what
you are comparing them too. Maybe you simply need an update query where
you compare the values between 2 tables and update where different.
Can't determine from your description.
Can you pass a list to an update query?
No. And Yes. Let's say you have a listbox called LB. It displays 5
columns. The Key field, ID, is displayed in the second column. You
want to filter for records you have selected in the listbox.

Dim var As Variant
Dim strHold As String
If Me.LB.Itemselected.Count > 0 then
For each var in Me.LB.Itemselected
'1 is the 2nd column since colcnt starts at 0
strHold = strHold & Me.LB.Column(1,var) & ", "
Next

'Remove the ", ".
'If ids selected are 1,2,3,4,5 the result is (1,2,3,4,5)
strHold = "(" & Left(strHold,len(StrHold)-2) & ")"

Me.Filter = "Id IN " & strHold
Me.FilterOn = True
Endif

I am passing a list, per se, with the value in strHold. See the help
topic "IN Operator"


or can I edit one field in the database if I know the record number or that
ID number without having to loop through each one.

Thanks in advance

[PC Datasheet]

Danny,

From my files. See below my SIG line ---
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Use Values From A Multiselect Listbox As Query Criteria

Loop through the ItemsSelected collection of the listbox and generate the SQL
string.
Dim SQLStr As String
Dim vSel As Variant
SQLStr = "SELECT <some fields> FROM <some tables> WHERE <criteria>"
With Me!ListBoxName
If .ItemsSelected.Count > 0 Then ' Did user select any rows?
SQLStr = SQLStr & " AND [ClassID] IN("
For Each vSel In .ItemsSelected
SQLStr = SQLStr & .Column(0, varItem) & ", "
Next vSel
SQLStr = Left(SQLStr, Len(SQLStr) - 2) & ");"
End If
End With

Note: The first (zeroth actually) column of the listbox is assumed to be a long
integer ClassID.


SQLStr can now be used as the RecordSource


"Danny" <da********@hotmail.com> wrote in message
news:TK**********************@news4.srv.hcvlny.cv. net...

I set this up so the users can select more than one record so they can
update records a bunch at a time.
only one field is modified.
How can I edit this table easily instead of scrolling through the database
and comparing the selected items in the list box and comapring them to the
fields in the database.

Can you pass a list to an update query?

or can I edit one field in the database if I know the record number or that
ID number without having to loop through each one.

Thanks in advance

[Danny]

"Danny" <da********@hotmail.com> wrote in message
news:TK**********************@news4.srv.hcvlny.cv. net...

I set this up so the users can select more than one record so they can
update records a bunch at a time.
only one field is modified.
How can I edit this table easily instead of scrolling through the database
and comparing the selected items in the list box and comapring them to the
fields in the database.

Can you pass a list to an update query?

or can I edit one field in the database if I know the record number or that ID number without having to loop through each one.

Thanks in advance

Thanks for your great responses and I will try them out.
BUT, this list can get large, like 1000 items in it, and I guess the sql
string will get huge. This is not good right?

[Salad]

Danny wrote:

"Danny" <da********@hotmail.com> wrote in message
news:TK**********************@news4.srv.hcvlny.cv. net...

I set this up so the users can select more than one record so they can
update records a bunch at a time.
only one field is modified.
How can I edit this table easily instead of scrolling through the database
and comparing the selected items in the list box and comapring them to the
fields in the database.

Can you pass a list to an update query?

or can I edit one field in the database if I know the record number or

that

ID number without having to loop through each one.

Thanks in advance

Thanks for your great responses and I will try them out.
BUT, this list can get large, like 1000 items in it, and I guess the sql
string will get huge. This is not good right?

Don't know. I had a similar question on this about a month ago. PC
Datasheet had a change on the recordsource. That would probably bomb as
the SQL string would be too large. It probably would not if it were
stored in a query.

I ran a test and stored a string of keys over 13000 bytes, as each key
was 5 bytes I had over 1000 keys. Then I set the filter and it worked
like a champ and was near instantaneous. So instead of changing the
recordsource you might want to change the filter instead. I figure as
long as your filter string is less than 64KB you should be OK.