Trouble changing value in an appended field in ADO recordset

[sienayr]

Greetings,

I have searched hi and lo through the groups and haven't found my
problem specifically. I have listed below what I have tried based on
what I have found in the groups. Please let me know if what I am
trying to do is even possible; if it is, any insight is greatly
appreciated.

I am building an ADO recordset from an SQL server, connection
properties as follows (coding in VBA/A2K):

conn.Provider = "MSDataShape.1"
conn.Properties("Data Source").Value = "Server"
conn.Properties("Initial Catalog").Value = "Database"
conn.Properties("Password").Value = "pw"
conn.Properties("User ID").Value = "id"
conn.Properties("Data Provider").Value = "SQLOLEDB.1"
conn.Properties("Persist Security Info").Value = True

SQL="Select field1, field2, "MadeUpField" as field3 From tblFields"

rst.Open SQL, conn, adOpenDynamic, adLockPessimistic

The recordset will be set to a form's recordset in A2K. The values for
field3 come from another entire data source, so I am trying to fill in
the values for field3 dynamically by moving through the recordset,
before binding the recordset to the form.

I have tried to modify the field3 value in the recordset resulting from
using the SQL above as follows:

Do Until rst.EOF
rst.Fields("field3").Value = strSomeValue
rst.Update
rst.MoveNext
Loop

This gives the 'Multi-step operation generated errors. Check each
status value.' error when it gets to the
rst.Fields("field3").Value = strSomeValue line of code.

I have also tried using this SQL:
SQL="Select field1, field2, From tblField" (not including the added
field)

and this code to append the field to the recordset:

rst.Fields.Append "field3", adChar, 50
rst.CursorLocation = adUseClient
rst.Open SQL, conn(same as above), adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do Until rst.EOF
rst.Fields("field3").Value = strFieldValue
rst.Update
rst.MoveNext
Loop

This code give me 'Item cannot be found in the collection corresponding
to the requested name or ordinal when it gets to the
rst.Fields("field3").Value = strSomeValue line of code.

I am dying here, any help is greatly appreciated!

rn

[redryderridesagain]

Sien..
Could you restate your problem in a sentence?

[sienayr]

In the SQL statement "Select field1, field2, "MadeUpField" as field3

From tblFields", I need to programatically change the value of field3,

which is populated with the value "MadeUpField" on every row. In other
words, the column is not from the actual data source, it is appened via
the SQL statement. I have been unable to programmatically move through
the rows of the recordset and change the values of that field using
either of the methods described above. I hope this clarifies some...
thanks

[sienayr]

And here is the answer:

http://support.microsoft.com/kb/223771/