Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Programatically duplicating a field in a table

Status
Not open for further replies.

VBAguy22

IS-IT--Management
Aug 5, 2003
180
CA
Hello
I have a table "myTable" and I have fields in it "Name", "Address", "AmountStart"
They are all populated.
Assuming I want to programatically add a field called "AmountEnd" and make the values in its records be equal "AmountStart" at first (then manually change them as needed).
How do I do it?
Basically I need to create another field, name it, and populate it with values of an existing field using VB
 
I think that this is what you are asking for:

Code:
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    
    Set db = CurrentDb
    
    Set tdf = db("myTable")
    
    Set fld = tdf.CreateField("AmountEnd", dbDouble)
    
    tdf.Fields.Append fld
    
    Set fld = Nothing
    Set tdf = Nothing
    
    db.Execute "UPDATE myTable SET AmountEnd = AmountStart;"
    
    Set db = Nothing
 
Hi,

you could also achieve this using the docmd.runsql function, i.e...
Code:
DoCmd.RunSQL "ALTER TABLE myTable" & _
    " ADD COLUMN AmountEnd NUMERIC;"
DoCmd.RunSQL "UPDATE myTable" & _
    " SET AmountEnd = AmountStart;"

HTH, Jamie
FAQ219-2884
[deejay]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top