Solved: Conditionally add new field to existing table

[hkeiner]

Newbee here with what may be a simple question...

How do I conditionally add a NEWFIELD to an EXISTINGTABLE when the NEWFIELD does not already exist in the EXISTINGTABLE? I found the CreateField method in VBA help but I am not sure how to use it. I am thinking it could be used like shown below.

Also, I am not sure how to test if the NEWFIELD already exists and what to do about the 'expression' part of the code. Probably need a couple of DIM statements too.

Any advice would be appreciated.


[vba]
Sub CreateNewField ()

If FieldAlreadyExists Then
Exit Sub
Else
expression.CreateField(NEWFIELD , Text, 30)
End If

End Sub
[/vba]

[hkeiner]

After quite a bit of trial, frustration, and time I managed to come up with some code that works OK for me. I thought I would share it in case someone else finds a need to do what I wanted to do.


[vba]

Function CreateNewField()

' Avoids error message if field already exists
On Error Resume Next

' declares variables and variable values
Dim tbldef As TableDef
Dim dbs
Set dbs = CurrentDb
Set tbldef = dbs.TableDefs("EXISTINGTABLE")

' creates new field if field does not exist / error if field already exists
With tbldef
.Fields.Append .CreateField("NEWFIELD", dbText, 50)
End With

'clears variable
Set tbldef = Nothing
End Function


[/vba]

[hansup]

After quite a bit of trial, frustration, and time I managed to come up with some code that works OK for me. I thought I would share it in case someone else finds a need to do what I wanted to do.
[vba]Function CreateNewField()

' Avoids error message if field already exists
On Error Resume Next[/vba]

Your intention was to suppress error #3191, 'Cannot define field more than once'. However On Error Resume Next will ignore nearly all errors. Examples of other errors which your code would also ignore include: EXISTINGTABLE not found; EXISTINGTABLE already includes the maximum 255 fields; another user has a lock on EXISTINGTABLE; the database was opened read-only.

Please be cautious with On Error Resume Next. If you must use it, make sure you're only ignoring those errors you really do want to ignore.

I think an approach closer to the original pseudo-code outline is more sound. Create a function, FieldExists(), which returns True if the field exists in the target table. Then attempt to add the field only when FieldExists() is False.

[hkeiner]

Create a function, FieldExists(), which returns True if the field exists in
the target table. Then attempt to add the field only when FieldExists() is
False.

Yes, I like the idea of using more refined code to deal with specific errors rather than using the more general 'On Error Resume Next'.

My next problem then is how do I do that? That is, what code would the FieldExists() function contain? I can probably figure out how to use the function's result of true or false in my code, but I do not know how to create the FieldExists() function to return a true or false as appropriate.

Any advice on this latter problem is very much appreciated by this newbee to VBA.

[hansup]

Here is a quick function to get you started.
[VBA]Public Function FieldExists(ByVal pField As String, _
ByVal pTable As String) As Boolean
Dim blnReturn As Boolean
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.TableDefs(pTable)
'next line is not needed, but makes it clearer
blnReturn = False
For Each fld In tdf.Fields
If fld.Name = pField Then
blnReturn = True
Exit For
End If
Next fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
FieldExists = blnReturn
End Function[/VBA]I didn't include an error handler, so the caller code will have to deal with errors from the function ... or you can add in an error handler.

You can use it like this:[VBA]If Not FieldExists("NEWFIELD", "EXISTINGTABLE") Then
' put your code to add the field here
End If [/VBA]

[HiTechCoach]

This may help: DAO Programming Code Examples

[HiTechCoach]

Also this: Error Handling and Debugging Tips and Techniques