Loop through controls on a form in a different database

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

jaffer

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
 
      .Close
   End With
 
 
Exit_Command1_Click:
    Exit Sub
 
Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

Select allOpen 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

Hi Jim,

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

I was just asking.

Nelson

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?

Thanks.

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 ????
    Next
    rsDB.MoveNext
  Loop

Select allOpen in new window

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

GRayL:

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.

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.
JimD.

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
      rst.MoveNext
    Loop
    
    appAccess.Quit
    Set appAccess = Nothing
    
    rst.Close
    Set rst = Nothing
    
    Set db = Nothing
    
End Sub
  

Select allOpen in new window

JDettman:

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.

Thanks.

VL

JDettman:  Thanks, but I'll still make sure you get the points.

Please excuse my delay in getting the points to you.  You really helped.

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?

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!
JimD.

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.