Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Cedar Falls, Iowa, USA
    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
    Thanked 31 Times in 31 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()
    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