Good way to locate occurrence of table and queries, Form use

[jase2006]

Hi, I would like to locate the occurrence a certain table and query in all Forms. Is there a faster way than going into Form design mode and check the properties to see what table or query is used?

 

[Remou]

Use code.

For Each frm In CurrentProject.AllForms
    DoCmd.OpenForm frm.Name, acDesign
    Debug.Print Forms(frm.Name).RecordSource
    DoCmd.Close acForm, frm.Name, acSaveNo
Next

 

[jase2006]

Can you please tell me what frm is declare and set as?
Thanks

 

[Remou]

There is no need to set. You can:

Dim frm As Object

 

[jase2006]

Right I declare frm AS Object. But is "CurrentProject" available in ACCESS 97? It doesn't seem to recognise it?

 

[Remou]

Access 97 is quite different from the more recent releases. It is best that you mention the version in all your posts. I cannot test this, but it may suit:

Dim rs As Recordset

strSQL = "SELECT [Name] FROM MsysObjects " _
& "WHERE [Type] = -32768"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF
    Debug.Print rs!Name

    DoCmd.OpenForm rs!Name, acDesign
    Debug.Print Forms(rs!Name).RecordSource
    DoCmd.Close acForm, rs!Name, acSaveNo
    
    rs.MoveNext
Loop

 

[jase2006]

That is perfert, just need to little tweaking.

Thanks again

 

[MichaelRed]

but be aware that other constructs may reference the table/query. ComboBoxes and ListBoxes have a "RowSource" property which may be set to a table/query and Domain aggregate function (DSum, DMin, DMax, ... ) may be used as the control source ...

Other examples abound, so these are just the most common.

If your quest is to determine if the object (table / query) is used anywhere, the quest is a LOT more complicated than just the form recordsource!




MichaelRed

 

[jase2006]

Thanks for the advice.