Changing the Form properties for Popup, Modal, and Allow Design Changes

[Jack Peyton]

I am using Access 2000. I have 15 forms that have the Tag property set to 1.
I currently use a command button on a Form to change all of the Form
properties for Popup and Modal from No to Yes, and Allow Design Changes from
All Views to Design View Only. The following is a sample of the code that is
used for each of the forms.

DoCmd.OpenForm "FParkLot", acDesign, , , , acHidden
If Forms!FParkLot.Tag = 1 Then
Forms!FParkLot.PopUp = -1
Forms!FParkLot.Modal = -1
Forms!FParkLot.AllowDesignChanges = 0
DoCmd.Close acForm, "FParkLot"
End If

Is there a way to change this to loop through each of the 15 forms in the
Database without having to list each form?

Any advice will be appreciated.
Jack Peyton

 

[Tom Wickerath]

Hi Jack,

Here is some code I wrote a few years ago, which I've hardly looked at since
so I obviously have not tested it throughly. You are welcome to see if this
helps you out, by adding your code within the loop.

Option Compare Database
Option Explicit

Function EnumerateAllFormsAndReports(Optional objName As Variant)
' This code enumerates all forms and reports, and their recordsources.
' It requires the user to have design permissions
' Written by Tom Wickerath, 2/4/2004.
'
' Example usage from debug window:
' ? EnumerateAllFormsAndReports() All forms and reports.
' ? EnumerateAllFormsAndReports("") All forms and reports with no
recordsource.
' ? EnumerateAllFormsAndReports("tblCustomers") All forms and reports
with tblCustomers as recordsource.
'
Dim db As DAO.Database
Dim ctr As Container
Dim doc As Document
Dim i As Integer

Set db = CurrentDb()
i = 0

' Enumerate all forms
Debug.Print "Forms:" & vbTab & "Recordsource"
Set ctr = db.Containers!Forms

For Each doc In ctr.Documents
DoCmd.OpenForm doc.Name, acDesign
If Not IsMissing(objName) Then
If Forms(doc.Name).RecordSource = objName Then
Debug.Print doc.Name & ": " & Forms(doc.Name).RecordSource
i = i + 1
End If
Else
Debug.Print doc.Name & ": " & Forms(doc.Name).RecordSource
i = i + 1
End If
DoCmd.Close acForm, doc.Name
Next doc

If i = 0 Then
Debug.Print "No Forms"
End If

' Reset counter and enumerate all reports
i = 0
Set ctr = db.Containers!Reports
Debug.Print
Debug.Print "Reports:" & vbTab & "Recordsource"

For Each doc In ctr.Documents
DoCmd.OpenReport doc.Name, acDesign
If Not IsMissing(objName) Then
If Reports(doc.Name).RecordSource = objName Then
Debug.Print doc.Name & ": " & Reports(doc.Name).RecordSource
i = i + 1
End If
Else
Debug.Print doc.Name & ": " & Reports(doc.Name).RecordSource
i = i + 1
End If
DoCmd.Close acReport, doc.Name
Next doc

If i = 0 Then
Debug.Print "No Reports"
End If

End Function




Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

PS. I just discovered a run-time error when I copied the code that I posted
into a different database. The code compiled fine, but gave a run-time error
Type Mismatch. However, the same code ran just fine in my source database.

The problem is solved by using explicit declaration of two objects that are
found in more than one library. To help make the code a little bit more
bullet-proof, replace the following two lines of code:

Dim ctr As Container
Dim doc As Document

with this:

Dim ctr As DAO.Container
Dim doc As DAO.Document

Also, I didn't mention this, but the code requires a reference set to the
"Microsoft DAO 3.6 Object Library". It turns out that the new test database
I pasted the code into had a reference set to the Microsoft Word Object
Library, and this library was higher in priority than my DAO Object Library.
Thus, the run-time error 13: Type mismatch. Arrrggghhh!!

Here is an article that I wrote earlier that talks more about this:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jack Peyton]

Terry,
Thanks for the info. I will give it a try
Jack Peyton