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!!

 

[DCrake]

As the message suggests it appears that the name of the field you want to add to the table already exists.

To trap this error you could try the following

Public Function AddNewFieldToTable(sTableName As String, sFieldName As String) As Boolean



Dim bFlag As Boolean
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim tbl As DAO.TableDef

Set rst = CurrentDb.OpenRecordset(sTableName)
Set tbl = CurrentDb.TableDefs(sTableName)


For n = 0 To rst.Fields.Count() -1
   If rst(n).Name = sFieldName Then
      bFlag = True
      Exit For
   End If
Next

If bFlag = False Then
    Set fld = tbl.CreateField(sFieldName, dbDouble)
    fld.Properties("Required").Value = False
End If

Set rst = Nothing
Set fld = Nothing
Set tbl = Nothing

AddNewFieldToTable = Not bFlag

End Function

Then call the function as such

If AddFieldToTable("Pol","fundbalhk") = False Then

   MsgBox "Could not add " & sFieldName & " already exists.", vbExclamation+vbOkOnly,"Add new field - Failed"

Else
   Msgbox sFieldName & " added to table sucessfully. Click Ok to continue.", vbInformation+vbOkOnly,"Add new field - Successful"

End If

Aircode rules

David

 

[kwokv616]

If I say I have run this code before, would it be the reason that the fields already exist and cannot be overwritten?

If i only need them to exist (i dont need them to be deleted and created again each time because i only need the same fields each time i run it), does it mean that I can just not run the code and continue to do the remaining codes of the program?

 

[DCrake]

The code posted works lke this

You call the function AddNewFieldToTable(TableName,FieldName)

This function passes 2 arguments to the procedure a) the tabel you want to add the field to b) the actual name of the field you want to add.

The tabel in question is opened as a recordset and a loop is performed for each field in the table.

If we say we have a table called tblProducts and in this field there are three fields fldProductID, fldProductCategory, fldProductName. We want to add a new field call fldProductCost.

The loop would work like this

Loop 1
does fldProductID = fldProductCost - No continue in the loop
Loop 2
does fldProductCategory = fldProductCost - No continue in the loop
Loop 3
does fldProductName = fldProductCost - No continue in the loop

Last field end the loop

The bFlag variable is still False
So we have not found the field so it is alright to add it

The next time we run the code using the same fldProductCost field name the following would occur


Loop 1
does fldProductID = fldProductCost - No continue in the loop
Loop 2
does fldProductCategory = fldProductCost - No continue in the loop
Loop 3
does fldProductName = fldProductCost - No continue in the loop
Loop 4
does fldProductCost = fldProductCost - Yes set the flag to True and exit Loop


Now because the the flag is true this means that the field already exists in the tabel so don't try and add it.

The function return the outcome of the test which is then used by the message box. This is a untidy way of explaining the procedure but in essence this is what it is doing.

David