Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    34

    Unanswered: Show tables used in forms

    I'm a newbie and looking for a way to show all forms that either grab data from a table or are used to fill in data to a table. I have discovered a flaw in my database with a relationship and need to create a new table and redesign 3 other tables. My concern is that forms referring to the original tables will have errors. Is there any way to show me this info. Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Access all forms

    In Access 2003 you can use the dependancies view to show what table is being used in which form. Right-click on the table and select 'Object Dependencies' to see all objects that depend on that table. This only works in Access 2003 MDB format and not as a data project.

    Alternatively you can use this code (only from Access 2000 onwards):

    Dim obj As Access.AccessObject

    For Each obj In CurrentProject.AllForms
    Debug.Print "Name: " & obj.Name
    DoCmd.OpenForm obj.Name, acDesign
    Debug.Print "RecordSource: " & Forms(obj.Name).RecordSource
    DoCmd.Close acForm, obj.Name

    Next obj

    This code loops through each form and extracts the form name and the forms recordsource. From the record source you can see what table each form uses. Access has to open each form (in design mode) to see the form properties as these properties only exist when the form is open.

    This code won't pick up changes to the record source made by any VBA code that you have written

    Regards
    Justin

  3. #3
    Join Date
    Aug 2004
    Posts
    34
    I am using 2k and very new to VB...where would I put the code you mentioned

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Create a new form, place a button on the form and the in the Click event of the button place the code. Run the form and press the button. The results are displayed in the Immediate window in the code screen (press Ctrl + G to view the immediate window)

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The code will help you for the recordsource of a form, but you might run into problems with other controls. If you are using combo boxes that pull data from those tables or list boxes, or subforms, or if you do any dynamic SQL statements. Just my two cents.

  6. #6
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Thats a good point, but you could extend the code to loop through all the combo and list boxes on a form and extract the row source property. You would just need to extend the code to loop through all the controls on each form

  7. #7
    Join Date
    Aug 2004
    Posts
    34
    I ran the code and it worked great, but I do need to see recordsource for combo boxes...How do I extend code??? Thanks to both

    ED

Posting Permissions

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