Can you link to a sub-form in a 2nd database??? (Access2K SR1)

[WendellB]

We have a form that we use in a number of applications at different clients, and are toying with the idea of putting it into an add-in database so we don't have to maintain code in each of the 50 or so different databases. However this form uses dynamically linked subforms to display different view of data, so if we were to do that it would require that we link the master form in the add-in database to a sub-form contained in the main database. Does anybody out there know if you can do that trick programatically? Thanks in advance for any advise or help.

[JimDettman]

Not in anyway that I'm aware of. The SourceObject property expects the object to be within the current database.

If you explain in a bit more detail what it is your trying to do (for example why the subforms must remain in the main database), I might be able to offer other suggestions.

Jim.

[WendellB]

Your opinion pretty much matches ours. You can work in multiple databases in code, but there doesn't seem to be any way to set a subform control in an add-in to link to a subform in another database.

The scenario we have is a large form used to select various views of data using several subforms that are linked dynamically. Our clients like this form so much that we currently use it in nearly 20 different databases. However, if we decide we need to make a design change to the form, we now have to change it in 20 different databases. It would be wonderful if we could simply put the form into an add-in and reference the add-in from each of the application databases - we do that with several common forms we use in multiple apps. Hope this explains our objective better. If you have any insights or suggestions we would welcome them.

[Francois]

Wendell,

I don't know if you can link the subform but you can import it.
DoCmd.TransferDatabase acImport, "Microsoft Access", "Source.mdb", acForm, "frmToBeImported", "frmDestination", False

Hope this help

[WendellB]

That's true, but then we would have to load all the subforms we ever want to use into the add-in, not to mention linking to all of the tables used by the subforms, etc. We would be better off to import the main form and it's associated bits into each of the applications - that way we would only be adding one object (more or less) to each application database. It's a puzzle, isn't it.

[JimDettman]

The part I don't understand is:

"using several subforms that are linked dynamically"

Do you mean you modify the master/child links and change recordsources, change controls or what?

In other words I don't understand why the whole main/subform combination cannot be in the MDA and called from the main database. Why do the subforms have to reside there?

Jim.

[charlotte]

I suspect from what Wendell posted earlier that the subforms may be customized for various applications. Putting them in the MDA would require customized add-ins for each application instead.

[WendellB]

It's kind of a convoluted subject, but basically we change the sub-form to display different data depending on the view that is chosen on the main form. Thus in one application database we may have three different sub-forms that are linked dynamically to a single sub-form control on the main form, depending on which of the three choices the user makes. These subforms are quite complex, often involving a tab control and several sub-sub-forms on different tabs. In a second application database we might have 4 different sub-forms that the user can select, and a third application database might have two views, etc. By the time you get 15 or 20 application databases all using the same main form, it gets to be a maintenance nightmare if you need to make a change in the design of the main form. Hope this clarifies things a bit.

[PeterNRoth]

i have never done this, but here's what the help says in AXP:
<img src=/w3timages/blueline.gif width=33% height=2>
Set References to Type Libraries
- - -
When you set a reference to another application's type library, you can use the objects supplied by that application in your code. For example, if you set a reference from Microsoft Access to the Microsoft Excel library, you can then use Microsoft Excel objects through Automation (formerly called OLE Automation). If you set a reference to a Visual Basic project in another Microsoft Access database, you can call its public procedures. If you set a reference to an ActiveX control, you can use that control on Microsoft Access forms.
<img src=/w3timages/blueline.gif width=33% height=2>
So by the last sentence wording, it seems that it is (hypothetically) possible.

Perhaps the question is: how would this be done in another application? and translate the answer to Access.

I would research creating a dll or ActiveX component that i could call from access.

[WendellB]

Excellent point - we do that all the time when we use add-in and library databases, but we always do it from the application database to the add-in. What popped into my head when I read your post was that we could try doing it the other way - establishing a reference both ways in effect. The application would reference the add-in mdb file to get the main form, and then the add-in database would programatically set a reference to the application database in order to get at the subforms. Will do some testing and see if it really does work. Thanks for continuing to pursue the question even if it seemed impossible! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

[PeterNRoth]

Taking it all the way back to 1's and 0's, there HAS to be a way!
I'd be interested in reading your solution, since i am still a newbie at DB work. Maybe that's why I can offer a solution - I don't know enough to know what's impossible. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[charlotte]

I would be interested in reading a solution too, but don't count on there always being a way. Type libraries are typically written in a language like C, which has a lot more control over the whole process than VB has. Creating a COM object might be a possibility, but if you've ever worked much with ActiveX controls, you know that you can't just add on to them or mix them with other controls, so it's not likely you could add subforms from a user's database to one.

[JimDettman]

Wendell,

and the only goal is to propagate changes in the main form to the various application databases?

Jim.

[WendellB]

Yes, that's true. The main form contains several ActiveX controls which are updated from time to time by the component supplier (Microsoft, FMS, etc) so we have to make occasional changes to the code as the controls are upgraded (or supplanted by newer or better controls). I know it seems like overkill initially, but we do have about 15 databases that use the same main form, and updating is getting to be an onerous task. Thanks for all the interest in the problem.

[AndyAinscow]

From what I have read concerning this matter here you have the main form that is identical for each database.
What you don't want to do is manually change it in each of the production front ends, right?

I would do the following.
Have your own maintenance database with said main form and a table listing the functional front ends.
A piece of code that does the following.
Open table and read records
while got record
Open front end #1
Delete main form
import main form from maitenance database
close front end #1
read next record