create a field in code and assign it a value

[Mcgrco]

The code below will create a new field based on the current business day. i want to be able to assign this field a value (SumAmount = CDbl(DSum("[market_value£]", "tbl(9) Borrows_cash_term_alloc_stk")). Can anyone tell me how I do it.
I know how to do using sql but i thought there must be away to set the value when the field is created. many thanks

On Error GoTo ErrHandler
Dim dbCode As Database
Dim tbl As TableDef
Dim sPath As String
Set dbCode = DBEngine(0)(0)
Dim newfld As Field
Dim strSql As String
Dim SumAmount As Double
Set tbl = dbCode.TableDefs(sTable)
Set newfld = tbl.CreateField(FieldName, dbDouble)

tbl.Fields.Append newfld
tbl.Fields.Refresh

 

[doulostheou]

I may be wrong, but I think the only way to do this would be to then open the recordset and insert data into the new field. The following example assumes that you would want to insert the information inot each row of the table:

Dim rst as recordset
set rst = currentdb.openrecordset(sTable)
rst.movefirst 
do until rst.eof  
     rst.edit
     rst.fields(FieldName) = CDbl(DSum("[market_value£]", "tbl(9) Borrows_cash_term_alloc_stk"))
     rst.update
loop
set rst = nothing

 

[Pat Hartman]

Adding fields to tables "on the fly" seems to me to be a design nightmare. This is something you might have to do in a spreadsheet application because spreadsheets are "flattened" data structures but NEVER in a database application. In a relational table, new values should result in a row being added NOT a column.

 

[Mcgrco]

Thanks Pat. I will re-think my strategy