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

    change table property through code (Access 2000)

    How can i change the field property of a table through code? I have a table called Fruits and there two fields, namely
    Quantity and UnitPrice are set to required = Yes.
    I want to put the required property to No but with code.I could have easily changed the property in the table grid,but
    i want to use this code in the construction of a more complicated condition.Therefore i want to change the property with
    DoCmd....etc. How can i do that?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: change table property through code (A2K)

    Sample code for setting field's Required property:
    <pre>CurrentDb.TableDefs("TABLE1").Fields("FIELD1" ).Required = True</pre>

    Above works from Debug window. In context of a sub procedure I would normally use object variables for this type of thing.

    HTH

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change table property through code (A2K)

    Thank you very much for your reply. Your suggestion was excellent and i solved my problem.Now all works fine.
    In your reply your mention that you would use a variable in the frames of the code.Would you mind writing me an example?

    Best regards

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

    Re: change table property through code (A2K)

    If I may take the liberty to answer this question: a table, a field, etc. are examples of objects. If you use a variable to store an object, it's called an object variable. You assign an object variable with an instruction of the form

    <font face="Georgia">Set variablename = objectreference</font face=georgia>

    and when you're done with the variable, you must release the memory it occupies by an instruction of the form

    <font face="Georgia">Set variablename = Nothing</font face=georgia>

    Example:

    <font face="Georgia">Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Table1")

    Set fld = tdf.Fields("Field1")
    fld.Required = True

    Set fld = tdf.Fields("Field2")
    fld.AllowZeroLength = False

    Set fld = tdf.Fields("Field3")
    fld.DefaultValue = 12345

    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing</font face=georgia>

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: change table property through code (A2K)

    I think HansV has answered your question well. I would only add, one reason I mentioned use of object variables is that if you are making a lot of changes to your tables' design programatically, it much simpler to do so using object variables like dbs, tdf or fld (or whatever names you choose) than it is to type expressions like "CurrentDb.TableDefs("TABLE1").Fields("FIELD1" )" repeatedly. Also enables you to use loops, etc, for repetitive tasks. Here a short example of sub that updates Required property of all fields in specified table to true or false:
    <pre>Public Sub SetRequiredProperty(strTbl As String, _
    bValue As Boolean)

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field

    Set db = CurrentDb
    Set tbl = db.TableDefs(strTbl)

    For Each fld In tbl.Fields
    fld.Required = bValue
    Next

    Set db = Nothing
    Set tbl = Nothing
    Set fld = Nothing

    End Sub</pre>


    HTH

Posting Permissions

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