Access 2007 , VBA , recordset, adding New field [modified]
-
Dear ALl, i have a record set lets say: set rs = db.openrecordset ("SELECT Name, Location FROM tb1") I need to add a new field "salary" to rs, so i used : rs.fields.append "salary" , adinteger salary is of integer datatype This is not working, it is giving me Typemismatch Error and it highlights
**"salary"**, i also googled, all says the same method to append, but still not working ..... AM not sure whats the error, or why , Any one can guide how to append this coloumn to the recordset ??? any work around ??? :confused: Note: I want to fill the values of this "salary" field with numbers from calculations. Regards,0 will always beats the 1.
modified on Monday, February 7, 2011 5:48 AM
-
Dear ALl, i have a record set lets say: set rs = db.openrecordset ("SELECT Name, Location FROM tb1") I need to add a new field "salary" to rs, so i used : rs.fields.append "salary" , adinteger salary is of integer datatype This is not working, it is giving me Typemismatch Error and it highlights
**"salary"**, i also googled, all says the same method to append, but still not working ..... AM not sure whats the error, or why , Any one can guide how to append this coloumn to the recordset ??? any work around ??? :confused: Note: I want to fill the values of this "salary" field with numbers from calculations. Regards,0 will always beats the 1.
modified on Monday, February 7, 2011 5:48 AM
scorp_scorp wrote:
any work around ???
How about adding a dummy-column in the select-statement? Something like below;
set rs = db.openrecordset ("SELECT Name, Location, 0 As [Salary] FROM tb1")
You could then overwrite the values in the new column as required :)
I are Troll :suss:
-
scorp_scorp wrote:
any work around ???
How about adding a dummy-column in the select-statement? Something like below;
set rs = db.openrecordset ("SELECT Name, Location, 0 As [Salary] FROM tb1")
You could then overwrite the values in the new column as required :)
I are Troll :suss:
Greaaaat, thanks alot, this worked excellent but hence i got another problem: when now i want to fill this feild (after creation), am doing: With rs .AddNew .Fields("salary") = x .Update End With where x is an integer from some where, i have the following error: Field cannot be updated . i also used : rs.Edit rs("salary").Value = x rs.Update also same error Any hint why this error showing up ??? is it the right way to fill in this field ?? i think it is :confused:
0 will always beats the 1.
modified on Monday, February 7, 2011 11:36 PM
-
Greaaaat, thanks alot, this worked excellent but hence i got another problem: when now i want to fill this feild (after creation), am doing: With rs .AddNew .Fields("salary") = x .Update End With where x is an integer from some where, i have the following error: Field cannot be updated . i also used : rs.Edit rs("salary").Value = x rs.Update also same error Any hint why this error showing up ??? is it the right way to fill in this field ?? i think it is :confused:
0 will always beats the 1.
modified on Monday, February 7, 2011 11:36 PM
scorp_scorp wrote:
Any hint why this error showing up ???
I don't know, would have to look it up. My guess is that the recordset-object is readonly. What are you trying to achieve, and in what language? If you need to update some records in a database, you'd best execute the Sql against the database-server :)
I are Troll :suss:
-
scorp_scorp wrote:
Any hint why this error showing up ???
I don't know, would have to look it up. My guess is that the recordset-object is readonly. What are you trying to achieve, and in what language? If you need to update some records in a database, you'd best execute the Sql against the database-server :)
I are Troll :suss:
Thaks eddy, i found that recordsets are read only by default ... and also are not modifiable if sql depends on two tables of one-to-many relation or invlove sumation of rows ... Also, if above conditions are not true, then a recordset to be modifiable, i think attribs should be set like: set st = db.openrecordset("SQL",2,0,3) or set st = db.openrecordset("SQL",adOpenDynamic,adUseNone, adLockOptimistic)
0 will always beats the 1.