[AccessD] Library Forms

Hadyn,

    When a form in external db (featuring library reference), is opened in local db, it gets included in forms collection of local db and its controls can be accessed & acted upon via following sample syntax in local db's code:

    Forms("ExternalFormName")("LabelName").Caption

    However, any attempt to assign a local form as the SourceObject for a subform control located in such an external form, meets with failure, attracting a message implying (somewhat misleadingly) that Jet is unable to locate the subform control itself. Conclusion - Mixing up of objects between external and local db's is not permitted.

    Interestingly, if the local db has a form with exactly the same name as that of external form, it can be opened with direct click, even if external form with this name has already been opened via library reference. This is an unusual situations where the Forms collection of local db shows two forms with identical names.

    As a work-around for the functionality sought by you, temporary import of external form could be considered. With this, there would be no problem in using a local form as its subform. Ultimately, the imported form can be deleted if desired, before closing the local db.

    If the external form is a bound one, extra care would be needed so as to ensure that the record source continues to hold good, even though the relevant tables & queries are located in external db. This is done by using the  FROM --- IN -- syntax in source SQL.

    Sample subroutine in click event of command button in local db, as given below, will carry out the following tasks:

    (a) Import the external form (F_External) under a temporary name.
    (b) Open the freshly imported form.
    (c) Assign appropriate caption to the label serving as heading.
    (d) Assign RecordSource to this form, based upon query named Q_Books belonging to external db. Special syntax using  IN argument in FROM clause is used as shown
    (e) Assign a local form named F_LocalSub as SourceObject for subform control named SF_Sub, located on the imported form.

Best wishes,
A.D.Tejpal
---------------

Sample Subroutine
(Usung external form with local subform)
===============================
Private Sub Cmd_A_Click()
On Error Resume Next
    Dim Qst As String, FormName As String
    
    FormName = "FFF_ZZZ"
    
    ' Delete form FormName if existing
    DoCmd.DeleteObject acForm, FormName
    
    ' Import form F_External as FormName
    DoCmd.TransferDatabase acImport, _
                "Microsoft Access", _
                "ExternalDbPath", acForm, _
                "F_External", FormName
    DoCmd.OpenForm FormName
    Forms(FormName)("LbHdg").Caption = _
                    "Form " & FormName & _
                    " (Imported From External Db)"
    
    ' Assign recordsource to parent form
    Qst = "SELECT * FROM Q_Books " & _
            "IN '" & "ExternalDbPath" & "';"
    Forms(FormName).RecordSource = Qst
    
    ' Assign Subform to parent form
    Forms(FormName)("SF_Sub").SourceObject = _
                                                        "F_LocalSub"
    
    On Error GoTo 0
End Sub
===============================

  ----- Original Message ----- 
  From: Hadyn Morgan 
  To: Access Developers discussion and problem solving 
  Sent: Monday, May 21, 2007 14:20
  Subject: [AccessD] Library Forms


  Hi guys

  I have developed a wizard for a project which uses the same main wizard form and various sets of subforms to complete different processes.  It works well and I would like to add the main form as part of my standard library so I can have customised wizards for any project.

  Question:  Can a library (CodeDb) form have an application (CurrentDb) form as a subform?  If so, how?

  Cheers
  Hadyn