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
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
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
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)
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.
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