Modify Record source for form with VBA

[BAC]

Vista Ultimate, Access 2007, SQL Server 2008
I have an App with over 150 forms
I have been tasked to change the recordsource for all forms that currently
have a SELECT statement in their recordsource to a view and to change the
controlsource for all controls that have a select statement to a view as well
(i.e. remove all the SELECT statements from all the forms..
To ID which forms need changing, I am trying to get a list of the forms that
have select statements and their controls:

for each frm in forms
Debug.print frm.name, frm.recordsource
next frm
does fine for open forms only

for each frm in currentdb.allforms
Debug.print frm.name
for i = 0 to frm.properties.count -1
debug.print frm.properties(i).name, frm.properties(i).value
next i
next frm

Only prints the form's name ( as if the forms have no properties!)

SO: How do I get a list of all the forms (open or not) and their
recordsource AND the control source for the controls?

Intellisense does not offer a recordsource property for the ALLForms approach.
The VBA code reference in MSDN, the microsoft's KB and a Google search have
not been particularly clear in this.

TIA
BAC

 

[Daniel Pineault]

A simple google search provides all the information you need to accomplish
this. Try something similar to:

Dim obj As AccessObject, dbs As Object
Dim sFormName As String
Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
sFormName = obj.Name
Docmd.OpenForm sFormName ,acDesign
Forms(sFormName ).RecordSource = "YourNewRecSourceGoesHere"
Docmd.Close acForm,sFormName,acSaveYes
Next obj

Make sure all the forms are close, nothing open, prior to running.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Douglas J. Steele]

The AllForms collection contains AccessObject object (not the Form objects
themselves), and the only properties associated with an AccessObject object
is the name and object type.

To get at the properties of the form itself, you need to open the form so
that you can refer to it.

Dim dbCurr As DAO.Database
Dim objForm As AccessObject
Dim frmCurr As Form
Dim i As Long

Set dbCurr = CurrentDb()

For Each objForm in dbCurr.AllForms
DoCmd.OpenForm objForm.Name, View:=acDesign, _
WindowMode:=acHidden
Set frmCurr = Forms(objForm.Name)
Debug.print objForm.Name
For i = 0 To (frmCurr.Properties.Count -1)
Debug.Print frmCurr.Properties(i).Name, frmCurr.Properties(i).Value
Next i
DoCmd.Close acForm, objForm.Name, acSaveNo
Next objForm

Set frmCurr = Nothing
Set dbCurr = Nothing