Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filtering unbound control (a2003)

    I'm using a form with three unbound controls to provide the criteria for a query. The first control is a concatenation of three of the underlying table's fields, none of which is the PK. The values available to the second control need to be filtered by the primary key of the record in the first control. What's a good way to do that?

    first unbound control's row source:
    SELECT tblOrg.OrgID, tblOrg.OrgName, tblOrg.OrgClassID, [OrgName] & " - " & [City] & ", " & [StateID] AS Organization, tlkpCompleteAddr.[Mlg/PhysID], tblCSZ.City, tblCSZ.StateID
    FROM tblOrg INNER JOIN (tlkpCompleteAddr INNER JOIN tblCSZ ON tlkpCompleteAddr.CSZID = tblCSZ.CSZID) ON tblOrg.OrgID = tlkpCompleteAddr.OrgID
    WHERE (((tblOrg.OrgClassID)="client"))
    ORDER BY tblCSZ.City;

    2nd unbound control:
    SELECT tblWrkOrder.ClientOrgID, tblWrkOrder.WkOrderID AS [Work Order #], tblWrkOrder.StartDate
    FROM tblWrkOrder
    WHERE (((tblWrkOrder.ClientOrgID)=[What goes here to get the OrgID from first record in first unbound control?]))
    ORDER BY tblWrkOrder.WkOrderID, tblWrkOrder.StartDate;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filtering unbound control (a2003)

    Assuming that the first column is the Bound Column of the first control, you can refer to the OrgID as [Forms]![MyForm]![FirstControl] where MyForm is the name of the form and FirstControl is the name of the first control. So the Row Source of the second control would be

    ...
    WHERE (((tblWrkOrder.ClientOrgID)=[Forms]![MyForm]![FirstControl]))
    ...

    You must requery the second control each time the user selects something in the first control. Use the first control's After Update event for this:

    Private Sub FirstControl_AfterUpdate()
    Me.[SecondControl].Requery
    End Sub

Posting Permissions

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