Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    do nothing if a field exists (Access 2000)

    I have a function called CreateFieldsinProducts() that creates 2 new fields in the table Products.These fields are called oem and vip.
    This function works very well the first time, that is when the fields are not available, and then the function
    creates these fields.If i want to repeat he function second time, which means that the fields are already created,
    then i receive the Run time error 3191 : cannot define a field more than once.
    It is very important to me that i escape this error and do nothing if the field is present.This code is usedf by several
    users, some of them may have the field and some of them not.Therefore i would like to have some code of the type
    OnError resume next, and this code to function only if these fields are not present, but otherwise i do not want to obtain the message for the
    error, since it stops my further actions.


    Public Function CreateFieldsInProducts()
    ' create new fields in table Products
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb")
    Set tdf = dbs.TableDefs("Products")
    Set fld = tdf.CreateField("oem", dbCurrency)
    Set fld = tdf.CreateField("vip", dbCurrency)
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

    I will be grateful for any help

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: do nothing if a field exists (Access 2000)

    Use this modified function:
    <pre>Public Function CreateFieldsInProducts()
    On Error GoTo ErrProc
    ' create new fields in table Products
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb")
    Set tdf = dbs.TableDefs("Products")
    Set fld = tdf.CreateField("oem", dbCurrency)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("vip", dbCurrency)
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    Exit Function
    ErrProc:
    If Err = 3191 Then
    Resume Next
    Else
    MsgBox Err.Description ' or something else you want
    End If
    End Function</pre>

    I have also add a second tdf.Fields.Append fld because else only one field would be added.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •