Solved

Loop through controls on a form in a different database

Posted on 2009-07-02
18
1,192 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now