Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: Combo box update question

    I'll present a simple senario to illustrate the solution I'm looking for. Take three tables: (1) PARENT tblPerson(PersonID, PersonLastName, ...), (2) CHILD tblSubject(SubjectID, PersonID, SubjectName), and (3) LOOKUP tblLookupSubject(SubjectName). For SubjectName in tblSubject there is a combo box lookup to tblLookupSubject (say this lookup table contains 5 subjects).

    A person can take multiple subjects. When adding the first record for a person the combo box displays all 5 subjects. When adding a second subject for a person the combo box still displays all 5 subjects.

    How do I get the combo box to display only the subjects that have not been previously selected? (That is, after one subject has been selected the combo box should display the remaining 4 subjects, etx.)

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Dynamically change the query the control is based on to be something like

    SELECT [tblLookupSubject].[SubjectName]
    FROM [tblLookupSubject]
    WHERE [tblLookupSubject].[SubjectName] NOT IN ('Option A','Option D')
    ORDER BY [tblLookupSubject].[SubjectName];

    If for instance 'Option A' and 'Option D' had been selected.

    If you need help dynamically building and assigning SQL to a control, I'd suggest looking here.
    Me.Geek = True

  3. #3
    Join Date
    Jul 2009
    Posts
    6
    One of the things I haven't been able to figure out yet using this suggestion is where PersonID comes into the query, since the drop-down list of subjects must be tailored to each person.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd look in to binding your combo box to a query with a LEFT JOIN. Look at the unmatched query wizard for an example.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2009
    Posts
    6
    What I have now are two queries:
    qryA(PersonID,SubjectName) - lists every possible subject for every person
    qryB(PersonID,SubjectName) - lists just what subjects have actually been selected by different persons

    What I really need is a query that lists every record in qryA EXCEPT those records in qryB where the PersonIDs match. This will then give a list of which subjects a given person has not taken.

    I've tried different types of joins, but cannot get any to work.

  6. #6
    Join Date
    Jul 2009
    Posts
    6
    I have a solution, although probably not the most elegant.

    What I did was to modify qryA & qryB (above) each to a single, concatenated, field “PersonIDubjectName” (after converting the autonumber PersonID to a string; “:” is used as a placement character to later separate PersonID and SubjectName.)

    I then used an SQL statement of the form provided by Nick above to get the “PersonIDubjectName” records NOT yet selected, and then used another query to split “PersonIDubjectName” back to PersonID, SubjectName (converting the string PersonID back to an integer.)

    I used this final query as the source for the combo box on the form using the criteria that the PersonID on the form must match the PersonID from the query.

    Thanks for the help which pointed me in the right direction.

Posting Permissions

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