Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to do nothing if an object already exists (Access 2000)

    How to do nothing if an object in the collection exists


    I have a function that appends a defaul value as = Date() to the field OrderDate
    in the table called orders of my database.The function woks fine if there is no default value.
    However is the default value is already set to Date, then i get the error message

    "Cannot append. AN object with that name already exists in the collection"

    Is it possible to do nothing in case a default value already exists and just continue woth
    the other commands ?

    My function is the following



    Public Function AppendOrderDate()
    Dim StrPassword As String
    StrPassword = "secret"
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim idx As DAO.Index
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & StrPassword)
    Set tdf = dbs.TableDefs("orders")
    tdf.Fields.Refresh
    Set fld = tdf.Fields("OrderDate")
    fld.Properties("DefaultValue") = "=Date()"
    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    fld.Properties.Append prp
    dbs.Close
    Set prp = Nothing
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to do nothing if an object already exists (Access 2000)

    You are first setting the DefaultValue property in

    fld.Properties("DefaultValue") = "=Date()"

    which might as well have been the shorter and simpler

    fld.DefaultValue = "=Date()"

    and after that, you are creating this same property in

    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    fld.Properties.Append prp

    Clearly, you can't create a property after it has been set. But apart from that, DefaultValue is a built-in property of a Field object, there is no need to create it. Otherwise, the statement to set the property would have failed!

    So take out all statements that mention prp. Also, you do nothing with the variable idx, so you might take that out as well.

Posting Permissions

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