Access: Fill a combobox with selections from another field

Question

I'm just learning how to use Access and while I've managed to muddle my way through most of what I'm trying to do, there's something I haven't been able to figure out how to do yet.

I have two forms and corresponding tables. In frmProducts is ColorOptions, a multi-select combobox containing a list of possible color options for a product, and Design, a text control for the name. In frmCustomers is OrderDesign, a combobox with a list of items from tblProducts, and OrderColours, a combobox.

Now, this is the problem: I want OrderColours to display list of the color options in tblProducts, but I can't figure out how. I can get it to display the value, but it's not a list of items, just one entry with the 'list' (e.g. a single entry reading "Brown,Red,Green"). I want the user to be able to select a single item from that subset.

Ideally I'd like to do this without messing with VBA or any advanced SQL, but if that's not possible then that's fine as well. I think the issue may be that the Colours field which contains the colours for that product is stored as text, but I'm not sure how else to store it as there's no 'array' or 'list' option for datatypes.

Sorry if I haven't been clear enough, or if this is posted in the wrong sub. I'm a beginner in Access, so I may have not been clear enough or used the wrong terminology. Any help would be much appreciated.

Mark Butler Mark Butler · Accepted Answer · 2014-08-22T09:45:26

I'm not quite sure I understand exactly how you want this set up, so I'm assuming the following. Please correct me if this is not right:

tblProducts contains (at least) the two fields productDesign and productColour
It is possible for there to be multiple records in tblProducts with the same productDesign but different productColour (different colours of the same design)

tblProducts

There is another table, tblCustomers, in which each record contains a productDesign and one of the corresponding productColours.

So you need the combobox OrderColours to display a list of the possible productColours for the selected value of productDesign in OrderProducts.

Now, set up combobox OrderDesign as follows:

Row Source Type: Table/Query
Row Source: SELECT DISTINCT tblProducts.productDesign FROM tblProducts;

and OrderColour:

Row Source Type: Table/Query
Row Source: SELECT tblProducts.productColour, tblProducts.productDesign FROM tblProducts WHERE (((tblProducts.productDesign)=[Forms]![frmCustomers]![OrderDesign]));
Column Count: 1

and give OrderDesign the following event AfterUpdate:

Private Sub OrderDesign_AfterUpdate()

  Me.OrderColour = Null
  Me.OrderColour.Requery
  Me.OrderColour = Me.OrderColour.ItemData(0)

End Sub

You may well also need to consider what happens when moving between records, if your comboboxes are bound:

Private Sub Form_Current()
  Me.OrderColour.Requery
End Sub

in the Form_Current event should do the trick.

Read this for details.

Access: Fill a combobox with selections from another field

1 Answers