Get control names from form in another database 1

[wemeier]

I am trying to develop an Access application that will use VBA code to open another Access database and create an ASCII file of the names of all forms in that database and the names of the controls contained within each form.

When the forms are in the local database I can walk through the Forms container and open each form in design mode, then walk through the form's controls. However, I can't find a way to open a form in a foreign database in design mode.

Does anyone have any ideas for how to do this? Any help would be greatly appreciated.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[RoyVidar]

When you've opened the other db through automation, you should be able to do something along the lines of this air code

[tt]dim frm as object
dim frm1 as form
dim ctl as control

for each frm in objAccess.currentproject.allforms
debug.print frm.name
objAccess.docmd.openform frm.name, acdesign
set frm1 = forms(frm.name)
for each ctl in frm1.controls
debug.print ctl.name
next ctl
objAccess.docmd.close acform, frm.name
next frm[/tt]

Roy-Vidar

 

[wemeier]

RoyVidar,

Thanks for the quick reply. I'm not familiar with .currentproject.allforms. I'm still using Access 97. Is this in a later version? If so, can you think of anything I can do in Access 97? I tried your code, but it rejected.



Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[ZmrAbdulla]

I think it is [tt]CurrentDb[/tt] in access97


________________________________________________________
Zameer Abdulla
Help to find Missing people
Do not cut down the tree that gives you shade.

 

[RoyVidar]

Ah, yes, currentproject and the All<object> collections were introduced in the 2000 version, in previous versions, I think you need to go through the Documents collection of the Container object.

Perhaps something like this?

[tt]dim frm as object
dim frm1 as form
dim ctl as control
dim db as dao.database

dim objAccess as Access.Application
set objAccess = createobject("Access.Application")
objAccess.opendatabase "<path and name .mdb>"

set db = objAccess.CurrentDB
for each frm in db.containers("Forms").Documents
debug.print frm.name
objAccess.docmd.openform frm.name, acdesign
set frm1 = forms(frm.name)
for each ctl in frm1.controls
debug.print ctl.name
next ctl
objAccess.docmd.close acform, frm.name
next frm[/tt]

Roy-Vidar

 

[TheAceMan1]

How are ya wemeier . . .

Copy/Paste the following routine in a [blue]module[/blue] in the [blue]modules window[/blue]. Then enter [purple]Call GetAllFrmCtls[/purple] in the immediate window and hit enter. The code [blue]prints the control name & type[/blue]:

[blue]Public Sub GetAllFrmCtls()
   Dim objAcc As Access.Application, obj As AccessObject
   Dim frm As Form, ctl As Control, Typ As String
   
   DoCmd.Hourglass True
   Set objAcc = New Access.Application
   objAcc.OpenCurrentDatabase "C:\Database\Perry\Perry.mdb"
   
   For Each obj In objAcc.CurrentProject.AllForms
      objAcc.DoCmd.OpenForm obj.Name, acDesign
      Set frm = objAcc.Forms(obj.Name)
      Debug.Print obj.Name
                                                                     
      For Each ctl In frm.Controls
         Debug.Print "    " & ctl.Name & ".";
         Typ = Choose(ctl.ControlType - 99, "Label", "Rectangle", _
                      "Line", "Image", "CommandButton", "OptionButton", _
                      "CheckBox", "OptionGroup", "BoundObjectFrame", _
                      "TextBox", "ListBox", "ComboBox", _
                      "SubForm/SubReport", , _
                      "UnboundObject Frame/Chart" _
                      , , , , _
                      "PageBreak", "ActiveX/CustomControl" _
                      , , , , _
                      "Tab", "Page")
         Debug.Print Typ
      Next
      
      Set frm = Nothing
      objAcc.DoCmd.Close acForm, obj.Name, acSaveNo
   Next
   
   objAcc.CloseCurrentDatabase
   Set objAcc = Nothing
   DoCmd.Hourglass False

End Sub[/blue]

See Ya! . . . . . .

 

[RoyVidar]

Found a typo in both in the first reply, and the one for the 97 version, sorry (could be more though;-)).

[tt]set frm1 = objAccess.forms(frm.name)[/tt]

Roy-Vidar

 

[wemeier]

RoyVidar,

Thanks very much for the help. I had to tweak it a bit, but it works fine!

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]