Results 1 to 5 of 5
  1. #1
    DavidMadge
    Guest

    Changing form linkages midstream

    I need to apply different filters or change the master/child link between a subform and main form while the forms are open, depending on a field value in the main form's current record. Both forms include the fields 'LicenceNumber' (text) and 'P_PropertyCode' (autonum). The main form also includes 'PropertyContracts' (true/false). When I go to a different record on the main form and 'PropertyContracts' for that record is false, I want the forms linked only by 'LicenceNumber'. In these cases, the subform (fsubContract) will contain only one record (contract) for the current licence number, regardless of how many properties that licence covers. When 'PropertyContracts' is true for the current record, I want the forms linked by 'LicenceNumber' and 'P_PropertyCode'. In these cases the subform will contain one record for each property covered by the current licence. The current property is selected from a different subform.

    I've tried setting the subform's Filter and FilterOn properties by code under the main form's OnCurrent event. I can set the filter OK but setting FilterOn=True gives me run-time error 2001, 'You cancelled the previous operation'. When I put a command button on the subform to set the filter and filteron properties, I got the same error message. Is changing the filter likely to be clashing with setting bookmarks in the recordset ?

    I also looked at 'DoCmd ApplyFilter...' but can't see how to use it to apply a filter to a form other than the one the code is running in. I also can't see a way to alter the master/child links once the subform is loaded and the links are in place.

    Is it possible to do what I want ?

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

    Re: Changing form linkages midstream

    You do it by either changing the SourceObject of the subform control or the recordsource of the subform, and then changing the link properties in code.

    <pre> Dim sfrm As SubForm
    'This is the subform control, not
    'the form it contains
    Set sfrm = [fsubContract]</pre>

    <pre> 'This would change the subform in
    'the control to a different subform
    sfrm.SourceObject = "fsubLicense"</pre>

    <pre> 'This would change the rowsource
    'of the subform itself(not the control)
    'to whatever is contained in strSrc,
    'i.e., a select statement
    sfrm.Form.RecordSource = strSrc</pre>

    <pre> 'This would relink the subform to
    'the parent form based on the LicenseNumber
    'field in the underlying recordsets
    sfrm.LinkChildFields = "LicenceNumber"
    sfrm.LinkMasterFields = "LicenceNumber"</pre>


    Does that help?
    Charlotte

  3. #3
    DavidMadge
    Guest

    Re: Changing form linkages midstream

    Thanks Charlotte. I didn't want to change the subform source, but your answer told me all I needed. Here's what I'm doing now:

    If [PropertyContracts] = True Then
    fsubContractActivity.LinkMasterFields = ""
    fsubContractActivity.LinkChildFields = ""
    fsubContractActivity.LinkMasterFields = "LicenceNumber;[fsubPropertyName].Form![P_PropertyCode]"
    fsubContractActivity.LinkChildFields = "LicenceNumber;P_PropertyCode"
    Else
    fsubContractActivity.LinkMasterFields = ""
    fsubContractActivity.LinkChildFields = ""
    fsubContractActivity.LinkMasterFields = "LicenceNumber"
    fsubContractActivity.LinkChildFields = "LicenceNumber"
    End If

    It's working just as I want (If I don't clear the Master/Child fields first I get an error).

    Thanks again.

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

    Re: Changing form linkages midstream

    Whatever works! I've never tried changing the links for the same subform, so I wasn't aware of that necessity. I generally show different subforms depending on what I need to display.
    Charlotte

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

    Re: Changing form linkages midstream

    Another method if xou didn't want to change the source is to use two sub forms, one hidden, one visible and flip between them when appropriate. This could be useful if it takes a long time to requery the subform and the users often flip back and forth.

Posting Permissions

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