How Do I Append a Field?

[kwokv616]

i wrote this code. it runs fine until the last light where a msg box says "cannot define field more than once".

Have i typed in some wrong code?



Dim rst As DAO.Recordset
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Dim fld As DAO.Field

Set db = CurrentDb
Set tbl = db.TableDefs("Pol")



Set fld = tbl.CreateField("fundbalhk", dbDouble)
fld.Properties("Required").Value = False
tbl.Fields.Append fld



Thank you!!

[Stewart Ross]

Hi. It's what you do with your code that is wrong, not the code itself. You can't add the same field 'fundbalhk' twice to the same table ('pol' in this case). Your code as it stands will only work if table 'pol' does not have field 'fundbalhk' already defined.

And even if that is so, you can run your code just once - because after that a field of that name will exist in your table.

It kind of begs the question of why you are trying to add fields in code this way, where the table name and field name etc. are hard-coded into your VBA - makes no sense to me. Far quicker to add the field using the Table Design dialogue - unless you are intending to generalise the routine to be able to add fields of differing names to differing tables at will.

-Stewart

[missinglinq]

@Stewart Ross Inverness
And that idea also begs the question "Why?" I've always thought this idea of adding things on the fly, be they fields or textboxes or whatever, generally smacks of very poor database design! And how are you going to populate this field you've added thru code? You have no controls on forms to take care of the job. Are you going to try to add a control on a form on the fly as well?

Maybe if you could give us your reason for wanting to do this we could give you a better alternative.

Linq ;0)>

[ADezii]

After taking into consideration the expert advice from both linq and Stewart, if you reaaaaally need to create this Field on the fly for whatever reason, one fix would be:

Dim rst As DAO.Recordset
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Dim fld As DAO.Field
Dim intNumFields As Integer
Dim intCounter As Integer
 
Set db = CurrentDb
Set tbl = db.TableDefs("Pol")
 
intNumFields = tbl.Fields.Count
 
For intCounter = 0 To intNumFields - 1
  If tbl.Fields(intCounter).Name = "fundbalhk" Then Exit Sub
Next
 
Set fld = tbl.CreateField("fundbalhk", dbDouble)
fld.Properties("Required").Value = False
 
tbl.Fields.Append fld