Inspect the Control Source of All Controls in Your MS Access Project
Happy New Year!
Recently I was working on a project where I was implementing form spawning so that my client can see at least two different records using two identical forms, one of the actual forms and the other one a spawned version of itself, (a topic I’ve discussed at my SQL Server with Access meetings, click to join the announcement list!). Because the prior developer did not use form spawning, I had to search all instances in control source of all forms to identify references to the original form and replace it with TempVars.
The project has dozens of forms and thousands of controls, so I created the following code to scan all form record sources for the offending row source and the control source of all controls on the form as well.
Public Sub ScanForms()
On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim ctrl As Control
Dim strRowsource As String
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
‘Debug.Print “Working on: ” & obj.Name
DoCmd.OpenForm obj.Name, acDesign
strRowsource = Forms(obj.Name).RecordSource
If Err.Number Then
strRowsource = vbNullString
End If
If Len(strRowsource) Then
If InStr(1, strRowsource, “frmPatientProcessing”) > 0 Then
Debug.Print “Form: ” & obj.Name
End If
End If
For Each ctrl In Forms(obj.Name).Controls
On Error Resume Next
strRowsource = ctrl.ControlSource
If Err.Number Then
strRowsource = vbNullString
End If
On Error GoTo 0
If Len(strRowsource) Then
If InStr(1, strRowsource, “frmPatientProcessing”) > 0 Then
Debug.Print “Form: ” & obj.Name & ” Control:” & ctrl.Name
End If
End If
Next ctrl
DoCmd.Close acForm, obj.Name
Next obj
End Sub
Thanks Juan, in the article you say you were using TempVars to identify references to the original form while now you say you used TempVars to identify which record to display from all the records in memory. Is that the same thing? Can you give an example of how you used TempVars in this case?
Sorry, I got it now but can you still give an example of how you used TempVars in this case?
Why did you have to replace references to the original form with TempVars?
The customer requested I implement form spawning, which meant that references to the form would not work, since there would be multiple copies of the form in memory and each displaying a different record. I used TempVars to identify which record to display from all the records in memory.