Loop through controls on a form in a different database

I'm creating a table for all the form controls in a different database.

I open the other database using vba and DAO, Set db = OpenDatabase([Database Name]).  No problem.

Using containers I can get the names of the forms in the database, again no problem.

  For i = 0 To db.Containers("Forms").Documents.Count - 1

I cannot figure out how to get the controls on the form names that I have.

I've tried
  Set frm as db.Containers("Forms").Documents(i).  Type mismatch.

In the database which has the table I am creating with form controls name, how do I loop through the form controls in a different database in order to get the controls name, or whatever I want from the form controls.


PS  PLEASE do not ask why I want this.  How can it be done within the VBA code for a different database?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

To get the names of the controls, the form needs to be open in either design or view mode. To open the form in the other database will require the other database to be opened.

You can open the form in this database (probably only in design mode unless both databases have the same table structures).  In the Visual Basic Editor, go to Tools > References. Click on browse, select the other DB. The modules of the other DB are now available in this DB. You will not see the forms and reports in the main database container window so you use DoCmd.OpenForm and DoCmd.OpenReport to open the forms and reports.  You can reference the objects through VB as if they are in this database.

Once you open the form, You get the controls with:
Dim ctl As Control

For Each ctl In Forms!YourFormName
        Something = ctl.Name
Next ctl

Do you think this is worth more than 125 points?
try this code

Private Sub Command1_Click()
'On Error GoTo Err_Command1_Click
   Dim db As DAO.Database
   Dim ctrLoop As Container
   Dim prpLoop As Property
   Dim ctl As Control
   Dim frm As Form
   Set db = OpenDatabase("c:\Frames_Colors.mdb")
   With db
      ' Enumerate Containers collection.
      For Each ctrLoop In .Containers
         'Debug.Print "Properties of " & ctrLoop.Name _
            & " container"
         If ctrLoop.Name = "Forms" Then
         For Each frm In Forms
            'Debug.Print frm.Name
            For Each ctl In Controls
                Debug.Print ctl.Name
            Next ctl
         Next frm
         End If
      Next ctrLoop
   End With
    Exit Sub
    MsgBox Err.Description
    Resume Exit_Command1_Click
End Sub

Open in new window

hi jaffer:  Once you've identified the Forms container, then loop through the contents, and open each one first before looping through the Controls.  You will not get access to the controls of a form until it is opened.

docmd.openform frm.name
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Hi Jim,

<<Do you think this is worth more than 125 points? >>

I was just asking.

vlvawterAuthor Commented:
I certainly appreciate the help, but something is still missing.  

In the code snippet, everything works fine except for actually opening the form.

As you can see, I loop specifically through the Container that has Forms in it.  I can debug.print the form name with no problem.

I have tried all kinds of variations to open the form so I can loop through the controls.  

Does docmd.openform assume the form is inside my current project as opposed to being in another database?

Obviously I can use vba to import the form; I can easily open it, etc.  Is there a way around importing?  What is the code for the docmd.open form in my snippet.

Docmd.OpenForm db.Containers("Forms").Documents(i).Name  gives a type error.

Is importing (and then deleting) the form my only option?


PS  I'll gladly give 375 points for the code that works if I can avoid importing.  (I can't give more.  It's all I have.)
  Do Until rsDB.Recordset.EOF
    Set db = OpenDatabase(rsDB.Recordset![Database Name])
    For i = 0 To db.Containers("Forms").Documents.Count - 1
      DoCmd.OpenForm ????

Open in new window

vlvawterAuthor Commented:
I've noticed no one has responded since my last posting.

I'll buy the points and move the value to 500.  Is there an answer to my question?
3:  DoCmd OpenForm db.Containers("Forms(i).Name
Sorry missing a parenthesis

3:  DoCmd OpenForm db.Containers("Forms)(i).Name
vlvawterAuthor Commented:

No, that won't work.

I think you meant to type Docmd.OpenForm db.Containers("Forms")(i).Name.

The compiler simply won't take that.

I've tried Docmd.OpenForm db.Containers("Forms").Documents(i).Name.

If Debug.Print db.Containers("Forms").Documents(i).Name is used, the correct forms names are printed. I'm done it plenty of times.

However, if the same variable is used with openform (as I illustrated), the programs has a runtime error 2102, the name is misspelled or refers to a form that doesn't exist.

I'm back to the importing issue.  

It doesn't appear there is a way to access a form's controls from a different database without importing that form, for it seems that docmd.openform only works with forms within the current database.

If I'm wrong, please show me.  I don't want to import (and delete) because I'm talking about dozens of forms.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You should find the attached code workable. It's not pretty because I don't have much time at the moment.
It uses automation to open another instance of Access, load the DB in question, open all forms, and then loop through it's controls.

Sub DisplayControlsOnFormsInRemoteDBs()
    On Error Resume Next
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim appAccess As Access.Application
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblDBs")
    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
    Do While Not rst.EOF
      ' Open database in other instance of Microsoft Access.
      Debug.Print "DATABASE ------------------>" & rst![DatabaseName]
      appAccess.OpenCurrentDatabase rst![DatabaseName]
      For intJ = 0 To appAccess.CurrentDb.Containers("Forms").Documents.Count - 1
        ' Open the form.
        Debug.Print "FORM --------------->" & appAccess.CurrentDb.Containers("Forms").Documents(intJ).Name
        appAccess.DoCmd.OpenForm appAccess.CurrentDb.Containers("Forms").Documents(intJ).Name
        For intK = 0 To appAccess.Forms(appAccess.CurrentDb.Containers("Forms").Documents(intJ).Name).Controls.Count - 1
          Debug.Print appAccess.Forms(appAccess.CurrentDb.Containers("Forms").Documents(intJ).Name).Controls(intK).Name
        Next intK
        appAccess.DoCmd.Close acForm, appAccess.CurrentDb.Containers("Forms").Documents(intJ).Name
      Next intJ
      ' Get next database to check
    Set appAccess = Nothing
    Set rst = Nothing
    Set db = Nothing
End Sub

Open in new window

vlvawterAuthor Commented:

I'm in the middle to something right now.  I'll check your code later.  Yesterday, I played with creating another instance of Access, but I had trouble maneuvering through the objects.  I see in your code how it's done.

Once I see it works I'll buy the points and get them to you.  Don't be concerned if it's not until Sunday or Monday.


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Once I see it works I'll buy the points and get them to you.  Don't be concerned if it's not until Sunday or Monday.>>
Don't worry about the points.  Let's just get your problem solved.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vlvawterAuthor Commented:
JDettman:  Thanks, but I'll still make sure you get the points.
vlvawterAuthor Commented:
Please excuse my delay in getting the points to you.  You really helped.
vlvawterAuthor Commented:
JDettman: When I awarded the points, it didn't give me (or I didn't see) how to give you the extra points. I want to do that.  How do I give you the extra points?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No need for any additional points.   I help out when I can and where I can and that's enough for me.
Have a great day!
vlvawterAuthor Commented:
If you ever answer another question for me, I'll make a point of giving an extra 350 points; but I'l check to make sure I can add them before I accept your solution.  I'm still not sure what I did wrong.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.