update field value through ado

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

 

[Pavel Romashkin]

If you want to change the second field for the same record as you are
changing the ID for, you have no issues at all:

'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!OtherField = ...(NewValue)
rstTest.Update

Otherwise, you do need to somehow locate the exact records you need to
update, perhaps by limiting the rst source using a SELECT statement
(which would be a good idea even originally - what is the point of
seeking the entire table to locate a few selected values?).
I get a feeling that you could actually use SQL to do the updates,
without involving ADO. I am thinking it might be more efficient.

Pavel