Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  2. #2
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Francois

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  6. #6
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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>
    Wendell

  11. #11
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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>

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  13. #13
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Wendell,

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

    Jim.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •