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

    Field properties through code (Access 2000)

    I want to set additional field properties through code but somehow i

    cannot manage it.I receice the message for a wrong syntax.
    Can somebody help me ?

    my function is the following

    Public Function AddByCode()
    Dim dbs As DAO.Database
    dbs = CurrentDB
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set tdf = dbs.TableDefs("Orders")
    ' set additional field properties for the field orderdate
    Set fld = tdf.Fields("OrderDate")
    fld.Properties("DefaultValue") = "=Date()"
    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    ' set additional property the field PaymentID to be 0
    Set fld = tdf.Fields("PaymentID")
    fld.Properties("DefaultValue") = 0

    dbs.Close


    Set prp = Nothing
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    End Function

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

    Re: Field properties through code (Access 2000)

    There are two problems with your code, one major and one minor.

    The major problem is that dbs is an object, so you must use Set to assign it a value: Set dbs = CurrentDb. Omitting Set is the syntax error.

    The minor problem is that you set a non-declared variable to Nothing in Set idx = Nothing. If you have Option Explicit in the module, this will be a syntax error too, but if you don't have Option Explicit, it will pass silently.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field properties through code (Access 2000)

    It is interesting that this particular problem has cropped up right now as I have been meaning to post a question about "Set" for the last couple of weeks.

    I recently noticed while doing some reading in some reference books that there is not a lot of (really obvious) consistency in the use of the reserved word "set" but was able to infer that it seems to apply to objects while it is optional with methods and properties. Is this the right inference and is there a rational underlying logic to this or is it just your usual Microsoft inconsistency? The problem for those of us still learning VBA is many of the reference works take a subtle concept such as this as obvious and don't bother to explain it.

    Any thoughts would be most enlightening and helpful.

    Peter N

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

    Re: Field properties through code (Access 2000)

    Set must be used, and can only be used with objects.

    Set dbs = CurrentDb is valid, but dbs = CurrentDb is not valid, and Set intX = 5 is not valid either.

    For other variables, you can use Let to assign a value, but this is optional. Let intX = 5 is valid, as is x = 5. Although almost everyone (me included) uses the latter form, it is sloppy because ambiguous. = can be used to assign a value and to compare. In many other programming languages, = is used for comparing only. In Pascal/Delphi for instance, assignment is :=, for example intX := 5.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field properties through code (Access 2000)

    Actually, the only time the Let keyword is required (or explicitly used in VBA) and the *other* time Set is used is in declaring custom Let and Set properties. Set Properties are used only with objects and Let properties are used to assign a value to a custom property (not an object) for retrieval by a Get property.
    Charlotte

Posting Permissions

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