Dynamic field append Err 1

[Trudye]

I have a procedure that dynamically adds a field to an existing table. I would like the ability to delete the field if the need arises to rerun the job.

I tried using the following code but received err:
3219 Invalid Operation.

Set REC1 = db.TableDefs("tblVinCntExptRptsRecovery")
fldname = Format(AddBusinessDay(Date, 1), "MMM") & "-" & Format(AddBusinessDay(Date, 1), "DD")
Set fld = REC1.CreateField(fldname, dbText)
REC1.Fields.Append fld

If the field already exists the pgm goes to the Error Rtn
fVcount_Err:
If Err.Number = 3191 Then
REC1.Fields.Delete fld
REC1.Fields.Append fld
CurrentDb.Execute mySQL
MsgBox Err.Number & " - " & Err.DESCRIPTION
MsgBox "Field has been deleted and it will be readded so processing can continue"
Resume Next
End If

The REC1.Fields.Delete fld command is causing the error. The append has been working for some time and continues to work. Why can't I delete in the same way that I append?

Thanks much,
Trudye

 

[PHV]

Why not simply ignoring the error if the field already exists ?
If Err.Number = 3191 Then Resume Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Trudye]

Thanks PHV for responding, this fix is to address reruns.

 

[PHV]

Replace this:
REC1.Fields.Delete fld
By this:
REC1.Fields.Delete fldname

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Trudye]

DUH! that made sense, hind site is 20/20.

Thanks much,
Trudye