Data Entry Form: Remove choices from drop-down list based on previous selections RRS feed

  • Question

  • Hi, Everyone

    I am a total newbie to this, so please keep it as simple as you can :-)

    I am trying to create a simple Services and Hardware Requisition form for New User Take On.

    I have a table "Services", which lists Description, Service Number and Amount.

    I want to be able to update pricing in this table so I don't have a major issue updating the app.

    I have created a data entry form which the Team Leader will fill in - see attached.

    All the drop-down lists on the form reference the "Services" table, with default values pre-selected.

    for the "Other Services" list I want to allow multiple values to be selected, but I want to exclude those values already selected in the other drop down lists.

    Any ideas?

    Tuesday, September 18, 2018 8:37 AM

All replies

  • Hmm, to allow multiple value selection,  you must be using a multi-valued field (MVF). If so, the selections are usually set up at the table level. If so, I am not sure it's possible to limit the list per record. 

    If it's not possible, you might have to use an unbound listbox instead and then use code to populate the MVF.

    Just my 2 cents...

    Tuesday, September 18, 2018 12:06 PM
  • The issue is complicated by the poor design of your table.  Rather than having multiple columns for the service types a correct design would be to model the many-to-many relationship type between Users and Services in a related table.  In broad outline the tables would be:

    ….UserID  (PK)

    ….ServiceTypeID  (PK)

    ….ServiceID  (PK)
    ….ServiceTypeID  (FK)

    and to model the relationship type:

    ….UserID  (FK)
    ….ServiceID  FK)

    The primary key of UserServices is a composite of the two foreign keys.  Consequently any attempt to insert a duplicate row would raise a key violation error, which can be handled in the subform's module.  Note that UserServices does not include a ServiceTypeID  column as this is functionally determined by ServiceID.  To include such a column would mean that the table is not normalized to Third Normal Form and consequently open to the risk of update anomalies.

    The user interface would be a users form, in single form view, in which is embedded a user services subform in continuous forms view.  A user can select as many services as required in the subform by inserting a new row for each.

    If you wish to select a service type first in the subform and then a service from a restricted list, you can do so by means of a hybrid control made up of an unbound combo box superimposed on a bound text box to give the appearance of a single combo box control.  For this the subform would be based on a query which joins the UserServices, Services and ServiceTypes table.  Alternatively, and more simply, you can use a bound combo box in which the service type and service are both listed, and an unbound text box control which references the Column property of the combo box to show the service type of the selected service.  You'll find examples in in my public databases folder at:!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Tuesday, September 18, 2018 1:08 PM Typo corrected.
    • Proposed as answer by Dirty White Hat Tuesday, September 18, 2018 5:08 PM
    Tuesday, September 18, 2018 1:07 PM