?
Solved

returning form properties using vba

Posted on 2017-11-14
6
Medium Priority
?
39 Views
Last Modified: 2017-11-14
Is there a way, using VBA, to read through the list of forms in an Access database and return the setting for a specific property?  Specifically I would like to see what the MinMaxButtons property is set to on each form in the database
0
Comment
Question by:dsoderstrom
  • 3
  • 2
6 Comments
 
LVL 24
ID: 42367546
yes, you can run the free Analyzer here:

https://github.com/strive4peace/Analyzer

and then make a query using the a_Forms1 table with:

  • f_Name
  • f_MinMaxButtons

If you have only analyzed one database, you will not need to filter the query for DbID (assigned each time an analysis is done).

have an awesome day,
crystal
0
 
LVL 41

Expert Comment

by:PatHartman
ID: 42367625
This will print out that specific property for all forms in the database.
Sub sDisplaySpecificProperty()
    Dim db As DAO.Database
    Dim ctr As Container
    Dim doc As Document
    Dim frm As Form
    Set db = DBEngine(0)(0)
    Set ctr = db.Containers!Forms
    For Each doc In ctr.Documents
        DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
        Set frm = Forms(0)
        Debug.Print frm.Name & " -- " & frm.MinMaxButtons
        frm.ShortcutMenu = False
        DoCmd.Close acForm, doc.Name, acSaveYes
    Next doc
End Sub

Open in new window

0
 

Author Comment

by:dsoderstrom
ID: 42367770
Pat,
   I am trying to run your code and it's not working quite right.
   First, I took out the line "frm.ShortcutMenu = False" and I changed the Docmd.Close from acSaveYes to acSaveNo.  I don't want to modify anything.
   The problem I have is on the "Set frm = Forms(0).  After executing this line I'm thinking that the frm.name on the following line should match the doc.name on the preceding line but it does not.  frm.name is the name of a form but it does not match the doc.name.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 41

Expert Comment

by:PatHartman
ID: 42367842
I didn't write the code.  I modified it to print out the specific property you wanted to see.

There is a bug and I'll see if I can figure it out.  It looks like when you run it the first time, you get a bogus list but if you run it a second time, the list is correct.  Try that for now and I'll look at the code.  I thought the bug was fixed once I got a good list.  I never closed the app and then reopened it and tried again.  It is a problem only the first time you run it.

The save property was set because the original code actually modified form properties so rather than removing the line of code, comment it out so it will jog your memory if you want to convert this to actually changing the property.
0
 
LVL 41

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 42367874
I think I fixed it.  I changed this line --  Set frm = Forms(0) to  Set frm = Forms(doc.Name)
I also changed the Set db line to use Currentdb because I seem to remember some problem with the way the DBEngine(0)(0) reference refreshes.  I have no idea why the change I made fixed the problem.  I'm guessing that the Form(0) reference wasn't keeping up with the loop.  The real mystery is why it works the second time you run the loop.
Sub sDisplaySpecificProperty()
    Dim db As DAO.Database
    Dim ctr As Container
    Dim doc As Document
    Dim frm As Form
    Set db = CurrentDb
    Set ctr = db.Containers!Forms
    For Each doc In ctr.Documents
        DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
        Set frm = Forms(doc.Name)
        Debug.Print frm.Name & " -- " & frm.MinMaxButtons
        '''DoCmd.Close acForm, doc.Name, acSaveYes      '' use this line if you actually update the property
        DoCmd.Close acForm, doc.Name
    Next doc
End Sub

Open in new window

0
 

Author Closing Comment

by:dsoderstrom
ID: 42368004
Thanks Pat.  It works perfectly now.
And thanks Crystal for the link to the Analyzer.  I will be taking a closer look at it when I get a chance.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question