Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Datasheet with one combobox dependent on another combobox

    I have a very simple setup:
    Table Size: rows with different sizes
    Table Type: rows with different types
    Table Type_Size: rows showing which Sizes/Types combinations are permitted.

    Thus Type 1 might be available in Size S and M, but Type 2 might only be available in Size L.

    I have set up a form (i.e. for purchases) that allows a user to choose a Type from a combobox (easy query to fill the combobox). There is also a Size combobox with a simple query that adds an additional WHERE clause to only show Sizes for the specific Type selected in combobox #1 (based on the allowed combinations listed in Type_Size).

    I requery combobox #2 based on two events: change in combobox #1, or change in current record. As a form this works beautifully! I can only choose Sizes appropriate to each Type. Exactly what I want.

    Now I want to convert said form to a Datasheet. The query for the combobox #2 is the same across all rows of the Datasheet. In fact the results are pretty funny. If I have say 10 rows already displayed in the Datasheet and I select a row that has an item of Type 1, then all the rows with items Type 1 will list the size that was selected for that entry (since the query becomes valid). All the other rows will not show a size -- unless that size happens to also be available for Type 1.

    Any suggestions on how to make a Datasheet where each row displays the data it is supposed to?

    Thanks!
    Carlos

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Place the combos on a form that contains a sub-form in datasheet mode. That way the combos will only appear once.
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    That is exactly what I've done. A form that has a datasheet sub-form. Unfortunately due to a many-to-many relationship, said subform could have multiple rows - and I'm back to my problem.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Don't bind the subform to it's parent and, from the parent's code, build a filter with the values of the combos (and possibly other values) and apply it to the subform.
    Have a nice day!

  5. #5
    Join Date
    Jul 2012
    Posts
    1
    I am having an identical problem. My datasheet combo box (which references the value in another combo box on the datasheet) only seems to rely on the value in the first record on the sheet.

    It seems MS access doesn't allow this kind of data entry in a datasheet.

Posting Permissions

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