none
Sorting Combo box data RRS feed

  • Question

  • I've got a parentform1 and a subform1 of the parentform1.  The parent form is a list of employee fields (LastName, FirstName, Position, etc).  The subform has a number of fields that will deal with work done by the employee.  One of the fields (called tasks) in the subform is a combo box created within the underlying table of the subform.  What I did was I created another table that lists tasks and positions (ex: supervisor, manager, etc) assigned each task so there are two fields in the task table (task and position).  What I'd like to happen is when you open the parent form up (with the subform in datasheet view) and list all the employees the subform tasks field would only display the combo box list of tasks for what the position of that employee is. 

    The underlying tables are one(employeetbl) to many(tasksdetailstbl) relationship because employees can have multiple tasks.   The other table mentioned above that just has the two fields is the tasklisttbl and there isn't a relationship created for it since it is just used to add tasks for the combo box mentioned.  Help Please!  I'm open to other suggestions if there is a better way to do it.

    Monday, September 13, 2010 7:56 PM

Answers

  • taking,

    OK then you need to understandd that in 2003 there is a contraint on ComboBox contents...if a data value/item is not in the combobox list, then any data records with that value will show as blank/Null on-screen.

    For example:

    Table 1:

    ID    Property1  Property2

    1         AAAA      ABC

    2         AAAA      BCD

    3         BBBB      BBB

    4         BBBB      BCB

    if the combobox for property1 only lists "AAAA", records with ID-3 and ID-4 will show Property1 as empty/blank (Access 2007/2010 have another means of dealing with this issue).

    This must be considered when designing your user form, and the combobox list generated programatically from the actual (currently selected) data at runtime, or created as a "global" list - in order to avoid this issue.

    Bottom line if you have "bad"/"historic" data which must be displayed properly, even in your new forms/system, then you are almost forced into creating a generic list so that all values will appear, whether the data is "old" or "new". If not, then you are free to limit the lists as your business rules dictate.

     

    • Marked as answer by Bessie Zhao Tuesday, September 21, 2010 10:04 AM
    Tuesday, September 14, 2010 6:50 PM

All replies

  • before dispensing advice, We may need to know one important factor here:

    What version of Access are you using here? (this matters because the contents of combo-box lists have slightly different rules/options from older to newer releases.)

     

    Monday, September 13, 2010 8:14 PM
  • Typically a combo box will be based on a sorted Query, displaying the records in desired order, rather than directly on a table. Note that a field cannot be a combo box (despite Microsoft's misleading "lookup field" datatype, which just appears to be a combo box but is actually a Number field concealed behind the combo).

    In this case you apparently want to use a combo box based on a Query selecting only those tasks appropriate to this employee... right? If so, base the combo on a Query using a criterion on position such as

    =[Forms]![mainformname]![positionfield]

    and sorting by the task name.

     


    John W. Vinson/MVP
    Monday, September 13, 2010 9:08 PM
  • I'm using access 2003.
    Tuesday, September 14, 2010 6:34 PM
  • taking,

    OK then you need to understandd that in 2003 there is a contraint on ComboBox contents...if a data value/item is not in the combobox list, then any data records with that value will show as blank/Null on-screen.

    For example:

    Table 1:

    ID    Property1  Property2

    1         AAAA      ABC

    2         AAAA      BCD

    3         BBBB      BBB

    4         BBBB      BCB

    if the combobox for property1 only lists "AAAA", records with ID-3 and ID-4 will show Property1 as empty/blank (Access 2007/2010 have another means of dealing with this issue).

    This must be considered when designing your user form, and the combobox list generated programatically from the actual (currently selected) data at runtime, or created as a "global" list - in order to avoid this issue.

    Bottom line if you have "bad"/"historic" data which must be displayed properly, even in your new forms/system, then you are almost forced into creating a generic list so that all values will appear, whether the data is "old" or "new". If not, then you are free to limit the lists as your business rules dictate.

     

    • Marked as answer by Bessie Zhao Tuesday, September 21, 2010 10:04 AM
    Tuesday, September 14, 2010 6:50 PM