Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Exists (2K)

    I want to determine if a field exists in a table. Thus far I have figured out I need the following code:

    Dim DB As DAO.Database
    Dim TD As DAO.TableDef
    Set DB = CurrentDb
    Set TD = DB.TableDefs("tbl_parameter")

    What I want to do is find out if the field 'Comments' exists in the tbl_parameter table. If it does I will then copy the contents to a new field in a new table.
    Where do I go from here?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Field Exists (2K)

    There are basically two ways to do this:

    1) Loop through the fields:

    Dim fld As DAO.Field
    For Each fld In TD.Fields
    If fld.Name = "Comments" Then
    ' Yes! Execute code here
    ...
    Exit For
    End If
    Next fld

    2) Try to refer to the field and check for errors

    Dim fld As DAO.Field
    On Error Resume Next
    Set fld = TD.Fields("Comments")
    On Error Goto 0
    If Not fld Is Nothing Them
    ' Yes! Execute code here
    ...
    End If

  4. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Exists (2K)

    Thanks Hans,

    I tried 2 first. It gives an error on the Set fld = td.fields("Comments"). It was a runtime error Object not found.

    Option 1 works like a charm!

    Thanks,
    Tom

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

    Re: Field Exists (2K)

    Strange - the line On Error Resume Next should suppress error messages. But if 1 works, never mind.

  6. #5
    Star Lounger
    Join Date
    Nov 2005
    Location
    Edmonton, Alberta, Canada
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Exists (2K)

    Do you happen to have "Break on all errors" turned on (Tools Options General in VB)?

  7. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Exists (2K)

    No Break on Unhandled Errors.

  8. #7
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Exists (2K)

    Hans,

    I just read your response regarding On Error Resume. I realized that I missed entering that line in my code. SO, now I wish to say either method works fine. I am going to go with the second method. Sorry for the confusion, and thanks again.

    Tom

Posting Permissions

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