CODEWITHSUNDEEP
sqlvbams-accessdaorecordset
John Shaw
John Shaw

Reputation: 348

Insert Calculated Field in Table as one String VBA - MS Access

I need to open a Table(Unmatched); Create a New Field (ID); Use existing Fields to create a new unique String (hertz); then, finally, insert this new string Back into the Table's New Field. I'm stuck 'cause I'm new. Any nudge forward would be greatly appreciated.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim Hertz As String
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set tdf = db.TableDefs("Unmatched")
Set fld = tdf.CreateField("ID")
Set rst = db.OpenRecordset("Unmatched", dbOpenTable)

Do Until rst.EOF
hertz = rst![Accounting Document Item] & Mid(rst![JE Line Description], 20, 2) & Round(Abs(rst![Transaction Amount]), 0)

Debug.Print hertz 'immediate window check
---> DoCmd.RunSQL "?!?!?"
rst.MoveNext
Loop

Application.RefreshDatabaseWindow

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Upvotes: 0

Views: 458

Answers (1)

JJ32
JJ32

Reputation: 1034

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim Hertz As String
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set tdf = db.TableDefs("Unmatched")
Set fld = tdf.CreateField("ID", dbText, 255)
tdf.Fields.Append fld

Set rst = db.OpenRecordset("Unmatched")

Do Until rst.EOF
    Hertz = rst![Accounting Document Item] & Mid(rst![JE Line Description], 20, 2) & Round(Abs(rst![Transaction Amount]), 0)
    Debug.Print Hertz
    rst.Edit
    rst!ID = Hertz
    rst.Update
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing

This is how I would do it in code. An update query would be easier if you did not need to create the field on the fly.

Upvotes: 1

Related Questions