use ado to update field value

[george]

Hi to all,

This is my second desparate attempt for an answer, I'd
appreciate some help.

I have a table, tblTest, with two fields:

ID (primary key, type long)
Status (not unique values, type byte)

I also have a multiple selection list box, lstTest, with
two fields, based on the above mentioned table, tblTest.

I know how to change the ID field value in my table for
all records I choose to select in my list box through ado.
Below is shown the code I use:

dim cnn as Connection
dim rstTest as New ADODB.Recordset
dim varPosition as Variant

Set cnn = CurrentProject.Connection
rstTest.Open "tblTest", cnn, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect

'Set the Index Property to search on the primary key.
rstTest.Index = "ID"

'Loop through each selected record in the list.
For Each varPosition In lstTest.ItemsSelected

'Find the record in the tblTest.
rstTest.Seek lstTest.ItemData(varPosition)

'Change the ID value in the table.
rstTest!ID = ...(NewID) 'Some variable with NewID
rstTest.Update

Next varPosition

This works fine. What I would like to know is how to
change the value of the second field of my table, Status,
(instead of the ID field) which doesn't contain unique
values. Is this possible? Any kind of suggestion would be
greatly appreciated.

Thanks in advance, George.

 

[MacDermott]

It baffles me why you would want to change the Primary Key of a bunch of
records at all, much less change them all to the same value, but since
that's not what you're asking, I won't insist on addressing that.

When working with a listbox, I usually find it useful to collect up the
items selected into a long string, which can then become part of a SQL
statement.
For instance, I would have coded your issue like this: (WARNING: AIR CODE)
Dim SQLStr as string
Dim i as long
For i=0 to lstTest.listcount-1
if lstTest(i).selected=true then
SQLStr=SQLStr & lstTest.itemdata(i) & ","
end if
Next
If len(SQLStr)>0 then
SQLStr="UPDATE tblTest SET ID=" & NewID & "WHERE ID IN (" &
Left(SQLStr,len(SQLStr)-1) & ")"
CurrentProject.Connection.Execute SQLStr
end if

To update the Status field, you'd simple replace the clause
"UPDATE tblTest SET ID= " & NewID
with
"UPDATE tblTest SET Status= " & NewStatus

HTH
- Turtle

 

[george]

Thank you very much for your reply. The code I have given
serves educational purposes only in the use of ado
technology and I wanted to go one step further before I
adopt it to my application.
George