Solved

Loop through controls on a form in a different database

Posted on 2009-07-02
18
1,232 Views
Last Modified: 2012-05-07
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
  Next

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.

Thanks.

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



0
Comment
Question by:vlvawter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +2
18 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 24764622
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?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 24764706
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

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24765109
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:thenelson
ID: 24765176
Hi Jim,

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

I was just asking.

Nelson
0
 

Author Comment

by:vlvawter
ID: 24765957
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

Open in new window

0
 

Author Comment

by:vlvawter
ID: 24766747
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?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24767371
3:  DoCmd OpenForm db.Containers("Forms(i).Name
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24767381
Sorry missing a parenthesis

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

Author Comment

by:vlvawter
ID: 24767653
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.
0
 
LVL 57
ID: 24771993
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
  

Open in new window

0
 

Author Comment

by:vlvawter
ID: 24772157
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
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 24772338
<<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.
JimD.
0
 

Author Comment

by:vlvawter
ID: 24772415
JDettman:  Thanks, but I'll still make sure you get the points.
0
 

Author Closing Comment

by:vlvawter
ID: 31599229
Please excuse my delay in getting the points to you.  You really helped.
0
 

Author Comment

by:vlvawter
ID: 24900569
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?
0
 
LVL 57
ID: 24903682
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.
0
 

Author Comment

by:vlvawter
ID: 24903857
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.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

732 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