open a form using application object

[juneBug via AccessMonster.com]

Hello.

I am losing my mind and need a bit of help...the task is simple...open a MS
Access (97) form in another database and pass its recordsource as a string
back to the current db.

Here is what I got...I managed to get it working then I messed it up somehow..
...grrr. The function below takes 2 parameters...objName (name of the object I
want to open) and objType (type of the object- form, table etc). I cut out
the other case statements dealing with tables/queries etc. I am only
interested in forms right now.

'''''''''''''''''''''''''''''''''''''''''''''''''
'start of code
'''''''''''''''''''''''''''''''''''''''''''''''''
Public Function getRecordSource(inObjType As String, inObjName As String) As
String
Dim myForm As Form, myApp As Access.Application

Select Case inObjType
Case "Form"
Set myApp = New Access.Application
myApp.OpenCurrentDatabase [enter db name/location]
MsgBox myApp.Forms(inObjName).RecordSource
myApp.CloseCurrentDatabase
end select
Set myApp = Nothing
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''
'end of code
'''''''''''''''''''''''''''''''''''''''''''''''''
Right now the other database opens but I keep getting an error message that
the object I am looking for is not found even though I know it is there.

Any help would be appreciated. Also wondering if there is any way to open the
database and retrieve a report/form without actually opening the db, much
like the case of retireving tabledefs from another db using DAO.

THANKS.
juneBug

 

[Douglas J. Steele]

The Forms collection only contains those forms that are open. Since you've
just opened the database, there are no forms open.

You'll need to open your form, and then close it again when you're done with
it:

Set myApp = New Access.Application
myApp.OpenCurrentDatabase (dbLocation)
myApp.DoCmd.OpenForm inObjName
MsgBox myApp.Forms(inObjName).RecordSource
myApp.DoCmd.Close acForm, inObjName
myApp.CloseCurrentDatabase

 

[John Nurick]

Hi Doug,

Is it better in this situation (reading the RecordSource) to open the
form in Design view or normal view ... or does it make no difference
(except in cases where the form's RecordSource is changed as it opens?

Junebug, automating Access isn't quite the same as other Office apps;
it's probably worth checking the Microsoft KB articles on Using
Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
http://support.microsoft.com/?id=210111 (Access 2000 and later)

 

[Douglas J. Steele]

Good point, John. If the form is based on a recordset that's going to
retrieve a lot of data, opening in Design view would likely be better,
wouldn't it?

myApp.DoCmd.OpenForm inObjName, acDesign

 

[juneBug via AccessMonster.com]

Hello.

Thanks for the reply. It works. It makes sense.
Now...is there a way to access the objects of the other database without
automation (opening the DB, the form in design view) to read out its
properties?

Before I resorted using the Forms collection (I understand this only includes
the currently open forms), I was looking at the containers, document solution.
Accessing the Form container and iterating through all of its documents
(forms). I managed to read out the names of the all forms (myDocument.name),
however I could not access their properties. Any ideas on this?

 

[John Nurick]

Hello.

Thanks for the reply. It works. It makes sense.
Now...is there a way to access the objects of the other database without
automation (opening the DB, the form in design view) to read out its
properties?

Before I resorted using the Forms collection (I understand this only includes
the currently open forms), I was looking at the containers, document solution.
Accessing the Form container and iterating through all of its documents
(forms). I managed to read out the names of the all forms (myDocument.name),
however I could not access their properties. Any ideas on this?

AFAIK there's no way - at least no documented way - of accessing an
Access Form's properties via the corresponding DAO Document. In priciple
you can avoid opening the Form by using Application.SaveAsText (itself
undocumented) to export the definition of the form to a text file and
then parsing out the value of RecordSource - but this of course still
requires automating Access and not just DAO.

 

[juneBug via AccessMonster.com]

Thanks again...I will stick to the application object in the example above.