' ----------------------------------------------------------------
' Procedure Name: docForms
' Purpose: To document all controls on all forms in this database and indicate Date
' and store results in table tblzzFormDocumentation
' Procedure Kind: Function
' Procedure Access: Public
' based on material from Tektips (2017)
' Author: Jack
' Date: 14-May-22
' ----------------------------------------------------------------
Public Function docForms()
10 On Error GoTo docForms_Error
20 On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim ctrl As Control
Dim frm As Form
Dim FName As String
Dim cName As String
Dim PName As String
Dim pValue As String
Dim prp As Property
Dim ctrlPropSQL As String
30 Set dbs = Application.CurrentProject
'FOR EACH FORM
40 For Each obj In dbs.AllForms
'OPEN THE FORM IN DESIGN VIEW
'NEEDS TO BE OPEN, TO GET AT EACH CONTROL - DESIGN VIEW SO WE DON'T INITIALIZE ANY CODE
50 DoCmd.OpenForm obj.name, acDesign
'FOR EACH CONTROL ON THE FORM
60 For Each ctrl In Forms(obj.name).Controls
'IF THE CONTROL TYPE IS NOT A LABEL
70 If ctrl.ControlType <> acLabel Then
'LOOP THROUGH EACH PROPERTY ON THE CONTROL
80 For Each prp In ctrl.Properties
90 pValue = Nz(prp.value, "")
'IF PROPERTY VALUE ISNT BLANK OR NULL
100 If pValue <> "" Then
110 FName = obj.name
120 cName = ctrl.name
130 PName = prp.name
'MUST HAVE A TABLE WITH THOSE COLUMN NAMES - THIS IS IN SQL AND IS A LINKED TABLE, IN MY SET UP
'I ALSO HAVE AN ID COLUMN, WHICH IS AN IDENTITY, AND THE PROPERTYVALUE IS VARCHAR(MAX)
'PREPARE SQL INSERT STATEMENT WITH EACH COLUMN VALUE
140 ctrlPropSQL = "INSERT INTO tblzzFormDocumentation ([FormName],[ControlName],[PropertyName],[PropertyValue], DateChecked) VALUES ('" & FName & "','" & _
cName & "','" & PName & "','" & pValue & "', Date() );"
'EXECUTE SQL
150 CurrentDb.Execute ctrlPropSQL, dbSeeChanges
160 End If
170 Next prp
180 End If
190 Next ctrl
'CLOSE THE FORM, ONCE DONE LOOPING THROUGH ALL CONTROLS
200 DoCmd.Close acForm, obj.name, acSaveNo
210 Next obj
220 MsgBox "Forms have been documented @ : " & Now
230 On Error GoTo 0
docForms_Exit:
240 Exit Function
docForms_Error:
250 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure docForms, line " & Erl & "."
260 GoTo docForms_Exit
End Function