Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create fields in table (Access 2000)

    On the control of my form i want to give a command for creating fields in a table.However i receive the message error variable not defined.
    May i have some help as to where is my error?


    Dim dbs As Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim idx As DAO.Index

    Set tdf = dbs.TableDefs("Orders")
    Set fld = tdf.CreateField("LastUpdated", dbDate)
    tdf.Fields.Append fld
    tdf.Fields.Refresh

    ' set additional field properties
    Set fld = tdf.Fields("LastUpdated")
    fld.Properties("DefaultValue") = "=Date()"
    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    fld.Properties.Append prp

    ' create and append index
    Set idx = tdf.CreateIndex("LastUpdatedIdx")
    idx.Fields.Append idx.CreateField("LastUpdated")
    idx.Unique = False
    tdf.Indexes.Append idx

    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
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create fields in table (Access 2000)

    One rather glaring error is that you dim dbs as Database but you never appear to set it to anything. Without a valid database object instance, none of the other code is going to work.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create fields in table (Access 2000)

    Thank you very much indeed !!! , it was a glaring omission from all right to overlook it. ! Will you help me me more a bit? If the field exists, than i get the message 3191 Cant define field more than once. I wanted to add on the following
    If Err.Number = 3191 Then '3191 = Can't defin field more than once.
    'Log the error, or show messagebox or something... or nothing. Whatever!.
    End If

    But either this code is not in the right place, or i cant use it properly. What i want is to add the field if it doesn exist, or otherwise skip it

    Best regards



    Public Function OnOrders()
    If Err.Number = 3191 Then '3191 = Can't defin field more than once.
    'Log the error, or show messagebox or something... or nothing. Whatever!.
    End If

    Dim dbs As DAO.Database
    Set dbs = CurrentDb

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim idx As DAO.Index

    Set tdf = dbs.TableDefs("Orders")
    Set fld = tdf.CreateField("LastUpdated", dbDate)
    tdf.Fields.Append fld
    tdf.Fields.Refresh

    ' set additional field properties
    Set fld = tdf.Fields("LastUpdated")
    fld.Properties("DefaultValue") = "=Date()"
    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    fld.Properties.Append prp

    ' create and append index
    Set idx = tdf.CreateIndex("LastUpdatedIdx")
    idx.Fields.Append idx.CreateField("LastUpdated")
    idx.Unique = False
    tdf.Indexes.Append idx

    dbs.Close
    Set prp = Nothing
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing



    End Function

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

    Re: Create fields in table (Access 2000)

    You have put the test for an error at the beginning of the code, but at that point, the error has not occurred yet. the way to test for errors is by creating an error handling section. The general format for error handling is:

    Sub SomethingOrOther()

    ' Activate error handling
    On Error GoTo ErrHandler

    ' Instructions go here
    ...

    ExitHandler:
    ' Cleaning up that must always be performed
    ...
    Exit Sub

    ErrHandler:
    ' Check which error occurred
    Select Case Err.Number
    Case ...
    ' Action to be performed in this case
    Resume Next
    Case ...
    ' Action to be performed in this case
    Resume ExitHandler
    End Select
    End Sub

    In your function:

    Public Function OnOrders()
    Dim dbs As DAO.Database
    ...

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    ...

    ExitHandler:
    Set prp = Nothing
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    Select Case Err.Number
    Case 3191
    MsgBox "The field has already been defined!", vbCritical
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    Resume ExitHandler
    End Function

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create fields in table (Access 2000)

    Thank you very much for your reply. I wonder where i am wrong since again, when i let the function go, i obtain the message "The field has already been defined"
    and what i want is to skip that message,to define the filed if it is lacking but otherwise not to stop the process.Is it possible ?


    Public Function OnOrders()
    Dim dbs As DAO.Database
    On Error GoTo ErrHandler
    Set dbs = CurrentDb

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim idx As DAO.Index

    Set tdf = dbs.TableDefs("Orders")
    Set fld = tdf.CreateField("LastUpdated", dbDate)
    tdf.Fields.Append fld
    tdf.Fields.Refresh

    ' set additional field properties
    Set fld = tdf.Fields("LastUpdated")
    fld.Properties("DefaultValue") = "=Date()"
    Set prp = fld.CreateProperty("Format", dbText, "Short Date")
    fld.Properties.Append prp

    ' create and append index
    Set idx = tdf.CreateIndex("LastUpdatedIdx")
    idx.Fields.Append idx.CreateField("LastUpdated")
    idx.Unique = False
    tdf.Indexes.Append idx

    Close
    ExitHandler:
    Set prp = Nothing
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
    ErrHandler:
    Select Case Err.Number
    Case 3191
    MsgBox "The field has already been defined!", vbCritical
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    Resume ExitHandler
    End Function

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

    Re: Create fields in table (Access 2000)

    You can remove the msgbox from the error handling part if you don't want to show it.

    It is also possible to resume execution at a later point, but you would have to add code to do this for each of the properties and for the index too, for if the field already exists, you may already have created the properties and index too. It is possible to do this, but it makes your code more complicated. See the example in the online help for CreateProperty. I think it would be easier to just get out if the field exists already.

Posting Permissions

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