Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Question Unanswered: Limit Combo Box Based upon Subform

    Hello!

    I have a combo box that adds the owners of something to a list in a subform. What I would like to do is limit the list in the combo box so that when The box is opened, those owners on the subform already are not in the combo.

    This is the code that I have for the combo box dataset. It lists all of the owners.
    Code:
    SELECT DISTINCT qryNames.EntityID, qryNames.Name
    FROM qryNames LEFT JOIN [qryOverall-sfrmOwner] ON_
    qryNames.EntityID = [qryOverall-sfrmOwner].OwnerID
    WHERE ((([qryOverall-sfrmOwner].SubFundID)<>[Forms]![frmListofOwnersbyFund]![SubFundID]_
    Or ([qryOverall-sfrmOwner].SubFundID) Is Null))
    ORDER BY qryNames.Name;
    Thanks in advance!!

    p.s. I know that I need to change the name "Name" for one of the fields. That is on the list!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry but I'm not sure to understand. What do you mean with
    when The box is opened, those owners on the subform already are not in the combo
    ?

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    If the total list is:

    A
    B
    C
    D
    E
    F

    And the owners are

    A
    B
    C

    Then I would like the combo box to be:
    D
    E
    F

    Does this make more sense?

    Thanks!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use: SELECT ... FROM ... WHERE ... NOT IN (SELECT ...

    Example (from the book):

    Code:
    SELECT FirstName, LastName
    FROM Person.Contact AS c
        JOIN HumanResources.Employee AS e
        ON e.ContactID = c.ContactID
    WHERE EmployeeID NOT IN
       (SELECT SalesPersonID
       FROM Sales.SalesPerson
       WHERE SalesQuota > 250000)
    Have a nice day!

Posting Permissions

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