Well perhaps you did have more need. Can you post a copy of the database so readers may help identify the issue?
I have used these routines to identify
-recordsources of all forms
-controls on all forms.
Code:
'---------------------------------------------------------------------------------------
' Procedure : PutFormRecordSourcesInTable
' Author : Jack
' Date : 02/03/2014
' Purpose : Review all forms in this database; find recordsource for each.
' If no recordsource bypass the form.
' if recordsource is a Table/Query identify the table/query and fields.
' if recordsource is SQL, identify the SQL string.
'---------------------------------------------------------------------------------------
'
Sub PutFormRecordSourcesInTable()
Dim afrm As AccessObject
Dim frm As Access.Form
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RecSourceType As String
Dim strSQL_Drop As String
Dim strSQL_Create As String
10 On Error Resume Next
20 Set db = CurrentDb
'Delete existing copy of this table
30 strSQL_Drop = "DROP TABLE tblRecordSourceOfForms;"
40 DoCmd.RunSQL strSQL_Drop
50 On Error GoTo PutFormRecordSourcesInTable_Error
60 strSQL_Create = "CREATE TABLE tblRecordSourceOfForms" & _
" (form_name varchar(250), RecordSourceType varchar(20),RecordSourceText longtext );"
70 db.Execute strSQL_Create, dbFailOnError
80 DoEvents
90 Set rs = db.OpenRecordset("tblRecordSourceOfForms")
100 For Each afrm In CurrentProject.AllForms
110 If Not afrm.IsLoaded Then DoCmd.OpenForm afrm.name, acDesign, , , , acHidden
120 If Len(Forms(afrm.name).RecordSource & "") = 0 Then
130 Debug.Print afrm.name & " -- " & "**NO ASSIGNED RECORDSOURCE**"
140 RecSourceType = "NONE"
150 ElseIf InStr(Trim(Forms(afrm.name).RecordSource), "SELECT ") > 0 Then
160 Debug.Print afrm.name & " -- " & " - SQL - " & Forms(afrm.name).RecordSource
170 RecSourceType = "SQL"
180 Else
190 Debug.Print afrm.name & " -- " & " - Table/Query - " & Forms(afrm.name).RecordSource
200 RecSourceType = "Table/Query"
210 End If
220 rs.AddNew
230 rs!form_name = afrm.name
240 rs!RecordSourceType = RecSourceType
250 rs!RecordSourceText = Trim(Forms(afrm.name).RecordSource)
260 rs.Update
GetNext:
270 DoCmd.Close acForm, afrm.name
280 Next afrm
290 rs.Close
300 On Error GoTo 0
310 Exit Sub
PutFormRecordSourcesInTable_Error:
320 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PutFormRecordSourcesInTable of Module AWF_Related"
End Sub
Code:
'---------------------------------------------------------------------------------------
' Procedure : GetAllFormsAndControls
' Author : Jack
' Date : 12/01/2013
' Purpose : To iterate all forms and report all controls by form, control name and control type.
'
' posted question:
'http://www.accessforums.net/showthread.php?t=31409&p=154208#post154208
'---------------------------------------------------------------------------------------
'
Sub GetAllFormsAndControls()
10 On Error GoTo GetAllFormsAndControls_Error
20 On Error Resume Next
Dim objAccObj As AccessObject
Dim objForm As Object
Dim strForm As String
Dim ctl As Control
Dim objActiveForm As Form
Dim iCtlCount As Integer
'
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL_Drop As String
Dim strSQL_Create As String
'Delete existing copy of this table
30 strSQL_Drop = "DROP TABLE tblControlsOnForms;"
40 DoCmd.RunSQL strSQL_Drop
50 strSQL_Create = "CREATE TABLE tblControlsOnForms" & _
"(form_name varchar(250), control_name varchar(40),control_type varchar(25));"
60 DoCmd.RunSQL strSQL_Create
70 Set db = CurrentDb
80 Set rs = db.OpenRecordset("tblControlsOnForms")
90 With rs
100 Set objForm = Application.CurrentProject
110 For Each objAccObj In objForm.AllForms
120 iCtlCount = 0
130 strForm = objAccObj.name
' If strForm <> "frmEATBloatV4" Then GoTo XXX 'for debugging
140 Debug.Print strForm
150 DoCmd.OpenForm strForm, acDesign
160 Set objActiveForm = Application.Screen.ActiveForm
170 For Each ctl In objActiveForm.Controls
180 iCtlCount = iCtlCount + 1
190 .AddNew
200 !form_name = strForm
210 Select Case ctl.ControlType
Case 119 ' acWebBrowser, Treeview, Calendar
220 !control_type = "Custom control"
230 Case acTabCtl
240 !control_type = "TabCtl"
250 Case acLabel
260 !control_type = "Label"
270 Case acTextBox
280 !control_type = "TextBox"
290 Case acComboBox
300 !control_type = "ComboBox"
310 Case acCheckBox
320 !control_type = "CheckBox"
330 Case acListBox
340 !control_type = "ListBox"
350 Case acOptionButton
360 !control_type = "OptionButton"
370 Case acToggleButton
380 !control_type = "ToggleButton"
390 Case acSubform
400 !control_type = "SubForm"
410 Case acCommandButton
420 !control_type = "CommandButton"
430 Case acObjectFrame
440 !control_type = "ObjectFrame"
450 Case acBoundObjectFrame
460 !control_type = "BoundObjectFrame"
470 Case acRectangle
480 !control_type = "Rectangle"
490 Case acLine
500 !control_type = "Line"
510 Case acImage
520 !control_type = "Image"
530 Case acPage
540 !control_type = "Page"
550 Case acPageBreak
560 !control_type = "PageBreak"
570 Case acOptionGroup 'some sort of frame???
580 !control_type = "Option Group"
590 End Select
600 Debug.Print " " & ctl.name & " " & ctl.ControlType
610 !control_Name = ctl.name
620 !control_type = !control_type & " " & ctl.ControlType ' used to associate the control with the acControl constant
630 .Update
640 Next ctl
650 Debug.Print strForm; " ---Controlcount--: " & iCtlCount
660 DoCmd.Close acForm, strForm
XXX:
670 Next objAccObj
680 On Error GoTo 0
690 Exit Sub
700 End With
GetAllFormsAndControls_Error:
710 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure GetAllFormsAndControls of Module AWF_Related"
End Sub
These may help. Good luck.
Always work on a copy of your database.