Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2016
    Posts
    17

    Unanswered: Limit Selection in Combobox Without Affecting Past Records

    I have a form with a subform. The subform is in datasheet few and only displays one field (Office) and has a many-to-many relationship with the main form by way of a junction table. The field in the subform is a combobox with a row source to a table with office selections. I want to be able to limit the list of office choices in the combobox to eliminate any office that has been archived or tagged by way of a yes/no checkbox in the office table.

    In the row source for the combobox, I added the Archive field and entered No for the criteria but when returning to the main form any of the records that include the archived Office are blank. I need to be able to maintain the data that has already been entered.

    Hoping for some recommendations to accomplishing this that don’t necessarily involve duplicate fields where one is hidden by an event. Hoping there is a better solution.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 34
    you need an OUTER JOIN query.

    make a query,qsOffChosen, that has only Offices already chosen by (parent form ID).

    make another query, qsOffAvail, take the query above, qsOffChosen and the table with All offices, tOffices.
    join together on [office] name (or ID)
    dbl-click the join line, create an OUTER JOIN,
    set: All records in tOffices, Some in qsOffChosen
    bring down fields: tOffices.OFFICE , qsOffChosen.OFFICE
    under criteria, set tOffices.Office = null

    this says: show all offices in tOffices.Office that are NOT in qsOffChosen. (the ones not yet picked)

  3. #3
    Join Date
    Dec 2016
    Posts
    17
    ranman256,

    Thank you for your response. In the end, I utilized an OnFocus event on the combobox switching the RowSource to a query that included Offices that haven't been archived. I hadn't thought of it when I posted my issue. Thanks again.

Posting Permissions

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